Proble with Joining Tables

[manoj91]

Hi...............

I'm new to MS Access. I'm having 3 tables namely

JobInfo table used to maintain Information of the jobs received.
1. JobID #
2. JobName
3. JobSize


JobProductionStart table (a record for related job is inserted when production started for the job it may not contain records for those jobs for which productin is yet to start)
1. JobID #
2. ProductionStartDate


JobStatusInfo table is updated when a Job enters into a new step.
This table contain multiple records for a single job
1. TransactionID #
2. JobID $
3. Status
4. StatusUpdateDateTime

# Primary Key
$ Foreigh Key

Problem :-

I want all records form JobInfo table with ProductionStartDate from JobProductionStart table and Latest status from JobStatusInfo.




SELECT JI.JobId AS JobId, JI.ProjectID AS ProjectID, JI.JobName AS JobName, JI.JobSize AS JobSize, PS.ProductionStartDate AS ProductionStartDate, SI.Status AS Status, SI.Remarks AS Remarks, SI.TransactionID
FROM (JobInfo AS JI LEFT JOIN JobProductionStart AS PS ON JI.JobID=PS.JobID) LEFT JOIN JobStatusInfo AS SI ON JI.JobID=SI.JobID
WHERE JI.ProjectID='ADC0003';


I have written this query but it is showing dupelicate records from JobStatusInfo but I need those records which is having max(transactionID) for each JobID. (Idea behind that is I need latest status for a job from JobStatusInfo table)


Please provide some solution. I will be very much thankful to you.

Bye.................
Manoj