Last totals value

H

hughess7

I have a query in access using the totals row to gain max and last values of
my data. Due to it being a new system we have been going through a data
cleansing exercise and adding old data which was missed. One of the fields is
supposed to identify the last person who visited a dealer, I discovered it
wasn't working as expected when it displayed a person who left the company in
2004 against a 2005 record. It is because an old record had been added and it
was looking direct at the table not using a sort order on date.

I created a new query, sorting on date ascending so the last day appeared at
the bottom of the query results (which I presumed was what 'last' means?) -
based my old query with the last total on this new query but it still reports
the wrong person.

Can anyone shed any light on this and let me know a way round it. Can post
SQL if it helps...

Thanks
Sue
 
K

Ken Sheridan

You'll probably need to use a subquery to identify the person who visited a
dealer on the latest date. A simple example would be:

SELECT Employees.*
FROM Employees INNER JOIN Appointments
ON Employees.EmployeeID = Appointments.EmployeeID
WHERE AppointmentDate =
(SELECT MAX(AppointmentDate)
FROM Appointments);

This would identify the employee(s) whose row(s) in an Appointments table
had the latest (MAX) appointment date in the table.
 
H

hughess7

Thanks for the reply Ken, where would I type this? In the criteria line of a
query or in the sql view? Tried in sql view data def but it did not produce
expected results. Syntax incorrect produced if I try to type in criteria
under specialist field.

Table defs are as follow with 1 to many relationship:

Specialists Table
SpecialistID
SpecName

Itinerary Table
DealerCode
Specialist
ReviewDate
Activity (= Audit, IA, FA or VV)
CountryCode (= B)

Thanks
Sue
 
H

hughess7

Just to clarify, I am trying to list all dealers once with the latest date
they were visited and by which specialist.

Thanks in advance for any help.
Sue
 
K

Ken Sheridan

Sue:

I think the following should do it. This uses a correlated subquery, i.e.
the subquery references the outer query, so, in this case for each row of the
outer query the subquery returns the latest review date for the current
dealer in the outer query. This subquery is in the WHERE clause of the outer
query, so the outer query returns only those rows where the review date
matches the date returned by the subquery.

Note how the two instances of the Itinerary table are distinguished by
giving them aliases I1 and I2.

The simplest way to create this query is to do the whole thing in SQL view
(you should be able to just copy and paste from here). You could do it
partly in design view and partly in SQL view, but its probably better to
hand-craft the whole thing in SQL view.

SELECT I1.DealerCode, I1.ReviewDate AS LastVisit, SpecName
FROM Specialists INNER JOIN Itinerary AS I1
ON Specialists.SpecialistID = I1.Specialist
WHERE I1.ReviewDate =
(SELECT MAX(ReviewDate)
FROM Itinerary As I2
WHERE I2.DealerCode = I1.DealerCode);

This query will only return dealers who have been visited of course, so if
you want to return a list which also includes those not yet visited then
create another query which LEFT JOINs your Dealers table to this query. The
columns for the latest visit and specialist would be Null in this final
query's result set.
 
H

hughess7

Thanks very much for this Ken! Nearly there...

I have compared the results with the old query I had (which had every dealer
once with last review date but didn't always pick up the correct person) and
there are 17 records more produced by the old query that are missing from the
new query.

I think it might be due to the fact that the table holds all dealers for
various countries and some countries can have the same dealer codes.
Therefore the where clause needs to match countrycode AND dealercode. I will
try this if I can and see if it sorts things...

I also need from the query the Max Audit Number, I presume that I can just
add the audit number field to the query grid as since the last review has
been selected this should also be the latest audit number?

Cheers
Sue
 
H

hughess7

As suspected adding the countrycode to the where clause seems to have worked
:).

One more slight problem... I need the query to exclude any visits with an
Activity value of PV. If the last visit was a PV I want it ignored and the
visit before displayed. Not too sure where to put this in the sql... ?

Thanks
Sue
 
H

hughess7

Cracked it I think :)

SELECT I1.DealerCode, I1.ReviewDate AS LastVisit, I1.Specialist
FROM Specialists INNER JOIN Itinerary AS I1
ON Specialists.SpecialistID = I1.Specialist
WHERE I1.ReviewDate =
(SELECT MAX(ReviewDate)
FROM Itinerary As I2
WHERE I2.Activity <> "PV" and I2.DealerCode = I1.DealerCode and
I2.CountryCode = I1.CountryCode);

Thanks Ken for all your help...
 
Top