Query Help

[jennifer]

I was wondering how to grab the most recent data. I see how to grab the
"last" and "max", but it is not grabbing the correct data. For example

Date Store
6/27/2008 294

8/15/2008 222

I am wanting the query to grab the store 222, but when i use last or max it
is grabbing store 294. Is there a way to do this?

Thank you so much!

Jennifer

 

[Douglas J. Steele]

SELECT Store
FROM MyTable INNER JOIN
(SELECT Max([Date]) As MostRecent
FROM MyTable) As Subq
ON MyTable.[Date] = Subq.MostRecent

Note that Date is not a good name for a field. It's a reserved word, and can
lead to problems. For a comprehensive list of names to avoid, see what Allen
Browne has at http://www.allenbrowne.com/AppIssueBadWord.html

If you cannot (or will not) rename the field, at least put square brackets
around it, as I've done above.

 

[Jeff Boyce]

As Doug points out, you need the Max of the [Date] field, not the Max of the
[Store] field.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[jennifer]

Ok so if right now my query is

SELECT Advances.PS, Last(Advances.Partner_Name) AS LastOfPartner_Name,
Max(Advances.Date) AS MaxOfDate, Last(Advances.[Payout Store]) AS
[LastOfPayout Store], Sum(Advances.Amount) AS SumOfAmount, Termed.[Term
Date], Count(Advances.Description) AS CountOfDescription,
Count(Advances.[Payout Store]) AS [CountOfPayout Store]
FROM Advances LEFT JOIN Termed ON Advances.PS = Termed.ID
GROUP BY Advances.PS, Termed.[Term Date]
HAVING (((Max(Advances.Date))>#11/29/2007#));

Where should I place the statement below?

Thanks

SELECT Store
FROM MyTable INNER JOIN
(SELECT Max([Date]) As MostRecent
FROM MyTable) As Subq
ON MyTable.[Date] = Subq.MostRecent

Note that Date is not a good name for a field. It's a reserved word, and can
lead to problems. For a comprehensive list of names to avoid, see what Allen
Browne has at http://www.allenbrowne.com/AppIssueBadWord.html

If you cannot (or will not) rename the field, at least put square brackets
around it, as I've done above.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I was wondering how to grab the most recent data. I see how to grab the
"last" and "max", but it is not grabbing the correct data. For example

Date Store
6/27/2008 294

8/15/2008 222

I am wanting the query to grab the store 222, but when i use last or max
it
is grabbing store 294. Is there a way to do this?

Thank you so much!

Jennifer

 

[jennifer]

Is there Anyway that you can tell me where to put the SQL statement that you
provided(shown below) within the SQL statement that is already there? :

SELECT Store
FROM MyTable INNER JOIN
(SELECT Max([Date]) As MostRecent
FROM MyTable) As Subq
ON MyTable.[Date] = Subq.MostRecent

My SQL statement is

SELECT Advances.PS, Last(Advances.Partner_Name) AS LastOfPartner_Name,
Max(Advances.Date) AS MaxOfDate, Last(Advances.[Payout Store]) AS
[LastOfPayout Store], Sum(Advances.Amount) AS SumOfAmount, Termed.[Term
Date], Count(Advances.Description) AS CountOfDescription,
Count(Advances.[Payout Store]) AS [CountOfPayout Store]
FROM Advances LEFT JOIN Termed ON Advances.PS = Termed.ID
GROUP BY Advances.PS, Termed.[Term Date]
HAVING (((Max(Advances.Date))>#11/29/2007#));

Thanks,

Jennifer

SELECT Store
FROM MyTable INNER JOIN
(SELECT Max([Date]) As MostRecent
FROM MyTable) As Subq
ON MyTable.[Date] = Subq.MostRecent

Note that Date is not a good name for a field. It's a reserved word, and can
lead to problems. For a comprehensive list of names to avoid, see what Allen
Browne has at http://www.allenbrowne.com/AppIssueBadWord.html

If you cannot (or will not) rename the field, at least put square brackets
around it, as I've done above.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I was wondering how to grab the most recent data. I see how to grab the
"last" and "max", but it is not grabbing the correct data. For example

Date Store
6/27/2008 294

8/15/2008 222

I am wanting the query to grab the store 222, but when i use last or max
it
is grabbing store 294. Is there a way to do this?

Thank you so much!

Jennifer