Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Set up distict count in Design View?

Posted on 2003-10-27
7
Medium Priority
?
300 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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 

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

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

636 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