Lookup Values from another table

[lmcc007]

I have two tables:

1. tblEvent and tblWork.

2. The tblEvent table has two fields I would like to automatically populate frmWork form with using the criteria that ActivityID = 35 Or 36. The two fields are StartDate and EndDate.

3. If ActivityID = 35, enter EventDate as StartDate.

4. If ActivityID = 36, enter EventDate as EndDate.

I tried creating a query and it is not working. It gives me both dates twice.

That is:

CompanyID StartDate EndDate
1 4/1/10 4/18/10
1 4/1/10 4/18/10
2 3/3/90 3/31/10
2 3/3/90 3/31/10​

Instead of:

CompanyID StartDate EndDate
1 4/1/10 4/18/10
2 3/3/90 3/31/10​

Basically, in my frmWork form I do not want to have to re-enter the dates for this activity.

What am I doing wrong or should I be doing something else?

Thanks!

 

[FoFa]

Well you could select your event ID, and use two subselects to select the desired date based on the event ID and the activity ID. But it would not be an updatable query (display only or report only type thing).

 

[lmcc007]

Well you could select your event ID, and use two subselects to select the desired date based on the event ID and the activity ID. But it would not be an updatable query (display only or report only type thing).

I am not understanding what you mean.

 

[boblarson]

Did you remember to link on the applicable fields? I assume that tblEvent and tblWork both have the companyID and the StartDate fields. If so, then you would link on BOTH of those fields.

 

[lmcc007]

Did you remember to link on the applicable fields? I assume that tblEvent and tblWork both have the companyID and the StartDate fields. If so, then you would link on BOTH of those fields.

I did link on CompanyID, but not StartDate field.