Solved

Set up distict count in Design View?

Posted on 2003-10-27
7
290 Views
Last Modified: 2012-05-04
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.



0
Comment
Question by:hinkleyk
  • 3
  • 3
7 Comments
 
LVL 1

Expert Comment

by:gmav
ID: 9626002
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
 

Author Comment

by:hinkleyk
ID: 9626369
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
 
LVL 1

Expert Comment

by:gmav
ID: 9626983
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:hinkleyk
ID: 9631533
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
 

Expert Comment

by:andygua
ID: 9651011
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
 
LVL 1

Accepted Solution

by:
gmav earned 125 total points
ID: 9658443
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
 

Author Comment

by:hinkleyk
ID: 9660753
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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now