If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Subtotals
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. |
#2
|
|||
|
|||
Subtotals
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. |
#3
|
|||
|
|||
Subtotals
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. "John Spencer" wrote in message ... 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. |
#4
|
|||
|
|||
Subtotals
"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 Andy wrote: 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. "John Spencer" wrote in message ... 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. |
#5
|
|||
|
|||
Subtotals
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. "John Spencer" wrote in message ... "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 Andy wrote: 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. "John Spencer" wrote in message ... 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. |
Thread Tools | |
Display Modes | |
|
|