A tricky query

[JC10001]

Hi guys (and gals?). I'm running into a snag with a database I am writing and I could really use some help. I'm at my wits end.

I have a table that stores a patient id, a record id, 3 fields for race, and other data. The main part looks something like this.

Pat_ID Rec_ID Race1 Race2 Race3
----------------------------------------------------------------
12345678 1 White Null Null
12345678 2 White Null Null
98765432 1 White Black Null
98765432 2 Black White Null
98765432 3 Asian Null Null


Basically, what I am trying to do is locate patients in which all of the race fields do not match up. Race fields that are swapped (like in the 3rd and 4th record) are ok. Its the fifth record in which the person is asian that sticks out. I'd like the result of the query to be a list of records that did not have a duplicate. I have already removed patients with only one record from the table so this table only contains patients with more than one record.

The result I am looking for would be:
Pat_ID Rec_ID Race1 Race2 Race3
----------------------------------------------------------------
98765432 3 Asian Null Null

I've tried many variations of different queries but none of them seem to be working. Many many thanks to anyone who might be able to help.

 

[Mile-O]

As you say you are writing this database I can only guess that you are not modifying a live database.

Your structure is wrong. You should not have a repeating group. You are building your table as if it were an Excel spreadsheet. This is Access; not Excel.

To prevent this being a problem I would strongly advise you to normalise your data. If you don't know what that means there's been loads of posts regarding the topic.

A web search of 1st, 2nd, and 3rd Normal Form will also offer more advice.

 

[JC10001]

I have no control over how the database is written. I'm merely doing what I am told. There are multiple race fields because that data is used for statistics.

What if I was to make my question a bit simpler? What if it was like this?

Pat_ID Rec_ID Race
-----------------------------------
12345 1 White
12345 2 White
12345 3 Black

and I wanted just...

Pat_ID Rec_ID Race
-----------------------------------
12345 3 Black

If I had that much I could probably figure out the rest. Basically I want to find records that do not have any duplicates for race.

 

[Mile-O]

Your best way, then, would be to put this function into a module:

Public Function FindDuplicates(ByVal x As String, y As String, z As String) As Boolean

    If x = y And y = z And x = z Then FindDuplicates = True

End Function

Then create a new field in your query:

NewField: FindDuplicates([Race1], [Race2], [Race3])

with the criteria: True

 

[jon98548]

Just so you know Mile isn't the only one, the table structure would be the best thing to change. I would talk to whoever it belongs to about normalising the data. Honestly, it doesn't sound like they have the firmest grip on Access.

 

[Mile-O]

Just want to say I thought you were looking for all where Race was duplicated.


The function will need to be written to accommodate what you want but you get the idea....

 

[Jon K]

Since you want to find non-duplicate races in different records, you can first use a union query to put all the races in one column and then exclude those that are duplicated.

I have put your sample data in the attached DB, which is saved in Access 97 format from Access 2000.

You can open the DB and run QueryThree.