Aggregate Query (Export to Excel)

[grahamjlaws]

Hi Everyone,

I am working on a Sales Analysis db, which pulls from:

tSales - Sales Figures (40k records)
tBuildUnit - Bom Build Units (8k records - one per product)
tBom - Bills of Material - All Components (71k records)
tUom - Units of Measure for Products (80k records - 10 per product)

It is quite basic in theory, it looks at a product sold, goes to tBom to get the cost of the raw materials.

I have a query that sums the components by their group, distinguishing between material and labour cost by the component group (cGrp) which is 'WORK' for labour and <>"WORK for materials. I can then sum the cost of the materials for a given product.

Anyway to cut to the issue in hand.

I export from our system (LM4 on btrieve on novell 3.2) into CSVs then manipulate those


And basically i want to have:

Cust______Sale04__Sale05__Sale06__Mat04__Mat05__Mat06
Genesis 400 350 420 230 190 250
Exodus 0 100 0 0 60 0
Revelation 200 300 140 200

we have 220 customers in this recordset. so i would like just 220 rows.


My data that i export shows every transaction (so i have 40k records)

So i used a aggregate query, grouping by customer account and summing the value, material, and labour fields, however this gives me each customer three times, one per year e.g.:

Cust______Sale04__Sale05__Sale06__Mat04__Mat05__Mat06
Genesis 400 0 0 230 0 0
Genesis 0 350 0 0 190 0
Genesis 0 0 420 0 0 250

How do i get one what i describe above?

After the query runs i want to export to excel with:

DoCmd.OutputTo acOutputQuery, "qGrouping", acFormatXLS, "d:\Output.xls", True


Thanks

Graham
(Carpenter by trade, now in construction acoustics and really shouldn't be doing anything with access!)

 

[grahamjlaws]

Answered

Don't worry have sorted it.

i was doing an aggregate query but had left a 'Year:year(sDate)' as 'Group By' in there so of course it returned one line per year.....

Doh.