If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Confused about how to filter/search my database.
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 |
#2
|
|||
|
|||
Confused about how to filter/search my database.
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 |
#3
|
|||
|
|||
Confused about how to filter/search my database.
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 I don't think you can do this with a filter - a filter simply hides values that don't match (often very useful). I think you need a query, and also a query which uses "grouping". Effectively, you need to select the names of the villages where the count of people of status XYZ is zero. So, in the query builder (off the top of my head) turn on "grouping" (the Zigma symbol), and in the Group By section pick "where" for Village, "count" for status, and put 0 in the criterion field. Tip: once you have the query apparently working, have a look at the SQL view - will significantly aid future understanding. Phil, London |
#4
|
|||
|
|||
Confused about how to filter/search my database.
Phil:
I don't think that approach will work as the COUNT only operates on those rows returned by virtue of the restriction in the WHERE clause. It would work if you were looking for villages with any specific positive number of rows which all match a criterion in the WHERE clause but not for zero rows. To do it by means of a HAVING clause you'd have to SUM the return value of an expression which evaluates to 1 or 0, e.g. SELECT Village FROM VillagePeople GROUP BY Village HAVING SUM(IIF(PersonStatus = "Type A",1,0)) = 0; However, this will only work if there is at least one row for every village in the table. Its theoretically possible that a village could have no people at all yet recorded for it, which would mean it satisfies the criterion of having no Type A people, but the query would not return it. Also there would be an update anomaly if a village has one person recorded for it, but they then leave, and the row is deleted. The village would no longer exist as far as the database is concerned. Similarly if the only instance of a particular person status is deleted. By normalizing the table by decomposition into three tables the update anomalies are avoided and the villages missing Type A people can be identified even if they are totally devoid of occupants. Ken Sheridan Stafford, England "Philip Herlihy" wrote: I don't think you can do this with a filter - a filter simply hides values that don't match (often very useful). I think you need a query, and also a query which uses "grouping". Effectively, you need to select the names of the villages where the count of people of status XYZ is zero. So, in the query builder (off the top of my head) turn on "grouping" (the Zigma symbol), and in the Group By section pick "where" for Village, "count" for status, and put 0 in the criterion field. Tip: once you have the query apparently working, have a look at the SQL view - will significantly aid future understanding. Phil, London |
#5
|
|||
|
|||
Confused about how to filter/search my database.
Ken Sheridan wrote:
Phil: I don't think that approach will work as the COUNT only operates on those rows returned by virtue of the restriction in the WHERE clause. It would work if you were looking for villages with any specific positive number of rows which all match a criterion in the WHERE clause but not for zero rows. To do it by means of a HAVING clause you'd have to SUM the return value of an expression which evaluates to 1 or 0, e.g. SELECT Village FROM VillagePeople GROUP BY Village HAVING SUM(IIF(PersonStatus = "Type A",1,0)) = 0; However, this will only work if there is at least one row for every village in the table. Its theoretically possible that a village could have no people at all yet recorded for it, which would mean it satisfies the criterion of having no Type A people, but the query would not return it. Also there would be an update anomaly if a village has one person recorded for it, but they then leave, and the row is deleted. The village would no longer exist as far as the database is concerned. Similarly if the only instance of a particular person status is deleted. By normalizing the table by decomposition into three tables the update anomalies are avoided and the villages missing Type A people can be identified even if they are totally devoid of occupants. Ken Sheridan Stafford, England "Philip Herlihy" wrote: I don't think you can do this with a filter - a filter simply hides values that don't match (often very useful). I think you need a query, and also a query which uses "grouping". Effectively, you need to select the names of the villages where the count of people of status XYZ is zero. So, in the query builder (off the top of my head) turn on "grouping" (the Zigma symbol), and in the Group By section pick "where" for Village, "count" for status, and put 0 in the criterion field. Tip: once you have the query apparently working, have a look at the SQL view - will significantly aid future understanding. Phil, London You're quite right - elegantly (and courteously) put. I shouldn't dash off "helpful" answers "off-the-top-of-my-head" when I don't have the time to think about them properly. This does take time, which is all the more reason to admire the generous (and accurate) answers so often given here! This problem now looks harder than it did last night, and it's not in my "comfort zone" after all. You're right about the need for normalisation. I keep thinking there ought to be an outer join in there somewhere - or is that just another way of doing this? Phil (sheepish) |
#6
|
|||
|
|||
Confused about how to filter/search my database.
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 For VillageStatus , read PersonStatus? Phil |
#7
|
|||
|
|||
Confused about how to filter/search my database.
Phil:
I have to admit that if I hadn't already posted a subquery solution I would at first sight have expected yours to work. It was only that having to think about the differences stirred the few remaining functioning grey cells into some semblance of activity. Regards, Ken Sheridan Stafford, England "Philip Herlihy" wrote: You're quite right - elegantly (and courteously) put. I shouldn't dash off "helpful" answers "off-the-top-of-my-head" when I don't have the time to think about them properly. This does take time, which is all the more reason to admire the generous (and accurate) answers so often given here! This problem now looks harder than it did last night, and it's not in my "comfort zone" after all. You're right about the need for normalisation. I keep thinking there ought to be an outer join in there somewhere - or is that just another way of doing this? Phil (sheepish) |
#8
|
|||
|
|||
Confused about how to filter/search my database.
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 |
#9
|
|||
|
|||
Confused about how to filter/search my database.
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 |
#10
|
|||
|
|||
Confused about how to filter/search my database.
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 |
|
Thread Tools | |
Display Modes | |
|
|