SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Other Forums
 MS Access
 Join & Subquery with MSACCESS - Help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rk_texas
Starting Member

3 Posts

Posted - 09/22/2012 :  18:01:00  Show Profile  Reply with Quote
I can not get the the Join and Subquery to work right on the following: "Show the FirstName, LastName, and TotalAmount of all customers who have had an order with an Item named 'Dress Shirt'. Use a join with a subquery. Present results sorted by LastName in accending order and then FirstName in decending order".

I can get the join to work OK:
SELECT FirstName, LastName
FROM (CUSTOMER INNER JOIN INVOICE ON CUSTOMER.CustomerID = INVOICE.CustomerNumber) INNER JOIN INVOICE_ITEM ON INVOICE.InvoiceNumber = INVOICE_ITEM.InvoiceNumber
WHERE Item = 'Dress Shirt'
ORDER BY LastName, FirstName DESC;

And the subquery:
SELECT FirstName, LastName
FROM CUSTOMER
WHERE CustomerID IN
(SELECT CustomerID
FROM INVOICE_ITEM
WHERE Item = 'Dress Shirt')
ORDER BY LastName, FirstName DESC;

But not the join and subquery:
ELECT FirstName, LastName, TotalAmount
FROM (CUSTOMER INNER JOIN INVOICE ON CUSTOMER.CustomerID = INVOICE.CustomerNumber) INNER JOIN INVOICE_ITEM ON INVOICE.InvoiceNumber = INVOICE_ITEM.InvoiceNumber
WHERE CustomerID IN
(SELECT CustomerID
FROM INVOICE_ITEM
WHERE Item = 'Dress Shirt')
ORDER BY LastName ASC, FirstName DESC;

The tables are:
CUSTOMER (CostomerID, Firstname, LastName, Phone, Email)
INVOICE (InvoiceNumber, CustomerNumber, Datein, DateOut, TotalAmount)
INVOICE_ITEM (InvoiceNumber, ItemNumber, Item, Quanity, UnitPrice)

Thanks

Edited by - rk_texas on 09/22/2012 20:37:42

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 09/22/2012 :  19:11:09  Show Profile  Reply with Quote
I am not very familiar with access, so this may be off. Are you trying to calculate the total amount only for Dress Shirt purchases, or are you trying to calculate the total amount for all customers who have bought at least one Dress Shirt? Assuming the latter, can you try the following?
SELECT FirstName,
       LastName,
       SUM(Quanity*UnitPrice) AS TotalAmount
FROM   (
           CUSTOMER INNER JOIN INVOICE ON CUSTOMER.CustomerID = INVOICE.CustomerNumber
       )
       INNER JOIN INVOICE_ITEM
            ON  INVOICE.InvoiceNumber = INVOICE_ITEM.InvoiceNumber
WHERE  CustomerID IN (SELECT CustomerID
                      FROM   INVOICE_ITEM
                      WHERE  Item = 'Dress Shirt')  
GROUP BY 
	FirstName,
    LastName,
    CUSTOMER.CustomerID
ORDER BY
       LastName ASC,
       FirstName DESC
Go to Top of Page

rk_texas
Starting Member

3 Posts

Posted - 09/22/2012 :  19:29:16  Show Profile  Reply with Quote
Thanks this works...

SELECT FirstName, LastName, TotalAmount
FROM (CUSTOMER INNER JOIN INVOICE ON CUSTOMER.CustomerID = INVOICE.CustomerNumber) INNER JOIN INVOICE_ITEM ON INVOICE.InvoiceNumber = INVOICE_ITEM.InvoiceNumber
WHERE CustomerID IN (SELECT CustomerID
FROM INVOICE
WHERE Item = 'Dress Shirt')
ORDER BY LastName ASC, FirstName DESC;
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000