Try

Select 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 
	WHERE Count.CountAsOf = ( 
		SELECT Max(CountAsOf) FROM Count Cnt 
			Where cnt.ListID  = Count.ListID 
				And Cnt.SelectID = Count.SelectID )
	ORDER BY Datacard.Listname, Selection.Selection

>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.