Using JOIN Clauses

Using JOIN Clauses

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

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.

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

SELECT s.SalesField, r.RepField, etc.FROM TBLSALES s, TBLREP rWHERE = r.idAND = AND s.SalesID = …AND s.SalesYear = …etc.
The problem you were having is that you were not putting the “ = ” condition in your where statement.


Share the Post: