Return last record in series 1

[HawkWeb]

Hi,

I would like to return the last entry relating to unique contacts using a query. EG

tblContacts

ID tblCar tblDrop

01 55 Dublin
02 55 Cork
03 55 Wicklow
04 55 Wexford
05 98 Cork
06 98 Waterford

Return
04 55 Wexford
06 98 Waterford

Thanks

 

[PHV]

SELECT A.ID, A.tblCar, A.tblDrop
FROM tblContacts AS A INNER JOIN (
SELECT tblCar, Max(ID) AS LastID FROM tblContacts GROUP BY tblCar
) AS B ON A.ID = B.LastID AND A.tblCar = B.tblCar;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[fredericofonseca]

How do you determine which is the last entry? is it by the ID number or by the County/City?

If the ID you can do a select ... from ... where id in (select max(id) from ... group by tblcar)

or something similar

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[HawkWeb]

Hi PHV,
Much appreciated.

Can you help with one further question??

I would like to return the last entry relating to unique contacts using a query. EG

tblContacts

ID tblCar tblDrop

01 55 Dublin
02 55 Cork
03 55 Wicklow
04 55 Wexford
05 98 Cork
06 98 Waterford

Return
04 55 Wexford
06 98 Waterford

SELECT A.ID, A.tblCar, A.tblDrop
FROM tblContacts AS A INNER JOIN (
SELECT tblCar, Max(ID) AS LastID FROM tblContacts GROUP BY tblCar
) AS B ON A.ID = B.LastID AND A.tblCar = B.tblCar

Many Thanks

I need the query to produce results depending on status of field in another table eg

tblContacts tblDrivers
ID tblCar tblDrop ID Car Finish Time

01 55 Dublin 43 13.00
02 55 Cork 55
03 55 Wicklow 58 15.30
04 55 Wexford 98
05 98 Cork 99 17.30
06 98 Waterford
07 99 Kilkenny
Return
04 55 Wexford
06 98 Waterford

 

[PHV]

What is the difference between the 2 Returns ?
Don't pick the Car 99 because its Finish Time is not null in tblDrivers ?
SELECT A.ID, A.tblCar, A.tblDrop
FROM (tblContacts AS A INNER JOIN (
SELECT tblCar, Max(ID) AS LastID FROM tblContacts GROUP BY tblCar
) AS B ON A.ID = B.LastID AND A.tblCar = B.tblCar)
INNER JOIN tblDrivers AS D ON A.tblCar = D.Car
WHERE D.[Finish Time] Is Null;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[HawkWeb]

Perfect

Thank you for your prompt and exact responce.
Other Forums unable to answer for last three days.

I'll definitely be back.

Regards

H

 

[HawkWeb]

Can you assist ??

When I select the following query as the Recordsource on my Form it works first time ............
____________________________________________
SELECT A.ID, A.tblCar, A.tblDrop
FROM (tblContacts AS A INNER JOIN (
SELECT tblCar, Max(ID) AS LastID FROM tblContacts GROUP BY tblCar
) AS B ON A.ID = B.LastID AND A.tblCar = B.tblCar)
INNER JOIN tblDrivers AS D ON A.tblCar = D.Car
WHERE D.[Finish Time] Is Null;
_____________________________________________

I then get the following Error
"The Microsoft Jet Database Engine cannot find the input Table or Query ' Select tblCar, Max(ID)AS LastID FROM tblContacts GROUP BY tblCar' Make sure it exists and its name is spelled correctly." and the Query has altered to

SELECT A.ID, A.tblCar, A.tblDrop
FROM (tblContacts AS A INNER JOIN [SELECT tblCar, Max(ID) AS LastID FROM tblContacts GROUP BY tblCar]. AS B ON (A.tblCar = B.tblCar) AND (A.ID = B.LastID)) INNER JOIN tblDrivers AS D ON A.tblCar = D.Car
WHERE D.[Finish Time] Is Null;

Any Ideas ???

H

 

[HawkWeb]

It's OK.


Problem caused by Corrupt Database.


Compact and Repair Solved Problem

Thanks