Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: Visual Basic
Expertise: Beginner
Oct 1, 1996

Using JOIN Clauses

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 TBLSALES
WHERE salesID = CustForm!txtCustID
AND   salesYear = CustForm!txtYear
AND   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 r
WHERE s.id = r.id
AND 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.
DevX Pro
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date