Set up distict count in Design View?

I give up! Please help...

I am creating a simple query with two tables. First table INVOICE has inv_number, cus_code  and Second table LINE has inv_number, line_number, line_units, and line_price.

Output should show invoice.cus_number, #of invoices (count(distinct line.inv_number), and total customer purchases (sum(LINE units* LINE_price).

My SQL view looks like this:
SELECT INVOICE.CUS_CODE, Count(LINE.INV_NUMBER) AS [Number of Invoices], Sum(LINE.LINE_UNITS*LINE.LINE_PRICE) AS [Total Customer Purchases]
FROM INVOICE INNER JOIN LINE ON INVOICE.INV_NUMBER = LINE.INV_NUMBER
WHERE (((INVOICE.INV_NUMBER)=[LINE].[INV_NUMBER]))
GROUP BY INVOICE.CUS_CODE
ORDER BY INVOICE.CUS_CODE;

Everything looks good except my #of invoices is incorrect because it will count every line item instead of every invoice number. I know that I need to put Count(DISTINCT LINE.INV_NUMBER) so that it will only count an INV_NUMBER once no matter how many lines it comes across but when I put the DISTINCT in the SQL view then I get a syntax error.

I have tried everything I know in the design view to get the count to be correct so that I can see the proper SQL syntax but I can't figure it out. As well, I have changed my 'group by' trying to get the correct answer as well.



hinkleykAsked:
Who is Participating?
 
gmavConnect With a Mentor Commented:
Hi again.

 you can substitude your [Number of Invoices] field with an expretion such as "dcount".
your  sql should be like:

"SELECT INVOICE.CUS_CODE, DCount("[inv_number]","invoice","[cus_code] = " & [cus_code]) AS [Number of Invoices], Sum(LINE.LINE_UNITS*LINE.LINE_PRICE) AS [Total Customer Purchases]
FROM INVOICE INNER JOIN LINE ON INVOICE.INV_NUMBER = LINE.INV_NUMBER
GROUP BY INVOICE.CUS_CODE
ORDER BY INVOICE.CUS_CODE;"


gmav
0
 
gmavCommented:
Hi.
Why dont you put your # of invoices in a different query. like "select  cus_code, count(inv_number) as no_of_invoices from invoice"

then include it in your master query.

just a small thought!
0
 
hinkleykAuthor Commented:
I would like to have it all done in one query. I am sure there is a way... it just isn't coming to me. I can't believe that a two table query is causing me such a headache. Thanks to anyone who can show me how to do this in one query.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
gmavCommented:
one other idea is to use "count( select distinct inv_number from line .......).
havent tested.
I would like some data samples if it is possible.

0
 
hinkleykAuthor Commented:
gmav:
Looked in your profile to get your email addr so I could email you my .mdb but couldn't find your address. My address is hinkleyk@hotmail.com if you want to send me a msg in private and I will send the database to you. This was for a homework asgn for which I have actually turned in already but I am still interested in knowing the nitty gritty on how to do it. As well, I asked my professor to send me the solution too. Anyway, I appreciate your help and will send the file as soon as you send me an email address. Thanks.
0
 
andyguaCommented:
hinkleyk, I've had the same problem...it's because Access' SQL are not quite SQL, so there are things as COUNT DISTINCT that won't work...

What I've done is to do it via 2 queries...in your case, you could first have a query which groups all of you invoices. Then, over this query, you could "select distinct" INV_Number.

It's kind of tricky, but I tried to explain it as clear as possible. You'll have to give it some tries, but it's possible to be done.

Andy.
0
 
hinkleykAuthor Commented:
gmav:
Recieved your email and the attached solution.  I appreciate your help. The time frame was not an issue so the delay was not important. Have a good weekend. Thanks again.
0
All Courses

From novice to tech pro — start learning today.