?
Solved

how to count in access query

Posted on 2016-08-11
11
Medium Priority
?
69 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 38

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 38

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
Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit 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 30

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 38

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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

764 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