Link to another table by fixed value

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?
Rob4077Asked:
Who is Participating?
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You may be able to use a Sub-select to show what you want, but that recordset would not be directly updateable either.

In cases like this, where you need to show RecordsX but only where ValueA>SomeValue, you'd better off doing this on a mainform/subform setup. Your mainform would show the RecordX values (i.e. your Jobs where CompletedDate of ActivityID4 equals a particular value), and your Subform would show records from your second table that would be associated with the selected record in the main form.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
What I need to do is create a query that will show the value of CompletedDate of fkActityId = 4 in tblJobDetails
That doesn't really make much sense, or I'm not reading it right.

If you want your query to show the CompletedDate for field in tblJobDetails where fkActivityID=4, then add that as a WHERE clause at the end:

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) WHERE tblJobDetails.fkActivityID=4

Or if it's in tblJobDetails_1:

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) WHERE tblJobDetails_1.fkActivityID=4

But I'm sure I'm missing something. If you could post some sample "raw" data, and show what you want from the query, we might be able to help further.
0
 
Rob4077Author Commented:
Hi Scott, Sorry it's so unclear. I've had a bad day and shouldn't have raised this question until I had more chance to think it through.

What happens is that each record in tblJobDetails relates to an Activity. However many of the activities have a dependency on the Completed date of the Activity with an Id of 4. Therefore I need to list all the activities showing their individual Completed dates and in another column the date Activity number 4 was completed as well. Does that make any more sense?

Actually I could use the suggestion you made but that would result in a non-editable recordset. If I had a field in the table with the number 4 in it that I could link to, it may result in an editable recordset which I was hoping for. The more I think about it the more I realise it can't happen the way I want and so I shouldn't have asked the question.
0
 
Rob4077Author Commented:
Yeah, that makes sense. Thanks for your input and sorry I wasted your time. I should have known this.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.