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

Kamis, 28 Juni 2012

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

Connie-

For the records that have no date (I assume a Null value), are there also
records that *do* have a date for the same listing? If not, then simply include
the records that have a Null date, like this:

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))) OR (cCurrentPrice1.Dat Is Null);

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
<http://www.viescas.com/> http://www.viescas.com/

(Paris, France)

From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of mrsgoudge
Sent: Wednesday, June 27, 2012 11:40 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [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)));

[Non-text portions of this message have been removed]