Solved

# how to count in access query

Posted on 2016-08-11
57 Views
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

could you help me to write the query in access?
0
Question by:rfedorov

LVL 35

Expert Comment

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

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 35

Assisted Solution

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

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 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

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));
``````
Output:
Group      SumOfQuantity
HVAC Comm           9
HVAC Res                 8
0

LVL 45

Assisted Solution

aikimark earned 125 total points
ID: 41754017
``````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)
``````
0

LVL 45

Expert Comment

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 35

Expert Comment

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

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

Question has a verified solution.

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

### Suggested Solutions

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…