Solved

how to count in access query

Posted on 2016-08-11
11
45 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
11 Comments
 
LVL 34

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 34

Assisted Solution

by:PatHartman
PatHartman earned 125 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
 
LVL 2

Assisted Solution

by:Antonio Salva Ripoll
Antonio Salva Ripoll earned 125 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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 30

Accepted Solution

by:
hnasr earned 125 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 45

Assisted Solution

by:aikimark
aikimark earned 125 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 45

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 34

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 45

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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

758 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now