CODEWITHSUNDEEP
sqlms-access
jGiblets
jGiblets

Reputation: 23

Summing Sales Data Access

I'm just starting out with SQL, I have been playing around with simple select queries and grouping data, now I want to pull some actually useful data out of our database for analysis. The data is organized as follows:

Access 2010 Database I didn't set it up, I know it isn't set up as it should be

I can't change data, only poll

-Customers are kept in one table

-Closed orders are kept in another table (each line item is listed with invoice #, date closed, Customer ID as well as other info)

-Archived closed orders table keep sales records that are a year + old (table is laid out exactly the same as Closed order table)

I want to start with a simple query, list all the customers from a certain branch and their past year totals. Here's what I have tried:

SELECT CUSTOMERS.Company, CUSTOMERS.[Ship City], (SELECT SUM (CLOSEDORDERS.Quant*CLOSEDORDERS.SellPrice) FROM CLOSEDORDERS WHERE CUSTOMERS.ID = CLOSEDORDERS.CustID) AS LifeTotal

FROM CUSTOMERS, CLOSEDORDERS

WHERE CUSTOMERS.Branch=33;

When I run the query, it asks me to enter a parameter value for CLOSEDORDERS.Quant. What am I doing wrong?

Upvotes: 0

Views: 56

Answers (1)

sgeddes
sgeddes

Reputation: 62861

I think this is what you're looking for with an OUTER JOIN:

SELECT CUSTOMERS.Company, 
       CUSTOMERS.[Ship City],
       SUM(CLOSEDORDERS.Quant*CLOSEDORDERS.SellPrice) AS LifeTotal
FROM CUSTOMERS 
    LEFT JOIN CLOSEDORDERS ON CUSTOMERS.ID = CLOSEDORDERS.CustID
WHERE CUSTOMERS.Branch=33
GROUP BY CUSTOMERS.Company, 
       CUSTOMERS.[Ship City]

If you only want to return matching results from both tables, then use a standard INNER JOIN instead of the LEFT JOIN.

Upvotes: 1

Related Questions