query for last date on multiple records

[pbaldy]

I think you mean post #11... yours is #15!

No, I said what I meant.

 

[David R]

Careful. It's #15 if you're in Linear mode, but #11 if you're in Hybrid (and #10 if Threaded, but ugggg).

 

[pbaldy]

The OP could have asked if they were confused, but I meant this one:

http://www.baldyweb.com/LastValue.htm

 

[Cirrostratus]

If there is two queries, will the form be able to update the data displayed in the second query? I'm thinking not but I'm not 100% sure.

 

[pbaldy]

The data would be read-only if that's what you mean. This might not be:

http://allenbrowne.com/subquery-01.html#TopN

 

[Gismo]

Hi All,

Any reason why my queries and reports will show a text field with Chinese characters instead of the actual description?

 

[isladogs]

Not sure why you've posted to this old thread.
Unless you've entered these characters, it's a sign of data corruption.
If it's only a couple of records, delete them, compact and recreate the records.
If it's a lot of records, it will be more tricky.

If this answer isn't sufficient, please start a new thread

 

[ddbz]

Hi,

This is an old thread but I have the same problem as OP and am getting errors when using the solutions presented here. I am attempting to filter an ID by the most recent date while including additional information specific to the ID.

My

Example table:
ID Date Frequency
1 1/1/2020 12
1 2/3/2018 48
2 4/23/2018 48
2 7/24/2021 48
3 8/9/2017 12
3 4/23/2018 24

I am trying to return the ID's information for the most recent date:
ID Date Frequency
1 1/1/2020 12
2 7/24/2021 48
3 4/23/2018 24

I have followed the instructions provided by pbaldy but my query is only returning the IDs associated with a single date that is prompted by access upon executing the query.

my two tables are [Active Table] and [Info].

my first query [Active] looks like:
SELECT [Active Table].[ID], Max([Info].[Date]) AS [MaxOfDate1]
FROM [Active Table] INNER JOIN [Info] ON [Active Table].[ID] = [Info].[ID]
GROUP BY [Active Table].[ID];

the second query looks like:
SELECT [Active].[ID], [Info].[Frequency]
FROM [Info] INNER JOIN [Active]
ON ([Info].[ID] = [Active].[ID])
AND ([Info].[Date] = [Active].[Date])

When it's executed the prompt is "enter parameter value" for [Active].[Date] so that it only returns information for IDs with dates equal to the one inputted.


Any help is appreciated!

 

[ebs17]

SELECT
   E.ID,
   E.Date,
   E.Frequency
FROM
   ExampleTable AS E
      INNER JOIN
         (
            SELECT
               ID,
               MAX(Date) AS MaxDate
            FROM
               ExampleTable
            GROUP BY
               ID
         ) AS SQ
         ON E.ID = SQ.ID
            AND
         E.Date = SQ.MaxDate

 

[pbaldy]

When it's executed the prompt is "enter parameter value" for [Active].[Date]

As to the error, the Active query doesn't return a field named "Date":

Max([Info].[Date]) AS [MaxOfDate1]

 

[ddbz]

SELECT
   E.ID,
   E.Date,
   E.Frequency
FROM
   ExampleTable AS E
      INNER JOIN
         (
            SELECT
               ID,
               MAX(Date) AS MaxDate
            FROM
               ExampleTable
            GROUP BY
               ID
         ) AS SQ
         ON E.ID = SQ.ID
            AND
         E.Date = SQ.MaxDate

Thanks, I'll keep this in my pocket for future reference.

As to the error, the Active query doesn't return a field named "Date":

Max([Info].[Date]) AS [MaxOfDate1]

Hah, not sure how that snuck in. Correcting that fixed the prompt that I previously mentioned. I'm still getting some duplicate line items along with some blanks, but I should be able to filter those out with Is Not Null and by removing duplicates. Thanks for the help.

 

[pbaldy]

No problem.