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 |
#11
|
|||
|
|||
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 |
|
Thread Tools | |
Display Modes | |
|
|