Query by last date?

  • Thread starter Thread starter Randy
  • Start date Start date
R

Randy

I need to pull up all records by last date in the table. I tried
putting Max in the criteria but that did not work.

Would someone help me with this............thanks
 
Randy

How? Post the SQL you used. Did you use a Totals query?

More information, please.

Jeff Boyce
<Access MVP>
 
Jeff I was able to use a Total query and use Max and this worked, but
when I do the query it will not let me use but 5 fields. When I try to
add the six field will not work. Is there something I can do so I can
add the other data fields? If I try to add this query to the report.
It starts to ask for information on the other fields like I was using a
parameter query.
 
Again, without seeing the SQL statement that your query is using, it's tough
guessing what might be happening.

One approach I've used when using a Totals query to first find a Max()/Min()
date, amount, whatever, is to create a second query, based on the first,
using what the first found and bringing in any other fields I needed. I'm
fairly sure there's a more elegant (e.g., directly in SQL, only one query)
solution, but this has worked for me.

Good luck

Jeff Boyce
<Access MVP>
 
Jeff here is the code.

SELECT Max([TRAIN2].[START_DATE]) AS MaxOfSTART_DATE, [TRAIN2].[SSN1],
[TRAINMAIN].[LAST_NAME], [TRAINMAIN].[FIRST_NAME], [TRAINMAIN].[CHIP]
FROM TRAINMAIN INNER JOIN TRAIN2 ON [TRAINMAIN].[SSN]=[TRAIN2].[SSN1]
GROUP BY [TRAIN2].[SSN1], [TRAINMAIN].[LAST_NAME],
[TRAINMAIN].[FIRST_NAME], [TRAINMAIN].[CHIP];
 
Randy

So, did you try the "chained" queries -- first a Totals query to get the max
start date, grouped by SSN), then a second query based on the first and
connected back to the underlying tables?

Jeff Boyce
<Access MVP>
 
Back
Top