Hi all,

I have been sent a flat file in excel that I need to query in Access, but my head is exploding trying to determine whether I can actually perform the query that I want.

There is only one table and the structure is thus:

UUID
FamilyName
FirstName
PostTitle
and some other stuff

UUID is unique

FamilyName is one of three choices, we'll call them 1, 2 and 3
FirstName varies so we could have a structure that looks like:

FamilyName FirstName
1 Blue
2 Blue
1 Red
2 Red
3 Red
3 Green

I need to query every instance where a FirstName does not have 1, 2 and 3 (in this example, the query would return 3 Blue, 1 Green, 2 Green)

Does this makes sense? I know that FirstName should ideally sit in another table with it's own PK but unless there's a really easy way of doing that extract, then we'll have to stick with what we've got

TIA

If there can only be three choices, you can determine which first names don't have three choices using:

SELECT FirstName
FROM MyTable
GROUP BY FirstName
HAVING Count(*) <> 3

If it's really a case that you care which three FamilyName values, you can use

SELECT FirstName
FROM MyTable
WHERE FamilyName IN (1, 2, 3)
GROUP BY FirstName
HAVING Count(*) <> 3

Now that you know the FirstNames of interest, you can join them back to your table:

SELECT UUID, FamilyName, FirstName, PostTitle
FROM MyTable
WHERE FirstName IN (
SELECT FirstName
FROM MyTable
WHERE FamilyName IN (1, 2, 3)
GROUP BY FirstName
HAVING Count(*) <> 3
)

Just to ask a couple of questions as a check - can there only ever be those three (pre-determined) choices - and might there ever be duplicate entries?

Thanks for that above, Doug! Can't seem to get the first option to work (the first one seems to be all I need).

To Leigh,

Allow me to fully explain the situation and all will become clear (as mud!)

A rather large UK employer issues cards to staff - hence a database of users (hence the fields "FamilyName" and "FirstName")

A group of staff will work at one location and in order to overcome problems with lost/forgotten cards, each location has to have 3 spare cards. Each card carries a defined role for that location.

I want to query the data to see if every location has all 3 cards, or whether they are short of one or two (so that we can issue the missing cards)

So in our users database, we have, by example:

FamilyName FirstName
Card 1 Blah Site
Card 2 Blah Site
Card 1 Other Site
Card 3 Other Site

etc, etc

If you read between the lines I'm sure it will be clear

Might this employer also be a very important service provider? ;-)
(And employer of my younger brother also)

So to check though - there can be no duplicates?
You'll never have

Card 1 Blah Site
Card 1 Blah Site
Card 3 Blah Site

Or if you did - it wouldn't matter that there were two Card 1's - just that there were three cards in total?
And there will only be the requirement of three cards...

Hi Leigh,

It doesn't matter if they have two Card 1's - all that's important is that they have cards 1, 2 and 3

(indeed, the employer is the 3rd largest in the world - after the Chinese Military and Indian Railways!)

Sorry for the very slow response to this - busy busy!
I'd just asked (about the data I mean - not the NHS... D'Oh I said it) because I was wondering if you did need all three present.
i.e. Having two Card 1's is fine - as long as there is still a 2 and a 3. (i.e. 4 in total:- "1, 1, 2, 3")
(Which, I'd say, is what Doug was alluding to when he said "If it's really a case that you care which three FamilyName values"...)

Consequently - to protect yourself against, say, a "1,1,3" eventuality you could use a slightly different query.
e.g.
SELECT FirstName
FROM (SELECT DISTINCT FamilyName, FirstName FROM MyTable) X
WHERE FamilyName IN (1, 2, 3)
GROUP BY FirstName
HAVING Count(*) < 3

or to make it generic - possibly even like
SELECT FirstName
FROM (SELECT DISTINCT FamilyName, FirstName FROM MyTable) X
GROUP BY FirstName
HAVING Count(*) < (SELECT COUNT(*) FROM (SELECT DISTINCT FamilyName FROM MyTable))

But that adds some unecessary overhead if you *know* you'll always only have the 1,2,3 options.