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!txtMonBasically 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 =The problem you were having is that you were not putting the “r.id = ” condition in your where statement.AND s.SalesID = …AND s.SalesYear = …etc.
