I have a database with the following structure:
tblJobs has top level details about each job with a primary key called JobId
tblJobDetails has details about each activity associated with each job and is linked to tblJobs by fkJobId. It also has a fkActivityId field that identifies the various activities
What I need to do is create a query that will show the value of CompletedDate of fkActityId = 4 in tblJobDetails against every record, so the following query needs to somehow be changed so that the last part of the From clause effectively becomes something like:
AND (4 = tblJobDetails_1.fkJobNum);
SELECT tblJobs.Jobnum, tblJobs.*, tblJobDetails.Activity, tblJobDetails.*, tblJobDetails_1.CompletedDate AS Activity4
FROM (tblJobs INNER JOIN tblJobDetails ON tblJobs.Jobnum = tblJobDetails.fkJobNum) LEFT JOIN tblJobDetails AS tblJobDetails_1 ON (tblJobDetails.fkActivityId = tblJobDetails_1.fkActivityId) AND (tblJobDetails.fkJobNum = tblJobDetails_1.fkJobNum);
I could create a field in tblJobDetails called something like LinkTo4 and give it a default value of 4 but that seems like a waste of space. Or I could use a dlookup to calculate the value but that would be a waste of resources.
How would you do this?