Distinct Count and Sum

[huyeote]

I have a table like this in MS Access database:

ID Date CustomerID InvoiceNo ItemNo Amount
1 01/01/2007 A 1 AAA 1
2 01/01/2007 A 1 BBB 2
3 01/01/2007 A 2 CCC 3
6 01/01/2007 B 5 AAA 6
7 01/01/2007 A 6 BBB 7
8 01/01/2007 B 5 BBB 8

I want to get disctinct count of InvoiceNo while summing up Amount by Date and CustomerID in one query. Firstly, I tried to use correlated query to count unique records but it failed.

SELECT A.Date, A.CustomerID, (SELECT  COUNT(B.InvoiceNo) FROM Table1 B WHERE B.Date = A.Date AND B.CustomerID = A.CustomerID ) AS CountOfDistInvNo, Sum(A.Amount) AS SumOfAmount 
FROM Table1 A
GROUP BY A.Date, A.CustomerID;

The result of Count is not a unique count. Can anyone tell me what's wrong with this correlated query?

After hours of googling on internet, the solution I got is equivalent to querying on top of the resulting table from another query.

My final query is

SELECT B.Date, B.CustomerID, Count(B.InvoiceNo) AS CountOfInvoiceNo, Sum(B.SumOfAmount) AS SumOfAmount
FROM (SELECT A.Date, A.CustomerID, A.InvoiceNo, SUM(A.Amount) AS SumOfAmount FROM Table1 A GROUP BY A.Date, A.CustomerID, A.InvoiceNo) AS B
GROUP BY B.Date, B.CustomerID;

Is there a better solution than this in Access?


Thanks,

Huyeote

 

[GohDiamond]

I Created a table Called Invoice Table using your specific example

Here is a Query Based on a Single table as you described

SELECT [Invoice Table].DateSold, [Invoice Table].CustomerID, Count([Invoice Table].InvoiceNo) AS CountOfInvoiceNo, Sum([Invoice Table].Amount) AS SumOfAmount
FROM [Invoice Table]
GROUP BY [Invoice Table].DateSold, [Invoice Table].CustomerID;

The results are
1/01/2007 Customer A had 4 invoices for total amount of 13
1/01/2007 Customer B had 2 invoices for total amount of 14


For two tables try this: Customers, Invoice Table With a relationship built on Customer ID: (I added the Customer Name from the Customers Table to demonstrate the relationship advantage)

SELECT [Invoice Table].DateSold, Customers.CustomerID, Customers.CustomerName, Count([Invoice Table].InvoiceNo) AS CountOfInvoiceNo, Sum([Invoice Table].Amount) AS SumOfAmount
FROM Customers INNER JOIN [Invoice Table] ON Customers.CustomerID = [Invoice Table].CustomerID
GROUP BY [Invoice Table].DateSold, Customers.CustomerID, Customers.CustomerName;

Results are as follows:
1/01/2007 Customer A "Alpha Company" had 4 invoices for total amount of 13
1/01/2007 Customer B "Beta Ltd." had 2 invoices for total amount of 14

I hope this helps

Goh

 

[huyeote]

GohDiamond,

Thank you for your reply.

1/01/2007 Customer A had 4 invoices for total amount of 13

But the right count of unique invoice no is 3 though there were 4 records for Customer A on 1/01/2007. What I'm looking for is the count of unique/distinct records in a field.

Huyeote

 

[Pat Hartman]

You can break the problem into two pieces. The first query needs to summarize to the customer/invoice level and sum the amount fields. The second query uses the first query as input and it can then count the invoices correctly and do the final summarization of the amounts.

 

[huyeote]

PatHartman,

That's exactly what my final query does. My question is: is there any other way of doing it?

My other question is: Why the correlated query doesn't give the right count as it does in other DB?

Cheers

Huyeote

 

[ootkhopdi]

i m also facing this problem

one table in which ID is same but regarding values are different
i want to count how many value having one ID
and how can i select IDs,which having More than One Value
pls help me