[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

how to count in access query

Posted on 2016-08-11
11
Medium Priority
?
72 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
[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
11 Comments
 
LVL 39

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 39

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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 46

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 46

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 39

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 46

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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

650 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