WHERE something exists and something else doesn't...

[BonnieG]

I have a table called user_change an within that I record the changes that have been made to particular users.

The fields I want to query are:

username
change_type

This second one is a text field which has a value of the type of change.

I want to find all of my users who've requested a card and haven't had one printed yet. I need to pull out all usernames which have records assigned to them with a change_type "card requested" but do NOT have ANY records assigned to them with the change_type "card printed". (Some of my users won't have requested a card - they will have just had one printed for them. I don't need to know about these.)

So I might have the following data...

usernamechange_typeANNECcard requestedADAMAcard printedADAMBcard printedADAMDcard requested ADAMDcard printedADAMMcard printedADAMScard requested ADAMWcard requestedADELEDcard printedADELEHcard requested
My query would show me only ANNEC, ADAMS, ADAMW and ADELEH, as those are the only two for whom a record exists for "card requested" but not for "card printed".

Any ideas?

 

[JHB]

Try to post some sample data in MS-Access database or Excel + the query you use.

 

[BonnieG]

Sorry I'm confused... I posted some sample data above, and I don't have the query as I don't know how to write it?

 

[JHB]

Sorry I'm confused... I posted some sample data above, and I don't have the query as I don't know how to write it?

Sorry - maybe for whom who knows the data can see what it is, for others is a long line of mixed letters. So a table in MS-Access so we can see the field names and sample data, might do wonders to get a solution.