Access query help

sligo_dave

I have an access table which contains and id code and quantity for each code. However, i want to total by A-Z main code, not by the sub code of each letter. Query results is as below, need to have just as A total, B total, etc. The below Group by and Sum counts up the sub codes but need to figure out how to get the A-Z groups only. Any advice help please?


ID Code Quantity
A1.1 2
A1.2 3
A1.3 1
A1.4 4
A1.5 5
A1.6 1
A1.7 2
A1.8 5
B1.1 4
B1.2 3
B1.3 2
C1.1 11
C1.2 5
C1.3 4
C1.4 6

pillphil

Edit: Not sure if you have two or three columns in your table?

in your query, click the down arrow on the view button, select SQL view


Assuming your query looks like

SELECT DISTINCTROW Table1.code, Sum(Table1.quant) AS [Sum Of quant]
FROM Table1
GROUP BY Table1.code;

Change it to

SELECT DISTINCTROW LEFT(Table1.code, 1), Sum(Table1.quant) AS [Sum Of quant]
FROM Table1
GROUP BY LEFT(Table1.code, 1);

sligo_dave

pillphil wrote: »

Edit: Not sure if you have two or three columns in your table?

in your query, click the down arrow on the view button, select SQL view


Assuming your query looks like

SELECT DISTINCTROW Table1.code, Sum(Table1.quant) AS [Sum Of quant]
FROM Table1
GROUP BY Table1.code;

Change it to

SELECT DISTINCTROW LEFT(Table1.code, 1), Sum(Table1.quant) AS [Sum Of quant]
FROM Table1
GROUP BY LEFT(Table1.code, 1);

Thank you, that worked a treat! One other question, is it possible to have each of the A to Z rows populate in an access form?

pillphil

I'm not too familar with access tbh, but i you should be able to create a report from that query. or use the report wizard if you want them all on the one page

EDIT: sorry, you wanted a form, should also be possible

sligo_dave

pillphil wrote: »

I'm not too familar with access tbh, but i you should be able to create a report from that query. or use the report wizard if you want them all on the one page

No bother, thanks

pillphil

Select the query, go to the create tab on the ribbon and click form. i think that's what you're looking for?