Selecting only records where both of two values exist (1 Viewer)

[John O'Malvern]

I want to select only the records of Contacts for whom BOTH car and bike match (i.e. thay have qualified on both car and bike).
I know what I've entered in the criteria box [below] doesn't work, but it represents what I want.
My gut feeling that this is very simple to achieve, but I've failed.
(PS, why has this appeared in the Sticky section?)

 

[isladogs]

1. ONLY isn't a recognised keyword in Access
2. As Category Type should only have one item per record, no single record should be both Car And Bike . . . UNLESS you are using MVFs in which case a different approach is needed.

One approach would be to do 2 separate queries filtering for Car and Bike then do a third query with an inner join to get all contacts common to both queries.

PS: Its not in the Sticky section

 

[plog]

Not that simple actually--but not too hard. Gonna take a subquery. There's a few ways to do this so I'm betting this turns into another pedantic coding contest. Here's my entry:

SELECT ContactID, MAX([Category Type]) AS Type1,  MAX([Category Type]) AS Type2
FROM tbl_Contact_Category 
WHERE ([Category Type]) = 'Bike' OR ([Category Type]='car')
GROUP BY ContactID

Name that 'sub1'. It will let you identify contacts with both a car and a bike--it doesn't do that itself, but the next query will. You will take the query you have, get rid of tblContact_Category and tblCategoryTypes and replace it with sub1. You will link sub1 to Contacts by ContactID. Then for your criteria you will bring down Type1 from sub1 and use 'Car' under it. Also bring down Type2 from sub1 and use 'Bike' under it. Run it and that will give you your results.

2 unrelated notes:

1. Don't use spaces in names. [Category Type] shouldn't have a space, it just makes coding/querying that much more difficult. Just jam them together: CategoryType

2. You don't store duplicate data. What's the point of tblCategoryTypes if you are just storing all the information in it also in tbl_ContactCategories?

 

[GaP42]

BTW Category type in tblContact_Category appears to be redundant - it should be removed as you have it in tblCategoryTypes. Re the query - think of it as two sets of data - one for bikes and one for cars - and yuo need the intersection of the two sets.

 

[John O'Malvern]

1. Yup, I know.
2 they aren't. Car and bike are different records

Really? 2 Q and then another? Just to do this?

PS Oh good. It was there at first.

 

[plog]

Is your number 2 in response to my #2? If so you missed the point. Reread my explanation. tblCategoryTypes is a subset of tbl_ContactCategories. Every field in tblCategoryTypes is in tbl_ContactCategories. That's wrong.

 

[John O'Malvern]

Not that simple actually--but not too hard. Gonna take a subquery. There's a few ways to do this so I'm betting this turns into another pedantic coding contest. Here's my entry:

SELECT ContactID, MAX([Category Type]) AS Type1,  MAX([Category Type]) AS Type2
FROM tbl_Contact_Category
WHERE ([Category Type]) = 'Bike' OR ([Category Type]='car')
GROUP BY ContactID

Name that 'sub1'. It will let you identify contacts with both a car and a bike--it doesn't do that itself, but the next query will. You will take the query you have, get rid of tblContact_Category and tblCategoryTypes and replace it with sub1. You will link sub1 to Contacts by ContactID. Then for your criteria you will bring down Type1 from sub1 and use 'Car' under it. Also bring down Type2 from sub1 and use 'Bike' under it. Run it and that will give you your results.

2 unrelated notes:

1. Don't use spaces in names. [Category Type] shouldn't have a space, it just makes coding/querying that much more difficult. Just jam them together: CategoryType

2. You don't store duplicate data. What's the point of tblCategoryTypes if you are just storing all the information in it also in tbl_ContactCategories?

Thank you! I'll give that a go when I get a chance.
Re. Notes
1 Yes, I know not to use spaces, I inherited a big, elaborate database in current use, adn don't have the time & energy to put all those things right.
2 I realise now that Category Type in tblContact_Categories is superfluous. However, given that one Contact can have many CategoryTypes (car, bike etc) AND one CategoryType can be linked to many Contacts, I think I need the tblContact_Categories (better if it were called tblContacts_n_Categories) to keep everything properly relational.
Surely this is the same situation as the classic example of needing a table between Customers and Products in Northwind (If I remember that correctly)?

 

[John O'Malvern]

Is your number 2 in response to my #2? If so you missed the point. Reread my explanation. tblCategoryTypes is a subset of tbl_ContactCategories. Every field in tblCategoryTypes is in tbl_ContactCategories. That's wrong.

Youi're right - CategoryType shouldn't be in tblContact_Categories. (the ID is superfluous, also) Just the two FKs which together form a primary key, of course.
My reasoning for having both tables is as stated in my reply to plog

 

[GaP42]

It is the Contactid in tblcontacts that is superfluous, given your explanation above, not the ContactID in tblcontact_categories. There it is used as FK to resolve the N:N relationship of contacts to categories.

 

[June7]

ContactID is primary key in tblContacts and therefore is NOT superfluous.

An entry:

SELECT tblContact_Category.ContactID, tblContact_Category.CategoryTypeID, Q1.CategoryTypeID
FROM tblContact_Category
INNER JOIN (SELECT CategoryTypeID, ContactID FROM tblContact_Category WHERE CategoryTypeID=y) AS Q1
ON tblContact_Category.ContactID = Q1.ContactID
WHERE (((tblContact_Category.CategoryTypeID)=x));

Another:
Unique identifier ID in tblContact_Category can be useful.

SELECT * FROM tblContact_Category WHERE CategoryTypeID = x AND ID IN (SELECT ID FROM tblContact_Category WHERE CategoryTypeID = y)

If you want info from related tables, just include them in the outer query.

Do some testing to see which SQL will perform best.

 

[John O'Malvern]

It is the Contactid in tblcontacts that is superfluous, given your explanation above, not the ContactID in tblcontact_categories. There it is used as FK to resolve the N:N relationship of contacts to categories.

ContactID is the primary key of the table Contacts ! (Which is the main table of the database)

 

[John O'Malvern]

AFK for a few hours, now.
Thanks for all suggestions.

 

[GaP42]

ContactID is the primary key of the table Contacts ! (Which is the main table of the database)

Yep - my misread - I can only blame the hay fever I have atm. The ID in tblContact_categories is superfluous as you suggested, if it is not defined as the PK rather than the existing composite PK.

 

[June7]

Even if not defined as PK, an ID autonumber can still sometimes be useful as a unique record identifier (see my SQL example in post 10.

tblCategory_Contact really doesn't need any PK. The two FK fields can be set as a compound index without being defined as PK.