CODEWITHSUNDEEP
sqlms-accesscount
Kirs Kringle
Kirs Kringle

Reputation: 929

How to COUNT from another table in Access Database

Write a query that displays for each customer their customer code, name, total, balance(from Customer table), and their total purchases (from Invoice). This column can be called Total_purchases.

Alright, so yes this is a lab question, however I have spent a significant amount of time trying to figure out how this works.

CUSTOMER table has a foreign key in INVOCE (CUS_CODE). INVOICE keeps track by INV_NUMBER a customer can be listed in that table more then once if they have had several transactions.

I've tried a number of things my latest thing is:

SELECT CUSTOMER.CUS_CODE, CUSOMTER.CUS_FNAME + " " + CUSTOMER.CUS_LNAME as NAME,
CUSTOMER.CUS_BALANCE 
FROM (SELECT COUNT(*) as total_purchases 
FROM INVOICE WHERE CUSTOMER.CUS_CODE = INVOICE.CUS_CODE);

However, it asks for a Parameter Value for each thing.

Here is the tables:

**CUSTOMER**
CUS_CODE
CUS_FNAME
CUS_LNAME
CUS_BALANCE

**INVOICE**
INV_NUMBER
CUS_CODE
INV_DATE

Some help in understanding how to select something for another table and count it would be extremely helpful. I tried just having two SELECT, but then I get the operator error on the FROM clause.

I've tried to make this post as detailed as possible if any information seems missing or incomplete please do not hesitate to call me out in comments.

Thank You

Upvotes: 1

Views: 2974

Answers (2)

Conrad Frix
Conrad Frix

Reputation: 52645

there are a couple of approaches you could take

Just join the two table and do a count on the invoice table is one 

SELECT 
      CUSTOMER.CUS_CODE, 
      CUSOMTER.CUS_FNAME + " " + CUSTOMER.CUS_LNAME as NAME,
      CUSTOMER.CUS_BALANCE ,
      COUNT (INVOICE.CUS_CODE)

FROM CUSTOMER 
     INNER JOIN INVOICE 
     ON CUSTOMER.CUS_CODE = INVOICE.CUS_CODE

GROUP BY 
      CUSTOMER.CUS_CODE, 
      CUSOMTER.CUS_FNAME + " " + CUSTOMER.CUS_LNAME,
      CUSTOMER.CUS_BALANCE

Another way to go if you don't want to do the group by on the customer fields is to create an inline view in the from clause and join to that

SELECT 
          CUSTOMER.CUS_CODE, 
          CUSOMTER.CUS_FNAME + " " + CUSTOMER.CUS_LNAME as NAME,
          CUSTOMER.CUS_BALANCE ,
          purchasecount.total_purchases 
FROM  
          CUSTOMER 
          INNER JOIN (SELECT COUNT(*) as total_purchases , INVOICE.CUS_CODE)
                      FROM INVOICE 
                      GROUP BY INVOICE.CUS_CODE)) purchasecount
          ON CUSTOMER.CUS_CODE = purchasecount.CUS_CODE ;

Another option is to create a query and then use that in the from.

The other option is Jack's answer uses an inline view inside the select.

Upvotes: 3

Jack Pettinger
Jack Pettinger

Reputation: 2755

SELECT CUSTOMER.CUS_CODE, CUSOMTER.CUS_FNAME + " " + CUSTOMER.CUS_LNAME as NAME,
CUSTOMER.CUS_BALANCE, 
(
  SELECT COUNT(INVOICE.CUS_CODE) 
  FROM INVOICE 
  WHERE (CUSTOMER.CUS_CODE = INVOICE.CUS_CODE)
) AS Total_purchases
FROM CUSTOMER

This uses what is known as a subquery. In the WHERE clause in the subquery note CUSTOMER.CUS_CODE = INVOICE.CUS_CODE.

This is where the invoice table is mapped to the customers table. Hence, the subquery uses the outer query CUSTOMER.CUS_CODE

Upvotes: 4

Related Questions