Locating unmatched data involving two fields in table

[Roncc]

I haven’t used the forum for a while, but you people have been very helpful in the past and I hope you can help me resolve a problem. I am the “I.T. guy” (volunteer) for our local foodbank and have used an Access database to keep records for them for many years. A client tracking database I created is set up so there is an autonumbering system that assigns a number to each household that we provide assistance to. This number is used as the “clientid” for the household. Any household may come in once per month to receive assistance. The “clientid” number is unique for each household and the table maintbl is used to store all the records. (When a client comes in for assistance this process is accomplished by first creating a record in a table called imaintbl, which has the autonumbering field in it, then appending that record from the table imaintbl to the table maintbl. At that point the record in the imaintbl is deleted and we are ready to enter another client into the system.) If the client is a new client, this autonumber is used as the clientid number and ordernumber. If they are a returning client, the number is used as an ordernumber only for the record. The “clientid’ field is a primary key field in the table maintbl. A second identifier for each household is the date of birth of the primary member of that household, the field is called the dob field. The system is designed so that there should be only one “clientid” number for each household but, it is possible that different households may share the same date of birth for the primary member of the household. For example, for the clientid 123456 the dob for the client will always be 10/14/1967 but here may be many clients with the data of birth 10/14/1967 (and they will have a different clientid number). I use this database to provide data for filing for grants and for providing support information to any agencies that may want to review our records. Here is my problem: over the years the database has been slightly corrupted and now there are some records where different clients have the same clientid. For example, there are records where the clientid is 23456 but some of the client’s dates of birth are 5/5/2001 and others are 10/2/1959 (they are different households). The number records like this are not huge, <70 out of 55,000 records, but I want my data to be as accurate as possible. Question: is there a way to identify which records of the 55,000 have common clientid number but have a different date of birth? I’ve tried creating queries and relationships, etc. but still can’t find the specific records that match in one field but not the other. Any ideas? Thanks in advance. Roncc

[Gicu]

Create a query in design view and add the maintbl twice; drag ClientID from maintbl to ClientID in maintbl_1, bring in whatever fields you want to see in the query and in the criteria for DOB in maintbl enter <> maintbl_1![DOB]

Cheers,

[Roncc]

That did the trick!!!! Thank you so much Gicu, I really appreciate it.

[Gicu]

You're very welcome!