Improve company productivity with a Business Account.Sign Up

x
?
Solved

how to count in access query

Posted on 2016-08-11
11
Medium Priority
?
75 Views
Last Modified: 2016-10-10
Quantity                          Accounting Code Description
2                                    HVAC Comm Upstream 123
3                                 HVAC Comm Upstream 444
4                                 HVAC Comm Upstream  555
2                                 HVAC Res QI 55
4                                 HVAC Res QI 222
1                                 HVAC Res 555
1                                 HVAC Res 55

there are two groups i need to count the total of Quantity, should two lines group by HVAC COMM and HVAC Res:
HVAC Comm       9
HVAC Res       8


Please
could you help me to write the query in access?
0
Comment
Question by:rfedorov
9 Comments
 
LVL 42

Expert Comment

by:PatHartman
ID: 41752927
This is made more difficult by not properly normalizing the table.

Select Quantity, [Accounting Code Description], IIf(Left([Accounting Code Description], 8) = "HVAC Res", "HVAC Res", "HVAC Comm") As AccountingGroup
From YourTable;

Then you can group by the AccountingGroup field in your report.

You can also do a totals query but queries only have one level of aggregation so you can't see both detail and summary in the same query without running separate queries and unioning them using artificial fields to cause the recordset to sort like a report.  Just run a report.
0
 

Author Comment

by:rfedorov
ID: 41752996
thank you, no, i want one query, do not need details, looks like i need Switch function...
the results should be
HVAC Comm       9
HVAC Res            8
0
 
LVL 42

Assisted Solution

by:PatHartman
PatHartman earned 500 total points
ID: 41753073
Once you take the query I posted and change it to a totals query, you end up with:

Select Sum(Quantity) as SumOfQuantity, [Accounting Code Description], IIf(Left([Accounting Code Description], 8) = "HVAC Res", "HVAC Res", "HVAC Comm") As AccountingGroup
From YourTable
Group By
[Accounting Code Description], IIf(Left([Accounting Code Description], 8) = "HVAC Res", "HVAC Res", "HVAC Comm");

All you needed to do was to press the sigma button.
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
LVL 2

Assisted Solution

by:Antonio Salva Ripoll
Antonio Salva Ripoll earned 500 total points
ID: 41753074
Hi.

I think what you need is a summing query, perhaps this is what you are looking for:

SELECT Sum(Quantity) AS QuantitySum, IIf(Left([Accounting Code Description],8)="HVAC Res","HVAC Res","HVAC Comm") AS Expr1
FROM MyTableName
GROUP BY IIf(Left([Accounting Code Description],8)="HVAC Res","HVAC Res","HVAC Comm");

Open in new window


Open a new query and select the SQL view, paste my code in the window and replace MyTableName with your table name.

Save the query. When you run it, it returns you the expected results.

Best regards.

Antonio. (Barcelona, Spain)
0
 
LVL 31

Accepted Solution

by:
hnasr earned 500 total points
ID: 41753546
Try:
table: Table7
Quantity          Accounting Code Description
2                HVAC Comm Upstream 123
3               HVAC Comm Upstream 444
4               HVAC Comm Upstream  555
2               HVAC Res QI 55
4               HVAC Res QI 222
1               HVAC Res 555
1               HVAC Res 555

Query:
SELECT IIf(InStr([Accounting Code Description],"HVAC Comm")>0,"HVAC Comm",IIf(InStr([Accounting Code Description],"HVAC Res")>0,"HVAC Res",0)) AS [Group], Sum(Table7.Quantity) AS SumOfQuantity
FROM Table7
GROUP BY IIf(InStr([Accounting Code Description],"HVAC Comm")>0,"HVAC Comm",IIf(InStr([Accounting Code Description],"HVAC Res")>0,"HVAC Res",0));

Open in new window

Output:
Group      SumOfQuantity
HVAC Comm           9
HVAC Res                 8
0
 
LVL 47

Assisted Solution

by:aikimark
aikimark earned 500 total points
ID: 41754017
Please test this
Select Left([Accounting Code Description], Instr(6, [Accounting Code Description], " ")-1) As AccountingGroup, 
Sum(Quantity) As SumOfQuantity
From YourTable
Group By Left([Accounting Code Description], Instr(6, [Accounting Code Description], " ")-1)

Open in new window

0
 
LVL 47

Expert Comment

by:aikimark
ID: 41772209
I think my comment should be added to the list of correct solutions
https://www.experts-exchange.com/questions/28962925/how-to-count-in-access-query.html#a41754017
0
 
LVL 42

Expert Comment

by:PatHartman
ID: 41772222
@aikimark, it doesn't give the output the user was looking for.  It just shows the first 6 characters of the name.

HVAC R  and   HVAC C

vs

HVAC Res   and   HVAC Comm
0
 
LVL 47

Expert Comment

by:aikimark
ID: 41772263
No.  It starts looking for a space character, starting with the sixth position.  I tested it before I posted it.
0

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Article by: Tammy
MySQLTuner is a script written in Perl that allows you to review a MySQL installation quickly and make adjustments to increase performance and stability. The current configuration variables and status data is retrieved and presented in a brief forma…
Usually, rounding is performed by some power of 10 - to thousands, hundreds, tens, or integer - or to one, two, or more decimals. But rounding can also be done to a power of two, say, 16 or 64, or 1/32 or 1/1024, even for extreme values.
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…
Hi, this video explains a free download that you can incorporate into your Access databases, or use stand-alone for contact management. Contacts -- Names, Addresses, Phone Numbers, eMail Addresses, Websites, Lists, Projects, Notes, Attachments…

579 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