?
Solved

Set up distict count in Design View?

Posted on 2003-10-27
7
Medium Priority
?
298 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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 500 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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Suggested Courses

770 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