ms中的SQL查询使用多个表字段从MS Access Form访问VBA创建
我在访问数据库中有多个表与EmpID字段有关系。所有表都有不同的员工记录，如一个名为MasterGeneralData的表具有Employee的所有常规信息，另一个名为MasterOfficialData的表只有员工的官方详细信息，即促销日期（DOP），加入日期（DOJ）等，其他一个表名为tabAddress有员工地址和联系方式。所有表都与EmpID字段有关系，这在每个表中都很常见。
我想使用任何表的任何字段执行选择查询，例如来自MasterGeneralData和DOP的EmpDOB，来自MasterOfficialData的DOJ with filter Where EmpID或DOB等
我创建了一个表单。一个combobox1（cboSelectTblQry）rowsource具有数据库的所有tablename。如果从cboSelectTblQry中选择任何表名，然后Listbox1（lstSelectFrom）加载了所选字段的所有字段名称，那么我从lstSelectFrom中选择任何必填字段（Item），此字段名称显示在ListBox2（lstSelectTo）中。我重复它直到选择从lstSelectFrom到lstSelectTo的必填字段然后我从cboSelectTblQry中选择第二个表名。我再次重复这个过程，直到选择从lstSelectFrom到lstSelectTo所需的文件。
我还有combobox2（cboWhere）和combobox3（cboEqualto），我选择过滤字段并用button1执行以下代码（cmdOpenQuery） -
I have multiple table in access database which have relationship together with EmpID field. All table have different record of employee like one table named MasterGeneralData have all general information of Employee, other one table named MasterOfficialData have only official details of employee i.e. date of promotion(DOP), date of Joining (DOJ) etc, Other one table named tabAddress have employee address and contact details. All tables have relationship with EmpID field which is common in each table.
I want to execute select query using any field of any table like EmpName, EmpDOB from MasterGeneralData and DOP, DOJ from MasterOfficialData with filter Where EmpID or DOB etc.
I created a form. One combobox1 (cboSelectTblQry) rowsource have all tablename of database. If any table name selected from cboSelectTblQry then Listbox1 (lstSelectFrom) loaded with all fields name of selected table then I choose any required field(Item) from lstSelectFrom and this field name shows in ListBox2 (lstSelectTo). I repeated it till selection of required fields from lstSelectFrom to lstSelectTo then I selects second table name from cboSelectTblQry. I again repeat this process till selection of required filed from lstSelectFrom to lstSelectTo.
I have also combobox2(cboWhere) and combobox3 (cboEqualto) where I select filter field and execute following code with button1 (cmdOpenQuery)-
我不确定是什么问题在这里。除非，我错过了一些东西，你刚刚解释了你想要做的事情。
我猜你在上班时遇到了麻烦，所以我要解决基本设计问题。如果这不是您的问题，请告诉我们。
为什么不创建单个查询，将所有表链接在EmpID上并返回所有列所有表格？然后使用该查询作为导出的基础，或者如果需要将它作为您在代码中构建的查询的基础？
然后，您不需要在正在构建的查询中将表链接在一起。只要表之间只有一对一的关系，就应该相当容易。
I''m not really sure what the Question is here. Unless, I''ve missed something, you''ve just explained what you are trying to do.
I''m going to guess you are having trouble getting it to work, so I''m going to address the basic design. If that isn''t your question, please let us know.
Why don''t you create a Single Query that links all the Tables together on EmpID and returns all the Columns from All the Tables? Then use that Query as the basis of your Export, or if needed have it as the basis for the Query that you build in Code?
Then you wont need to link the tables together in the Query you are building. As long as there is only one-to-one relationship between the tables, it should be fairly easy.
感谢回复jforbes ....
我的问题是我想在Access中构建用户友好表单，用户可以通过从表单中的组合框中选择表名并从ListBox1中选择所需的表字段名来创建查询。选定的字段将移动到ListBox2并根据ListBox2中的字段创建查询。
但是应该还有一个或两个Filter Criteria Combobox形式，用户应该选择过滤此查询。
我创建了这个表单设计并成功使用单个表字段但是对多个表字段没有成功...
亲切建议代码....
以下是我的表格..
Thanks for reply jforbes....
My question is that I want to build User Friendly Form in Access where User can create query by selecting tables name from combobox in Forms and selecting desired table fields names from ListBox1. Selected fields will be move to ListBox2 and create query based on fields in ListBox2.
But there should One Or two Filter Criteria Combobox also in form which should be selected by User to Filter this Query.
I created this Form design and successful with single table fields but couldn''t success with Multiple Table fields...
Kindly suggest the code....
Following is my Form..
这是一个非常整洁的表格，你正在那里工作。 />
我认为导致你麻烦的是加入其他表格。您可以使用查询将所有表连接在一起并将其用作表单和导出的基础，然后您不需要将代码加入表中。它会更容易，但灵活性会降低。或者，您需要在代码中创建QueryDef时定义表连接。
无论哪种方式，您都需要一个类似于以下内容的Select语句，其中包含两个（或更多）表在From子句中：
That is a very neat Form you are working on there.
I think what is causing you trouble is the Joining of the additional tables. You could use a Query to Join all your Tables together and use it as the basis of your Form and Export, then you wouldn''t need to join your Tables together in code. It would be easier, but less flexible. Or you will need to define the Table Joins when creating the QueryDef in code.
Either way you will need a Select statement that resembles the following, where both (or more) Tables are included in the From Clause: