How to join queries in VBA

[dkwong]

I would like to do the following in VBA or a mix of Access objects and VBA. Here is some pseudocode:

- Assign the following SQL to a variable or recordset: "SELECT Employee_ID, Employee_Name, Employee_Phone FROM Employee WHERE " & Forms!EmployeeForm.Filter i.e. the filter specified on the previous form
- Take the above query and join it with the Receipts table by Employee_ID to get Receipts.Amount
- Set the joined query as the Recordsource of the report

Any ideas? I'm stuck on how to do this exactly. Thanks!

 

[kevinclark]

Just a suggestion:
If your doing all this in Access to begin with why not try the following:

Step1 - Create a Form (ie; Name = Form2)
Step2 - Add a one text box to form (ie; Name = Text0)
Step3 - Create the following query (Cut & paste below)
call it EReceipts

SELECT E.Employee_ID, E.Employee_Name, E.Employee_Phone, Sum(R.Reciept_Amount) AS Sum_Of_Reciepts
FROM Employee AS E INNER JOIN Reciepts AS R ON E.Employee_ID=R.Employee_ID
GROUP BY E.Employee_ID, E.Employee_Name, E.Employee_Phone
HAVING (((E.Employee_ID)=[Forms]![Form2]![Text0]));

Step4 - Create a Report (Use the wizard and base it on the
the query named EReceipts
Step5 - Add a command button to Form2 (Use the wizard to
have it launch the report)
Step6 - Open the form - add the employee number in the
text box and press the command button.

 

[dkwong]

Thanks, Kevin. The reason I'm doing this is because I'm trying to do a workaround. The underlying query for the report is the following:

"SELECT Employee.Employee_ID, Employee.Employee_Name, Employee.Employee_Phone, Sum(Receipts.Amount) FROM Employee, Receipts INNER JOIN Employee.Employee_ID ON Receipts.Employee_ID."

An example of a filter specified on the form by right clicking on a field and selecting one of the filter options may contain Employee_ID in it e.g. "Employee_ID IN (...". This becomes the Filter property of the report. The problem is, it won't know which Employee_ID the filter is referring to in the query i.e. could be Employee.Employee_ID or Receipts.Employee_ID.

Hope this makes sense. So I was thinking maybe I can join the queries in VBA code to get around this problem. Any ideas on how to resolve this?

 

[kevinclark]

Sorry dkwong if I am still not understanding but could you not just use an "OR" clause in the SQL ?

"OR" clause example below (You would have to replace references to the Textboxes as required - point them to the filter ?):

SELECT E.Employee_ID, E.Employee_Name, E.Employee_Phone, Sum(R.Reciept_Amount) AS Sum_Of_Reciepts
FROM Employee AS E INNER JOIN Reciepts AS R ON E.Employee_ID = R.Employee_ID
GROUP BY E.Employee_ID, E.Employee_Name, E.Employee_Phone
HAVING (E.Employee_ID=[Forms]![Form2]![Text0]) OR (E.Employee_Name=[Forms]![Form2]![Text1]);


This above SQL joins the two tables together based on Employee_ID from both tables. But it only returns the fields as designated - thus only returns Employee_ID from the Employee Table. I am assuming that if you do not have Employee N in the Employee Table - then you would not have any receipts for Employee N in the Receipt table.

again sorry if I have still not understood