Multiple possibilities for one field causing duplicate records for one person.

[russmann2000]

Ok, so I have an issue with a query. I have a table with a field called "license" with possibilities of "driver", "hunting", "fishing", "liquor", and such that could populate that field. One person can have multiple records because they can have all of these particular licenses. For the "hunting" and "fishing" licenses, since they are essentially the same, I want to be able to have only the "hunting" record appear in the query results if both "hunting" and "fishing" licenses appear in the table. I just can't for the life of me figure out how to do this. Your help is greatly appreciated!

[ranman256]

it the tLicense table has fields:[LicType] and [OwnerID]
then 1 person can have mutiple licenses.

query :
select * from tLicense where [LicType]='Hunting'

[russmann2000]

That’s gonna pull nothing but hunting, I want all of the other types to show as well. Only if “hunting” and “fishing” are in the table for one person, to exclude the fishing record, but all other types should show as well, “liquor”, “driver”, etc.

[orange]

A Person can have 0 or Many License(s)
A License is for Hunting or driving or fishing or liquor or ???

Person--->License<---- LicenseType

Using ranmans set up

select * from tLicense where OwnerID = 1234;

[russmann2000]

That won’t work either. It’s only gonna pull a specific person and it is not excluding the fishing license based on if a person has both a fishing and hunting license.

[orange]

Why do you store fishing license if you don't intend to retrieve it?
If you set your tables up as suggested, you can use a query or queries to get whatever you need.
The key is to identify exactly what you need; design for it and proceed.

Select * from tLicense where
OwnerID = 1234 and
licenseType <>"Fishing" etc. etc.

For more see this by Allen Browne.

[June7]

I gather you want all licenses except when a person has fishing and hunting, exclude the fishing, but if they have fishing and no hunting, include the fishing. This will be tricky. Consider:

SELECT Table1.UserID, Table1.License
FROM (SELECT Table1.UserID, Table1.License AS HLic
FROM Table1
WHERE (((Table1.License)="Hunting"))) AS Query1
RIGHT JOIN Table1 ON Query1.UserID = Table1.UserID
WHERE (((IIf([License]="Fishing" And Not [HLic] Is Null,"HandF","NoH"))="NoH"));

[russmann2000]

Orange, that is not the point, the tables are set up the way they are set up. I have zero control over that. I have to work with what i have.

June7, thank you so much. I will work with that.