Sub-Total Query

[Smitty]

I have some data coming from an Oracle database, that I need to recreate in Access. It's much easier to pull the raw data and update the queries there than to have to pull static files from Oracle.

Here's an example:

Excel Workbook
	A	B	C	D	E	F	G	H
3	COMPANY - 701
4	LOCATION - TOTAL SMP
5	CURRENCY - USD
6	PRODUCT LINE - TOTAL PRODUCT LINE
7	ACTIVITIES - TOTAL ACTIVITIES
8		Year 09	Jan 10	Feb 10	Mar 10	Apr 10	May 10	Jun 10
9	SALES	.00	.00	.00	.00	.00	.00	.00
10	SALES - PRODUCTS AND SERVICES	.00	.00	.00	.00	.00	.00	.00
11	35000 SALES-SELLING PRICE REGULAR	.00	.00	.00	.00	.00	.00	.00
12	35001 SALES-LIQUID PRODUCT	.00	.00	.00	.00	.00	.00	.00
13	35002 SALES-GAS PRODUCT	.00	.00	.00	.00	.00	.00	.00
14	35004 SALES-EQUIPMENT	.00	.00	.00	.00	.00	.00	.00
34	FACILITY FEES	.00	.00	.00	.00	.00	.00	.00
35	35100 SALES-FACILITY FEES	.00	.00	.00	.00	.00	.00	.00
36	OTHER	.00	.00	.00	.00	.00	.00	.00
37	35201 SALES-INSURANCE CHARGE	.00	.00	.00	.00	.00	.00	.00
49	COST OF SALES	.00	.00	.00	.00	.00	.00	.00
50	COGS GENERAL	.00	.00	.00	.00	.00	.00	.00
51	INVENTORY COSTS	.00	.00	.00	.00	.00	.00	.00
52	46000 COGS-COST OF GOODS SOLD	.00	.00	.00	.00	.00	.00	.00
53	46001 COGS-TRANSFER IN COST OF GOODS SOLD	.00	.00	.00	.00	.00	.00	.00
54	46002 COGS-TRANSFER OUT OF COST OF GOODS SOLD	.00	.00	.00	.00	.00	.00	.00
55	46003 FABRICATION VARIANCE	.00	.00	.00	.00	.00	.00	.00
56	46004 COGS-AVERAGE COST VARIANCE	.00	.00	.00	.00	.00	.00	.00
57	46005 COGS-INVOICE PRICE VARIANCE	.00	.00	.00	.00	.00	.00	.00
58	46007 COGS-PURCHASE PRICE VARIANCE	.00	.00	.00	.00	.00	.00	.00
59	46008 COGS-STANDARD COST VARIANCE	.00	.00	.00	.00	.00	.00	.00
SC

Excel 2010

I'm trying to get the Category totals, as well as their individual components. I've been reading a bit about Subtotal queries with SQL, but not really wrapping my head around it at this point.

Any suggestions?

Thanks,

 

[Trevor G]

Smitty Hi,

A suggestion to get you going.

In a database select to create a new query, then select a table and then select the fields in the table, look at the icons at the top and look for the totals icon (It is the Autosum icon that you would use in Excel) this will place in groups as an extra row in the query, then select to change a group to sum or average or min or max, this then gives you groups and totals.

If this works for you then you can look at thge SQL statement via changing the view of the query, you then have the code you can adapt.

Hope this helps you!

 

[SydneyGeek]

Smitty,

Are these queries an end-point or a halfway house? If the queries are the end-point and the next step is to create a report, check out the reporting tools in Access. You can sort, group and filter to create just about any subtotal you want. Assuming that the screenshot is desired output, a report will handle that, no problem.

Denis

 

[Smitty]

Thanks guys - I'll look at both options.

Denis, yes these are the end point before being pulling into Excel to generate reports based off of those results. I had thought about reports, but decided against it as these are for data references only.

I was able to turn my crosstab query into a Pivot Table and export it, so as long as I can refresh it, it should suffice.

Thanks again!

(I love learning new stuff!)