Grouping Fields (1 Viewer)

Hi all,

Need some help please. Not sure if this should be under Queries or Reports, so apologies if this is inthe wrong place....

Basically, i have a table called "Sales" and there are various Field Names.... there is a field called Store Number which is unqie and is a four digit number i,e. 4101,4102,4501,4505 etc etc..... also within this table is another field called "Openting Stock on-hand Quantity".

What i am looking to do is either run a quiery that will show the following:

[FONT=&quot]So all of the stores beginning 40.. wil have the total stock summed. Same with 41.. 42.. and 43.. etc etc

Any help will be greatly appreciated.
[/FONT]

You want to use the MID function to extract the first characters of your store field, then you can group by that to generate your query:

SELECT Mid([Store Number], 1,2) & "xx" AS StoreGroup, SUM([Openting Stock on-hand Quantity]) AS SumOfStockOnHand
FROM SALES
GROUP BY Mid([Store Number], 1,2) & "xx";

Hi Plog,

Excellent, thanks very much for that.