帮助复杂的查询？ [英] help with complicated query?
问题描述
我有三个表格和三个表格：
1）tbl_Survey（frm_Survey，标签控件的第1页）有Survey_ID主键和字段Survey_Num
2）tbl_Replicate （sbfrm_Replicate，frm_Survey的子表单和选项卡控件的第2页）具有Replicate_ID主键，Survey_ID外键和字段Replicate_Num
3）tbl_Specimen_Replicate（frm_Specimen_Replicate）将Specimen_ID作为主键，将Replicate_ID作为外键key
换句话说：
tbl_Survey< --- one to many - > tbl_Replicate< ---一对多---> tbl_Specimen_Replicate
在frm_Specimen_Replicate我希望有一个文本框显示Survey_Num和Replicate_Num的串联，我不太清楚如何做到这一点。我试图让文本框的控件源成为一个SQL查询但是并没有很好地完成。我也尝试在表格当前事件上使用VBA代码，但它也不喜欢。可能是因为我的代码很糟糕：
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:
推荐答案
tbl_Specimen_Replicate有复制号，所以'不是'问题。您只需从复制表中查找调查编号即可。使用DLookup。
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.
所以我应该坚持使用当前表单上的VBA代码，然后使用DLookup获取Survey Num，执行select语句以获取Rep Num，然后执行连接？
是否可以创建一个设置为Select语句值的变量？
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?
所以我应该坚持使用当前表单上的VBA代码，然后使用DLookup获取Survey Num，执行select语句以获得Rep Num，然后进行连接？
是否可以创建一个设置为Select语句值的变量？
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?
您根本不需要SQL，我认为表单绑定到表中，因此您可以访问该记录Replicate Number而无需执行任何特殊操作。 DLookup将获得您的调查编号，然后您可以连接。
您甚至不需要VBA，您可以使用表达式设置控制源。
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.
这篇关于帮助复杂的查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
