Question:
I’m working with Visual Basic and Access 1.1. Currently my program uses two tables (tblrep and tblsales). The common field being tblrep.id = tblsales.id.

The first form of the program displays records from tblrep (a dynaset was used to obtain records from tblrep). A user may click on a command buttom SALES to obtain sales data for a particular rep. As soon as the SALES button is clicked, the user is prompted to enter a Year and a Month. The values for the Year and the Month entered are to be used in order to select the required records from the tblsales table. A standard SQL statement to obtain the necessary data (not using joints) would look as follows in the sales form:

SELECT *FROM TBLSALESWHERE salesID = CustForm!txtCustIDAND   salesYear = CustForm!txtYearAND   salesMon  = CustForm!txtMon

Basically I would like to get the sales of a given Representative for a given Year/Month, and only display the information for that Rep.

How can I accomplish this in VB using SQL, there does not seem to be a way to restrict the search unless I use joins, in which case I would get data for all the Reps and not just the one I want.

Answer:
This statement is the ANSI SQL to do what you want:

SELECT s.SalesField, r.RepField, etc.FROM TBLSALES s, TBLREP rWHERE s.id = r.idAND r.id = AND s.SalesID = …AND s.SalesYear = …etc.

The problem you were having is that you were not putting the “r.id = ” condition in your where statement.