return most recent record (1 Viewer)

[kobiashi]

Hi,

im trying to create a query that will return the most recent record based on a unique number

here is me table set up

TblEventPartsLocation
EventTrackableParts_ID
EventTracker_IDFK
Vehicle_IDFK
Part_IDFK
PartLocation_IDFK
TimeStamp

TblPart
Part_ID
SerialNumber
Quantiy
TimeStamp

TblVehicle
Vehicle_ID
VehicleName
TimeStamp

TblPartLocation
PartLocation_ID
PartLocation
TimeStamp

TblPart.Part_ID is linked to TblEventTrackablePart.Part_IDFK
TblVehicle.Vehicle_ID is linked to Tbl.EventTrackablePart.Vehicle_IDFK
TblPartLocation.PartLocation_ID is linked to Tbl.EventTrackablePart.PartLocation_IDFK


its to track parts from locations, either on a vehicle or in stores

what im trying to do is create a query to return the most recent record for a part, so TblPart.SerialNumber is my unique number, and im trying to return the most recent record from TblEventPartsLocation, every time a part is added or removed from a vehicle or stores an entry is added to this table, im having trouble just returning the most recent record for TblEventTrackerPart.SerialNumber. this is to be used as a query for the combo box in both AddPart and RemovePart form, when there are no parts left allocated to stores, the combo box will return no records

 

[arnelgp]

just query using TimeStamp as criteria of your query:

select * from TblEventPartsLocation where Part_IDFK = p1 And TimeStamp = (select max(TimeStamp) from TblEventPartsLocation as T1
where T1.part_IDFK = p1);

 

[kobiashi]

hi thanks for your reply

ive tried that, but it doesnt return what i want it to.

what i need is for it to return no records when all of the parts are assigned to vehicles, so when the most recent record for TblPart.SeriaNumber where TblEventPartLocation.Vehicle_IDFK has a value and TblEventPartLocation.PartLocation_IDFK is null, for the query to return no records.

 

[plog]

I think you need to demonstrate what you want with data. Npleaee provide 2 sets:

A. Starting data from your tables. Include table and field names and enough data to demonstrate all cases.

B. Expected results if A. Show what data you expect the query to return when you feed it the data from A.

Again, 2 sets of data--starting and expected.