Finding the highest value in a group of records

[kev747]

hi all,
How can I find the latest date value in a group of records?

if i run a query to select all records that meet a certain criteria, can i then find the record that has the latest date value, and insert it as another date value on all records selected by the query?

Thanks in advance,

Kev.

 

[Trendsetter]

The simple way to find the latest date value is to sort on the date field and have the order set to DESC - that way the latest will be the first record in the list - if you were to interrogate the query that would be record that would be first.

 

[scriverb]

Create a query with the following SQL and call it qryCriteriaSelect:
SELECT tblYourTable.CriteriaField, Max(tblYourTable.DateField) AS MaxOfDateField
FROM tblYourTable
GROUP BY tblYourTable.CriteriaField
HAVING (((tblYourTable.CriteriaField)=<criteria value>));

Create another query with the following SQL and name it as you wish. It takes in as input the above query and your table to update the table as you requested:

UPDATE tblYourTable INNER JOIN qryCriteriaSelect ON tblYourTable.CriteriaField = qryCriteriaSelect.CriteriaField SET tblYourTable.NEWDateField = [qryCriteriaSelect]![MaxOfDateField];

I have used a table name of tblYourTable and field names of CriteriaField, DateField, NEWDateField. These will have to be updated with the actual names of you table and fields. The green <criteria value> in the first query will have to be updated with whatever value you are going use to select the records.

Get back with me if you have any problems with this. We can work them and out and get this working for you.

Bob Scriver

 

[kev747]

Hi Bob,
Thanks for your time.

I've not worked in SQL before, so this is all new to me.
I created the first query as per your post, but when saving it I get the error message -

Syntax Error (missing operator)in query expression
'(((Tblmbthoffloadandreturn.emu)=<criteria value>))'.
The cursor then highlights the Less Than symbol before Criteria.

Here is my code so you can see what I've done wrong -

SELECT Tblmbthoffloadandreturn.emu,
Max(Tblmbthoffloadandreturn.date_offloaded) AS MaxOfDate_offloaded
FROM tblmbthoffloadandreturn
GROUP BY tblmbthoffloadandreturn.emu
HAVING (((tblmbthoffloadandreturn.emu)=<criteria value>));

One more thing, can I have the criteria value equal the existing value entered into a field on the current form? This would then negate the need to enter it manually.

Let me know what you think.

Thanks again,

Kev.

 

[scriverb]

You stated in your post:
if i run a query to select all records that meet a certain criteria

In my post I entered <criteria value> so that you can put that code in its place. Because you didn't identify just how you were selecting your records I left that for you to determine and enter. What is the value that you want to select? That should be entered in place of this entry.

And, Yes you can use the textbox entry by the user on your form. Forms![frmName].ControlName is the format to use. Update the form name and the Control Name.

HAVING (((tblmbthoffloadandreturn.emu = Forms![frmName].[ControlName]));

Let me know if you need more assistance.


Bob Scriver