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?
LVL 1
Rob4077Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rob4077Author Commented:
Yeah, that makes sense. Thanks for your input and sorry I wasted your time. I should have known this.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.