John, Thanks once again for coming to my rescue! I'm excited because in redoing that query, the form it's used in as a subform is FAST!!! WooHoo!
Connie
--- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@...> wrote:
>
> 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]
>
Tidak ada komentar:
Posting Komentar