ACCESS FEED: [MS_AccessPros] TOP 1 Subquery not returning records without dates

Rabu, 27 Juni 2012

[MS_AccessPros] TOP 1 Subquery not returning records without dates

Hi All! I am redoing a query to get the current price for each listing. The original price was in the listing table and price drops are in a separate table. I created a union query from those two tables. Some houses do not have any price associated with them.

That union query is used as the basis for the query that is below. It is supposed to filter out the listings without a price and have line for each ListID with the most recent price. It's working except it's not including the records with no date (which is the field used for the top 1 subquery).

What can I do to include those missing records?

thanks!
Connie

SELECT qCurrentPrice1.ListID, qCurrentPrice1.CurrentPrice, qCurrentPrice1.Dat
FROM qCurrentPrice1
WHERE (((qCurrentPrice1.Dat) In (SELECT TOP 1 Dat
FROM qCurrentPrice1 As Dupe
WHERE qCurrentPrice1.ListID = Dupe.ListID And Dupe.CurrentPrice Is Not Null
ORDER BY Dupe.ListID, Dupe.Dat DESC)));