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

By submitting your information, you agree that devx.com may send you DevX offers via email, phone and text message, as well as email offers about other products and services that DevX believes may be of interest to you. DevX will process your information in accordance with the Quinstreet Privacy Policy.


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

WEBINAR:

On-Demand

Application Security Testing: An Integral Part of DevOps


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
×
We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.
Thanks for your registration, follow us on our social networks to keep up-to-date