Confused about how to filter/search my database.

Peter:

Firstly, never ever use names as keys, for the reason you've discovered –
they can be duplicated. Even in combination with other values they are not
reliable. I've previously recounted the experience I once had at a clinic,
but its worth repeating. Patients were 'keyed' in the hospital's system by a
combination of their first and last names, gender and date of birth. On one
occasion at which I was present two patients turned up, having exactly the
same names, both being female and both having the same date of birth! When
dealing with people always use a 'surrogate' numeric key – an autonumber will
do fine as the value is arbitrary; all that matters is that the values are
unique.

However, this is the first time you've mentioned that individuals are
involved, and this does alter the basis of the model. So far we've
identified entity types of Villages and PeopleStatuses which are in a
many-to-many relationship with each other via the VillagePeople relationship
type, and we have three tables, one modelling each entity type (relationship
types are really just a special kind of entity type). This model does not
allow for individual people, only statuses which could apply to more than one
person.

By introducing individual people another entity type, People, is introduced
into the model, so we need a People table, with a numeric primary key
PersonID and columns for FirstName, LastName and whatever other attributes of
each person you need to record. One of these attributes is PersonStatus, so
a PersonStatus column is needed in the People table. This has a knock-on
effect, however, as the VillagePeople table now models a many-to-many
relationship not between Villages and PeopleStatuses, but between Villages
and People, and the there is a one-to-many relationship between
PeopleStatuses and People (this assumes each person can only be of one status
at any one time).

So, the VillagePeople table is now has columns Village and PersonID (NB the
latter must not be an autonumber in this table as it can be in People), with
the table's primary key being a composite one of the two columns, set up in
the same way as I described in my last reply.

Diagrammatically the model would thus look like this:

PeopleStatuses---People---VillagePeople---Villages

where the and signs indicate the 'many' end of each relationship (the
'referencing' tables in the jargon).

The query to find the villages with no people of the status type entered as
a parameter would now need expanding slightly like so:

SELECT *
FROM Villages
WHERE NOT EXISTS
(SELECT *
FROM VillagePeople INNER JOIN People
ON VillagePeople.PersonID = People.PersonID
WHERE VillagePeople.Village = Villages.Village
AND PersonStatus = [Enter status:]);

I'd suggest that you set up the four tables in a blank database and create
the relationships between them. Then in that link to you original tables in
your current database. You should then be able to fill the tables by means
of 'append' queries in the case of the people, PeopleStatuses and Villages
table. The PersonID column in people will be automatically filled with
arbitrary unique values if you've used an autonumber for this (which I'd
recommend).

When it comes to filling the VillagePeople table it’s a little trickier as
you need to get the relevant PersonID value which has been inserted into the
autonumber column in people for the relevant person in each row. For this
example I'm going to assume that the linked table to your original
VillagePeople table is called VillagePeople_1. The way this query works is
by joining this original table to the newly filled People and Villages tables
to get the PersonID and Village names which match those in the original table:

INSERT INTO VillagePeople ( PersonID, Village )
SELECT People.PersonID, Villages.Village
FROM (People INNER JOIN VillagePeople_1
ON People.Surname=VillagePeople_1.Surname)
INNER JOIN Villages
ON VillagePeople_1.Village=Villages.Village;

Note that because one of the joins here is on surname you must ensure for
the time being that all surnames are unique by artificially differentiating
the duplicates in the way you have done. Once you have successfully
populated the tables you can then edit them so the names are again
duplicated, as they should be of course, because each person is now uniquely
identified by their PersonID not by their name.

Ken Sheridan
Stafford, England

"PeterPeter" wrote:

Ken: I've got stuck again I'm afraid.

I made a new Villages table and created a single field 'Village' and made
this the primary key.

Currently my VillagePeople table uses the Surname field as the primary key
(this isn't very good as some people have the same surname so I have to put a
full stop after duplicate names). I tried changing Village and PersonStatus
to become the composite primary key, but that wasn't accepted: Village or
Person Status is not unique to each to each record. I.e. in some Villages
there is more than one person there; In the same village there may be lots of
people who are TypeB.

Sticking with Surname as the key field, I tried to perform the SQL query.
Unfortunately it returned: "the INSERT INTO statement contains the following
unknown fieldname: 'Village'. (Of course I haven't made the mistake of not
using my own name for this field).

I assume this error message has occured because my primary keys have not
been set up as you suggested, but I can't see how I can make Village and
PersonStatus be the composite primary key. My records are probably only
absolutely unique in terms of the PhoneNumber field.

I really appreciate your help, Peter

"Ken Sheridan" wrote:

Peter:

I'll assume you've created the (empty) Villages and (populated)
VillagePeople tables at least. You might have given them and their columns
different names to those I used of course, but its then just a question of
substituting the correct names in place of mine. I'll take each pint from
then on one at a time:

1. Primary Keys

1.1 The primary key of the Villages table is the Village column (this
assumes all village names are unique of course). So in table design view
select the Village column (aka field), right click and then select Primary
Key from the shortcut menu.

1.2 The primary key of the VillagePeople table is a composite one of
Village and PersonStatus, so you first have to select both columns. Do this
by clicking in the 'field selector' (that’s the little grey rectangle on the
left) of the Village column. Then while holding down the Ctrl key click on
the field selector of the PersonStatus column. Both should now be
highlighted. Then right click and select Primary Key from the shortcut menu.
The key symbol should now show in the field selectors of both columns.

1.3 If you've also created a PeopleStatuses table set the PersonStatus
column of this as its primary key in the same way as you did for Villages.

2 Populating Villages and PeopleStatuses tables

2.1 Lets assume you already have the VillagePeople table populated with your
existing data. You must fill the empty Villages table and PeopleStatuses
table (if you've also created this) BEFORE you create and enforce
relationships between the tables. You do this with 'append' queries'.

2.2 Open the query designer, don't add any tables, and switch to SQL view.

2.3 The SQL window should contain 'SELECT;' but nothing else. Delete this
and paste in the SQL fore the first query as follows, changing the table
and/or column names if necessary:

INSERT INTO Villages(Village)
SELECT DISTINCT Village FROM VillagePeople;

2.4 Click the 'Run' button on the toolbar or select Run from the Query menu
(or 2007 equivalent). Answer yes when prompted. All being well this should
add one row per village into the empty Villages table.

2.5 Do the same to fill PeopleStatuses, using the following SQL:

INSERT INTO PeopleStatuses(PersonStatus)
SELECT DISTINCT PersonStatus FROM VillagePeople;

3. Relationships

31 Open the Relationships window from the button on the main database
toolbar (or whatever the equivalent is if you are using Access 2007).

3.2 Right click within the window and select 'Show All'. You should now
see all the tables in the window.

3.3 Select the Village column from the Villages table and drag across to
the Village column in the VillagePeople table and release the mouse button.
The 'Edit Relationship' dialogue should now open showing the relationship
between the two tables as on-to-many.

3.4. Check the 'Enforce Referential Integrity' and 'Cascade Update Related
Fields' check boxes. If you want to be able to delete a row in the Villages
table and automatically have all matching rows in VillagePeople deleted also
check the 'Cascade Delete Related Fields' check box. Then click OK to create
the relationship

3.5 If you've created the PeopleStatuses table create a relationship
between this and Village People, this time on the PersonStatus columns. In
this relationship, however, you would almost certainly NOT want to 'Cascade
Delete Related Fields'.

4 Querying for Villages without Type A people

4. 1 Open the query designer as before, switch to SQL view and paste in the
following SQL:

SELECT *
FROM Villages
WHERE NOT EXISTS
(SELECT *
FROM VillagePeople
WHERE VillagePeople.Village = Villages.Village
AND PersonStatus = "Type A");

4.2 This assumes that the text entered in the PersonStatus colum in
VillagePeople for those villages with type A people is Type A exactly. You
can make the query generic, however, by using a parameter in place of the
literal string:

SELECT *
FROM Villages
WHERE NOT EXISTS
(SELECT *
FROM VillagePeople
WHERE VillagePeople.Village = Villages.Village
AND PersonStatus = [Enter status:]);

4.3 Whether you use a parameter or not to open the query select Datasheet
View from the View menu or from the appropriate button on the toolbar to open
the query. If you've used a parameter you'll be prompted to Enter status:
In this case you can enter any value of the PersonStatus column and the query
will return those villages which don't have a person of that status.

4.4 Save the query under a suitable name if you want to use it again. Make
sure you save it while in SQL view, though.

4.5 You can switch to design view to see how the above query would look if
built in design view. You'll see, however, that the whole of the subquery is
contained within the 'field' row of a column in the design grid. A subquery
cannot be constructed visually in design view, it has to be entered as SQL,
so it makes more sense to write and save the whole thing in SQL to start
with.

4.6 While opening a query directly in datasheet view will give you the
results a better option, once you've built and tested the query, is to create
a form based on it, using the form wizard to create a 'continuous' form. The
presentation is a lot more professional looking that way. All you have to do
then is open the form rather than opening the query. If you've used a
parameter in the query you'll be prompted for the value as before.

5 Data Entry

5.1 Not directly connected with your current requirement, but having
created and filled the three related tables you can create an interface for
data entry by creating a form based on the Villages table and embed within it
a subform based on the VillagePeople table. The main form would be in single
form view and the subform in continuous form view, and would contain a combo
box from which you can select any status value from the PeopleStatuses table,
so you can add as many rows per village as necessary in the subform simply by
selecting a different status from the combo box for each. You can even add a
new status value into the PeopleStatuses table directly via the combo box if
its not already represented in the database. This is something to come back
to once you've got the tables set up and populated correctly, however.

I hope I haven't missed any points, but if there's anything I haven't
covered post back.

Ken Sheridan
Stafford, England

"PeterPeter" wrote:

Thank you very much for your suggestions, Ken and Phil.
Ken: I've tried to implement your solution but have got very stuck as I am
new to Access. I don't know quite what to click on/type in.
Sorry for being such a newb, I would really appreciate your help.

Kindest Regards, Peter

"Ken Sheridan" wrote:

Peter:

This is a little trickier than might appear at first site as two or more
rows in the table could have the same value in the Village column, one of
them having "Type A" in the PersonStatus column and the others different
statuses. This means if you create a query restricted like so:

SELECT *
FROM VillagePeople
WHERE PersonStatus "Type A";

it won't tell you which villages don't have Type A people as the rows for
the village with other types of people will still meet this criterion even
though one doesn't.

Before we come to the correct solution, however, we need to step back a
little and look at the wider structure of the database. On its own the
VillagePeople table is not 'normalized' because the Village name is repeated
in multiple rows, once for each person status for each village. This means
that the same village could be entered slightly differently, e.g. as a result
of a simple typo, Much Binding in the March say, instead of Much Binding in
the Marsh. What's needed is another table Villages with a column Village
which contains one row per village. This table is then related to
VillagePeople in a many-to-many relationship type on the Village columns,
with referential integrity and cascade updates enforced in the relationship.
The enforcement of referential integrity is important as that prevents two
things:

1. The insertion of an invalid Village value in VillagePeople
2. The deletion of a row from Villages while there is at laest one
matching row in VillagePeople.

The primary key of Villages is the Village column. The primary key of
VillagePeople is a composite one of both columns, Village and PersonStatus.

If you don't already have a Villages table then create one and fill it with
the following 'append' query:

INSERT INTO Villages(Village)
SELECT DISTINCT Village FROM VillagePeople;

Now you have two correctly normalized tables. To find villages with no
Type A person you can use a subquery in the WHERE clause of a query like so:

SELECT *
FROM Villages
WHERE NOT EXISTS
(SELECT *
FROM VillagePeople
WHERE VillagePeople.Village = Villages.Village
AND PersonStatus = "Type A");

In plain English what this query is saying is: return rows from Villages
where there is no row in VillagePeople with the same Village value and a
PersonStatus value of "Type A".

You might have inferred, correctly, from the above that you also should have
a PeopleStatuses table with a column PersonStatus, this table also being
related to VillagePeople in the same way as Villages is, but this time on the
PersonStatus columns. What VillageStatus is in fact doing is modelling a
many-to-many relationship type between the Villages and PeopleStatuses entity
types by resolving the relationship into two one-to-many relationship types.
This is how many-to-many relationship types are modelled in a relational
database. Occasionally, in some special circumstances, a one-to-many
relationship is also modelled by a third table in the same way, but normally
it is modelled simply by including a foreign key column in the referencing
table which maps to the primary key column of the referenced table.

Ken Sheridan
Stafford, England

"PeterPeter" wrote:

Dear lovely people,

I'm fairly new to Microsoft Access and was wondering if you would please
help me with something? (Please forgive what may be incorrect terminology).

I have made a database and would like to perform a filter.

In my database:

Field 1 contains many possible values (villages)

Field 2 contains several values (status of person)

My database contains records that are alike in terms of Field 1 and alike in
terms of field 2.

What I would like to do is list the "villages that don't have a particular
status of person (i.e. villages that don't have a person Type A)".

Please would you advise me how I might perform this filter/search?

Yours Hopefully and with Thanks, Peter