Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Query two related tables - help!

Status
Not open for further replies.

sjh

Programmer
Oct 29, 2001
263
US
I have a products table and sale rate table.

Products table contains all the products and their original cost. Sale rate table contains only the products that are on sale, and their discount rate.

I want to create a query that calculates the final discount price for all the products.

Products table: All products
ProductID
OrigPrice

SalesRate table: Only the products that are on sale
ProductID
DiscountRate

What I want is FinalPrice table: All products
ProductID
FinalPrice = OrigPrice * ((100-DiscountRate)/100)

I don't know how to create a query that will display all the productIDs. My query only returns the final price of products that are on sale.

Please help! Thank you. - sjh
 
The solution is in the type of join you do (Join Properties). If you can see your query in Design View then click on the arrow connecting your two tables. Select the option where you select all the products from the product table but only those records form the sale table where the keys are equal. This will allow you to see all your products and the sale info for only those products that are on sale. If you can't see you query in Design view below see a sql statement where I'm extracting the parent data from the parent table and only the email for those parents who have provided email addresses. If I did a regular join I would only get parents who had email ids. This way, parents without an email id will have nothing returned in the email field.

SELECT tblParents.ParentID,
tblParents.ParentFirstName,
tblParents.ParentLastName,
tblParents.ParentEmailID,
tblEmail.EmailID,
tblEmail.Email,
tblEmail.EmailInclude
FROM tblEmail RIGHT JOIN tblParents ON tblEmail.EmailID = tblParents.ParentEmailID;
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top