how do I query last date

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a single table that contains the following two fields : [trap id] and
collection date. Each trap should be collected once per week and I want to
query the last date a trap was collected (in order to identify any missing
trap collections). I would have thought that this would have been a simple
task but I cannot get anything to work.
--
Thanks in advance for your help

Regards

Alan
 
SQL statement would look like

SELECT [Trap ID], MAX([Collection Date]) as LastCollected
FROM [YourTable]
GROUP BY [Trap ID]

In the query grid, build a new query
-- add your table
-- add the two fields
-- Select View: Totals from the menu
-- Change Group By to Max under the collection date field
 
Select [Trap ID], Max([Collection Date])
From YourTableName
Click to expand...
Group By TrapID

Hope that helps!

I have a single table that contains the following two fields : [trap id] and
collection date. Each trap should be collected once per week and I want to
query the last date a trap was collected (in order to identify any missing
trap collections). I would have thought that this would have been a simple
task but I cannot get anything to work.
--
Thanks in advance for your help

Regards

Alan
Click to expand...
 
Easy! Thanks
--
Thanks in advance for your help

Regards

Alan


John Spencer said:
SQL statement would look like

SELECT [Trap ID], MAX([Collection Date]) as LastCollected
FROM [YourTable]
GROUP BY [Trap ID]

In the query grid, build a new query
-- add your table
-- add the two fields
-- Select View: Totals from the menu
-- Change Group By to Max under the collection date field


Alan said:
I have a single table that contains the following two fields : [trap id]
and
collection date. Each trap should be collected once per week and I want to
query the last date a trap was collected (in order to identify any missing
trap collections). I would have thought that this would have been a simple
task but I cannot get anything to work.
--
Thanks in advance for your help

Regards

Alan
Click to expand...
Click to expand...
 
Alan said:
I have a single table that contains the following two fields : [trap id] and
collection date. Each trap should be collected once per week and I want to
query the last date a trap was collected (in order to identify any missing
trap collections). I would have thought that this would have been a simple
task but I cannot get anything to work.
Click to expand...


Did you try:

SELECT trapid, Max(collectiondate) as LastCollection
FROM table
GROUP BY trapid
 
Hi John

I tried what you recommend below in query design and it is not giving me the
last date... any idea why?

In the query, I have ID#, Year (expression), Month (Expression), Category, $
Amount and Check Date. I want the last check date. (The month/Year
expressions are also based on the check date, fyi) The category is an "if"
expression.

using access 2003
Thanks

John Spencer said:
SQL statement would look like

SELECT [Trap ID], MAX([Collection Date]) as LastCollected
FROM [YourTable]
GROUP BY [Trap ID]

In the query grid, build a new query
-- add your table
-- add the two fields
-- Select View: Totals from the menu
-- Change Group By to Max under the collection date field


Alan said:
I have a single table that contains the following two fields : [trap id]
and
collection date. Each trap should be collected once per week and I want to
query the last date a trap was collected (in order to identify any missing
trap collections). I would have thought that this would have been a simple
task but I cannot get anything to work.
--
Thanks in advance for your help

Regards

Alan
Click to expand...
Click to expand...
 
Back
Top