Thanks a lot Sergey but I have another twist I forgot to mention which I should have. Actually, instead of using a theoretical example trying to keep it simple, I'll just provide my real problem (which I should of from the beginning).
I have a table named "ListOrder" which hold mailing lists our company orders (e.g. Readers Digest, Forbes, etc.). Important fields are:
ListOrderID - pkey
MailingOrderID - FKey to "MailingOrder" to which mailing campaign it belongs.
ListID - Fkey to "Datacard" which holds mailing lists.
SelectID - Fkey to "Selection" which holds particular selection being ordered (e.g. Last 6 month subscribers, L12M, etc.)
I have a "Count" table that holds counts (number of subscribers to Readers Digest for example) by state. Layout:
CountID - Pkey
ListID
SelectID
CountAsOf - date the count was entered
Now, I want to run a query that'll returns one most recent "Count" record for all "ListOrder" with MailingOrderID=133. The following query which I came up with returns all "Count" records that match which I don't want.
Select DISTINCT [Count].*, Datacard.Listname, Selection.Selection
FROM [Count] JOIN ListOrder ON [Count].ListID = ListOrder.ListID
AND [Count].SelectID=ListOrder.SelectID
AND ListOrder.MailingOrderID=133
JOIN [Datacard] ON [Count].ListID = [Datacard].ListID
JOIN Selection ON [Count].SelectID = Selection.SelectID
ORDER BY Datacard.Listname, Selection.Selection
Sorry about making you think twice about this. TIA.
It's "my" world. You're just living in it.