help with complicated query?

[AccessIdiot]

I have three tables and three forms:
1) tbl_Survey (frm_Survey, page 1 of a tab control) has Survey_ID primary key and field Survey_Num
2) tbl_Replicate (sbfrm_Replicate, subform of frm_Survey and page 2 of a tab control) has Replicate_ID primary key, Survey_ID foreign key, and field Replicate_Num
3) tbl_Specimen_Replicate (frm_Specimen_Replicate) has Specimen_ID as primary key and Replicate_ID as a foreign key

So in other words:
tbl_Survey <--- one to many --> tbl_Replicate < --- one to many ---> tbl_Specimen_Replicate

On frm_Specimen_Replicate I would like to have a textbox that shows a concatenation of the Survey_Num and Replicate_Num and I'm not quite sure how to do that. I tried to make the control source of the textbox a sql query but that didn't pan out well. I also tried to use VBA code on the form current event but it didn't like that either. Probably because my code sucks:

DoCmd.RunSQL "SELECT tbl_Survey.Survey_Num, tbl_Replicate.Replicate_Num FROM (tbl_Survey INNER JOIN tbl_Replicate ON tbl_Survey.Survey_ID = tbl_Replicate.Survey_ID) INNER JOIN tbl_Specimen_Replicate ON tbl_Replicate.Replicate_ID = tbl_Specimen_Replicate.Replicate_ID;"

Well maybe the code isn't that bad but it's not really a concatenation - it's just a select statement.

Any ideas how I can pull this off?

Thanks!

[Rabbit]

tbl_Specimen_Replicate has the Replicate number so that's no problem. You just need to look up the Survey Number from the Replicate table. Use DLookup for that.

[AccessIdiot]

So I should stick with the VBA code on current form, then use DLookup to get the Survey Num, do a select statement to get the Rep Num, and then do a concatenate?

Is it possible to create a variable that you set to the value of the Select statement?

[Rabbit]

So I should stick with the VBA code on current form, then use DLookup to get the Survey Num, do a select statement to get the Rep Num, and then do a concatenate?

Is it possible to create a variable that you set to the value of the Select statement?

You don't need SQL at all, I assume the form is bound to the table so you have access to that records Replicate Number without having to do anything special. DLookup will get your your Survey Number, then you can concatenate.

You don't even need VBA you can just set the control source using an expression.

[AccessIdiot]

Okay I'm struggling even with getting the survey number.

I have this:

=DLookUp("[Survey_Num]","tbl_Survey_Num","[Survey_ID]= ")

but I'm not sure how to put in the criteria for getting the survey_id that is related to tbl_Replicate.Replicate_ID that is related to tbl_Specimen_Replicate.Replicate_ID.

That's why I thought I'd need SQL to do a join since tbl_Survey isn't directly related to tbl_Specimen_Replicate but only through tbl_Replicate which has both Survey_ID and Replicate_ID.

Confusing yes?

[Rabbit]

Okay I'm struggling even with getting the survey number.

I have this:

=DLookUp("[Survey_Num]","tbl_Survey_Num","[Survey_ID]= ")

but I'm not sure how to put in the criteria for getting the survey_id that is related to tbl_Replicate.Replicate_ID that is related to tbl_Specimen_Replicate.Replicate_ID.

That's why I thought I'd need SQL to do a join since tbl_Survey isn't directly related to tbl_Specimen_Replicate but only through tbl_Replicate which has both Survey_ID and Replicate_ID.

Confusing yes?

You're going about it the wrong way. You shouldn't be getting the Survey number from tbl_Survey_Num but from tbl_Replicate where the Replicate_ID is equal to the Replicate_ID in your tbl_Specimen_Replicate which you have access to.

[AccessIdiot]

Oh of course! Why didn't I think of that? Let me give it shot. :)

[AccessIdiot]

You're going about it the wrong way. You shouldn't be getting the Survey number from tbl_Survey_Num but from tbl_Replicate where the Replicate_ID is equal to the Replicate_ID in your tbl_Specimen_Replicate which you have access to.

No wait, I can't get the Survey_Num from tbl_Replicate - tbl_Replicate contains the Survey_ID but not the Survey_Num. Survey_Num only exists in tbl_Survey.

Or can I?

tbl_Survey
Survey_ID
Survey_Num

tbl_Replicate
Replicate_ID
Survey_ID

tbl_Specimen_Replicate
Replicate_ID

I've only seen examples of the DLookup function that involves two tables or forms, not three?

[Rabbit]

No wait, I can't get the Survey_Num from tbl_Replicate - tbl_Replicate contains the Survey_ID but not the Survey_Num. Survey_Num only exists in tbl_Survey.

Or can I?

tbl_Survey tbl_Replicate tbl_Specimen_Replicate
Survey_ID Replicate_ID Replicate_ID
Survey_Num Survey_ID


I've only seen examples of the DLookup table that involves two tables or forms, not three?

Well.. I suppose you could embed DLookups.

DLookup("Survey_Num", "tbl_Survey", "Survey_ID = " & DLookup(...))

[AccessIdiot]

Tried this:

=DLookUp("[Survey_Num]","[tbl_Survey]","[Survey_ID] = " & DLookUp("[Survey_ID]","[tbl_Replicate]","[Replicate_ID]=Forms![frm_Specimen_Replicate]![Replicate_ID]"))

and got #Error

Is my formatting wrong?

[Rabbit]

Looks perfectly fine. Everything's a number correct?

[AccessIdiot]

Okay so instead I have done this and got the Survey_Num just fine:

=Forms!frm_Survey!Survey_Num

But it didn't like the concatenation I tried:

=Forms!frm_Survey!Survey_Num & Forms!sbfrm_Replicate!Replicate_Num

Do I need quote marks for the concatenation or something?

[Rabbit]

Okay so instead I have done this and got the Survey_Num just fine:

=Forms!frm_Survey!Survey_Num

But it didn't like the concatenation I tried:

=Forms!frm_Survey!Survey_Num & Forms!sbfrm_Replicate!Replicate_Num

Do I need quote marks for the concatenation or something?

That works as long the forms are open and are synced to the same record.

Is sbfrm_Replicate a subform? Because the syntax for that is [Forms]![MainForm]![SubForm].Form.ControlName

[AccessIdiot]

Yep I JUST figured that out!

=Forms!frm_Survey!Survey_Num & Forms!frm_Survey!sbfrm_Replicate!Replicate_Num

works perfectly!

Thanks Rabbit :D

[Rabbit]

Not a problem.