Join & Subquery with MSACCESS - Help

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Join & Subquery with MSACCESS - Help

Author

Topic

rk_texas
Starting Member

3 Posts

Posted - 2012-09-22 : 18:01:00

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

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-22 : 19:11:09

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

rk_texas
Starting Member

3 Posts

Posted - 2012-09-22 : 19:29:16

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;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources