Totals in an MS Query

[9889]

Hi

I have a table which I need to find the latest entry date for each record where there are multiple entries for some of the people in the database. I'm using a query but it doesn't seem to be working correctly.

The fields are:

Unique Person ID
Name of School Attending
Start Date of Attending School

I need to find for each Unique Person ID the latest Start Date of Attending School and display the corresponding School Name for that date.

I've put the fields into a query and completed the following:

Unique Perso ID - Group
Start Date of Attending School - Max
Name of School Attending - Max

but I end up getting the wrong school for the Start Date of Attending School.

Can anybody help please.

Best Wishes

Angie

 

[jrbarnett]

What you are displaying is the latest start date and the latest name of the school for each person ID.
I'd guess your SQL code looks something like:

select personid, max (schoolname), max (schoolstartdate)
from schools
group by personid

whereas I'd expect it to be something like to get the data you want:

select personid, 
       max (schoolstartdate) as startdate,
       (select s2.schoolname from schools s2 where s2.personid = schools.personid and s2.schoolstartdate = schools.schoolstartdate) as schoolname
from schools
group by personid, (select s2.schoolname from schools s2 where s2.personid = schools.personid and s2.schoolstartdate = schools.schoolstartdate)
order by personid

 

[9889]

Hi

Yes you're quite right my coding does look exactly like that.

What I get when I run the query is for instance if I had a Person ID of 678 and I had two entries against it ie

01/07/07 Heath School
10/10/07 Special School

I seem to be getting something like 10/10/07 with Heath School against it but it's not consistent. It seems to happen when the entries have been put in by the user in reverse order.

Hope this makes sense.

Best Wishes

Angie

 

[jrbarnett]

Can you post the exact SQL code you have got at the moment?

John

 

[dhookom]

I just posted this SQL of the Northwind Orders table that displays the most recent 3 orders from each customer. You should be able to modify the SQL to fit your needs since you only want one top record.

SELECT Orders.OrderID, Orders.CustomerID, Orders.OrderDate, Orders.RequiredDate, Orders.ShippedDate, Orders.Freight
FROM Orders
WHERE (((Orders.OrderID) In (SELECT TOP 3 OrderID from Orders O where O.CustomerID = Orders.CustomerID ORDER BY OrderDate Desc)))
ORDER BY Orders.CustomerID, Orders.OrderDate DESC;

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[9889]

Hi

Thank you for all your help. Here is the coding I have used:

SELECT tbl_schools.YP_unique_identifier,
Max(tbl_schools.School_start_date) AS MaxOfSchool_start_date, Max(tbl_schools.School_name) AS
MaxOfSchool_name
FROM tbl_schools
GROUP BY tbl_schools.YP_unique_identifier;

Many thanks

Angie

 

[jrbarnett]

With the information that Duane and I have posted, can you not adapt your query now to return the correct information?

John

 

[9889]

Hi

Thank you both for your help. That's brilliant.

Best Wishes

Angie