[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 302
  • Last Modified:

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.



0
hinkleyk
Asked:
hinkleyk
  • 3
  • 3
1 Solution
 
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
 
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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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
 
gmavCommented:
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
 
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now