Subtotals

[Guest]

Hi

I have a query which brings in the following information:
Shop Month SKU Qty
Sample data might be:
AL 12 123 8
BU 12 234 1
AL 12 123 1
AL 12 234 1
AL 11 123 5

I wish to get subtotals by branch, date and SKU. The example above would
give:
AL 11 123 5
AL 12 123 9
AL 12 234 1
BU 12 234 1

I've tried every method I can think of and I'm guessing it may need multiple
queries to get the results I want. Usually I would use the query to create a
table, open it in Excel and do it there. This query brings back over 180,000
records, though, which is too big for Office 2K to handle in Excel.

Thanks.

 

[John Spencer]

SELECT Ship, [Month], SKU, SUM(Qty) as theTotal
FROM SomeTable
GROUP BY Ship, [Month], SKU

In Query design view
-- Add your table (or query)
-- Add your fields
-- SELECT View: Totals
-- Change GROUP BY to SUM under Qty


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Guest]

Hi John - and thanks for the reply.

I have already tried that - without success. I was thinking that the query
would need to be sorted. Is that not necessary? How does Access know to
subtotal by the inidividual fields? Is it just because they are included in
the query?

Thanks.
Andy.

SELECT Ship, [Month], SKU, SUM(Qty) as theTotal
FROM SomeTable
GROUP BY Ship, [Month], SKU

In Query design view
-- Add your table (or query)
-- Add your fields
-- SELECT View: Totals
-- Change GROUP BY to SUM under Qty


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Hi

I have a query which brings in the following information:
Shop Month SKU Qty
Sample data might be:
AL 12 123 8
BU 12 234 1
AL 12 123 1
AL 12 234 1
AL 11 123 5

I wish to get subtotals by branch, date and SKU. The example above would
give:
AL 11 123 5
AL 12 123 9
AL 12 234 1
BU 12 234 1

I've tried every method I can think of and I'm guessing it may need
multiple
queries to get the results I want. Usually I would use the query to
create a
table, open it in Excel and do it there. This query brings back over
180,000
records, though, which is too big for Office 2K to handle in Excel.

Thanks.

 

[John Spencer]

"I have already tried that - without success."
What does that mean? Did you get an error message, the wrong results, no
results, or something else?

Are you trying to get the totals and the details in the same query?. If so
that doesn't work - you need one query for the details and one query for the
totals.

You use a report to display the details and use grouping within the report to
display summary data.

If you HAD to do this in a query, you could write a UNION query in SQL that
would do so.


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Hi John - and thanks for the reply.

I have already tried that - without success. I was thinking that the query
would need to be sorted. Is that not necessary? How does Access know to
subtotal by the inidividual fields? Is it just because they are included in
the query?

Thanks.
Andy.

SELECT Ship, [Month], SKU, SUM(Qty) as theTotal
FROM SomeTable
GROUP BY Ship, [Month], SKU

In Query design view
-- Add your table (or query)
-- Add your fields
-- SELECT View: Totals
-- Change GROUP BY to SUM under Qty


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Hi

I have a query which brings in the following information:
Shop Month SKU Qty
Sample data might be:
AL 12 123 8
BU 12 234 1
AL 12 123 1
AL 12 234 1
AL 11 123 5

I wish to get subtotals by branch, date and SKU. The example above would
give:
AL 11 123 5
AL 12 123 9
AL 12 234 1
BU 12 234 1

I've tried every method I can think of and I'm guessing it may need
multiple
queries to get the results I want. Usually I would use the query to
create a
table, open it in Excel and do it there. This query brings back over
180,000
records, though, which is too big for Office 2K to handle in Excel.

Thanks.

 

[Guest]

Hi John

Thanks very much! That's sorted it out. I was trying to do the whole thing
in one query. I've now split it into two - one for the detail and one for
the totals - and it's worked beautifully!

Thanks!
Andy.

"I have already tried that - without success."
What does that mean? Did you get an error message, the wrong results, no
results, or something else?

Are you trying to get the totals and the details in the same query?. If
so that doesn't work - you need one query for the details and one query
for the totals.

You use a report to display the details and use grouping within the report
to display summary data.

If you HAD to do this in a query, you could write a UNION query in SQL
that would do so.


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Hi John - and thanks for the reply.

I have already tried that - without success. I was thinking that the
query would need to be sorted. Is that not necessary? How does Access
know to subtotal by the inidividual fields? Is it just because they are
included in the query?

Thanks.
Andy.

SELECT Ship, [Month], SKU, SUM(Qty) as theTotal
FROM SomeTable
GROUP BY Ship, [Month], SKU

In Query design view
-- Add your table (or query)
-- Add your fields
-- SELECT View: Totals
-- Change GROUP BY to SUM under Qty


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Andy wrote:
Hi

I have a query which brings in the following information:
Shop Month SKU Qty
Sample data might be:
AL 12 123 8
BU 12 234 1
AL 12 123 1
AL 12 234 1
AL 11 123 5

I wish to get subtotals by branch, date and SKU. The example above
would
give:
AL 11 123 5
AL 12 123 9
AL 12 234 1
BU 12 234 1

I've tried every method I can think of and I'm guessing it may need
multiple
queries to get the results I want. Usually I would use the query to
create a
table, open it in Excel and do it there. This query brings back over
180,000
records, though, which is too big for Office 2K to handle in Excel.

Thanks.