Query suggestions - sub totalling

  • Thread starter Thread starter Jamie
  • Start date Start date
J

Jamie

Hello all :)

I am stuck and not sure where to look for an answer (sub queries, use
excel). But in brief I am trying to sub-total the recordset of a table
with certain criteria. To help explain it more clearly (well I hope
so) I mocked up a table that reflects the key elements. So for
example:

Company - Refence - StockCode - T1 - T2 - T3 - Total
A - aa01 - 1234 - 1 - 1 - 1 - 3
A - aa02 - 5678 - 1 - 0 - 0 - 1
B - cc01 - 3421 - 0 - 0 - 1 - 1
B - cc01 - 3421 - 1 - 2 - 3 - 6
C - dd01 - 7777 - 1 - 0 - 3 - 4
C - aa02 - 4242 - 3 - 1 - 1 - 5
C - ee05 - 4242 - 3 - 2 - 1 - 6

I am trying to subtotal certain combinations of fields (much like sub-
totalling function in Excel). I have sorted the data in order of
[company] and [StockCode]. I am struggling with the concept of sub-
totalling using SQL. I would like to be able to sub-total several
fields on duplications of the stockcode, with the end result being one
record for each stockcode with sub-totalled quantities across the
T1,2,3 fields.

If I can achieve that in SQL I would then like to be able to repeat as
before but group by [company] then group by [reference], then sub-
total the T1,2,3 fields by [stockcode] (listed by company &
reference).

I apologise for the format of the question, if it makes it clearer I
kind of want to emulate the 'Subtotal' data function in Excel. However
I find Excel a headache due to the various data types in my tables,
and the end result leaves the formatting a mess (most fields not
displayed on the sub-total rows).

If some one could advise if it's relatively easy, and suggest the
direction to look into. I am guessing it should be possible if I use
sub-queries, but I wouldn't know where to start.

Any advise would be appreciated.

Thanks
Jamie
 
It would help if you showed what your actual data looked like. What gets
totaled to result in T1, T2, and T3?
 
Company - Refence - StockCode - T1 - T2 - T3 - Total
A - aa01 - 1234 - 1 - 1 - 1 - 3
A - aa02 - 5678 - 1 - 0 - 0 - 1
B - cc01 - 3421 - 0 - 0 - 1 - 1
B - cc01 - 3421 - 1 - 2 - 3 - 6
C - dd01 - 7777 - 1 - 0 - 3 - 4
C - aa02 - 4242 - 3 - 1 - 1 - 5
C - ee05 - 4242 - 3 - 2 - 1 - 6

I am looking to total the numberic values of Columns T1, T2 and T3
where the StockCode is duplicated (where the supplier and reference
are the same). Effectively where (Supplier and or Reference are the
same) sum columns T1, T2, and T3, in one row.

Apologies its been a long day.
Jamie
 
It is just a regular totals query --
SELECT Company, Refence, StockCode, Sum(T1) AS T1_Total, Sum(T2) AS
T2_Total, Sum(T3) AS T3_Total, Sum(Nz(T1,0) + Nz(T2,0) + Nz(T3,0) AS Total
FROM YourTable
GROUP ON Company, Refence, StockCode;
 
Back
Top