Running total in a query

[conyers_roger]

Hi all, I apologize if my question sounds rather simplistic, but I bet
many novices have encountered similar problems.
I'm trying to query a database table that approximately resembles
the following:

PRODUCT UNITS SOLD REGION YEAR
X 100 West 2001
X 25 South 2001
Y 50 South 2002
Z 150 North 2001


Is there a way I can create a query that summarizes the units of each
product sold on a yearly bases? i.e. for the above table that would be
125 units of Product X sold in 2001.

I tried the query's Total function and tried to use the Sum function
for various fields, but was not able to get the correct results.
Somehow, the query resuses to total the number of products sold for a
particular year. It just ends up summing up the years--for example, if
year 2001 occurs twice, it shows it as 4002--which is meaningless. Do
I have to insert a new column/field and try another formula, like DSUM?

When I created a cross-tab query, it was able to perform the summary,
but I was not able to do it in a regular query. Is there a formula
that would help me perform the calculation in a non cross-tab query?

Any response will be appreciated.
Roger

 

[Guest]

Roger:

To get an aggregated value such as a sum, avg, min, max etc you group the
query by the columns over which you want the values aggregated. In your case
this would be the Product and Year columns. You would sum the Units Sold
column. It sound like you've summed the year column. In SQL the query would
be like this:

SELECT Product.Year, SUM([Units Sold]) AS TotalNumberSold
FROM YourTable
GROUP BY Product.Year;

In query design view you first select Totals from the View menu, then leave
the Total row for Product and Year as Group By, but select Sum in the Total
row for Units Sold. Don't include the Region column in the query at all or
you'll get the values aggregated by product, year and region, which will be
exactly the same as the original data of course as each group will be just
one row.

You can also do it in a report, in which you could if you wished show both
the detailed and aggregated values. To do this you'd base the report on the
table and use the reports internal Sorting and Grouping mechanism. You would
group the report first Product, then on Year giving the Year grouping a group
footer. In the footer you'd put an unbound text box with a ControlSource of:

=Sum([Units Sold])

This would give you subtotals per year for each product. If you wished you
could also give the Product grouping a group footer and out a text box in
that with an identical ControlSource. This would give you the total numbers
of each product sold over all years.

If you only wish to show the aggregated values in a report just leave the
detail section empty and make it zero height.

You can even combine the details and the aggregated values in a query if you
wish, by using a correlated subquery like so:

SELECT Product, [Units Sold], Region, Year
(SELECT SUM([Units Sold])
FROM YourTable AS T2
WHERE T2.Product = T1.Product
AND T2.Year = T1.Year) AS TotalSoldInYear
FROM YourTable AS T1;

The aliases T1 and T2 are used to differentiate between the two instances of
the table, so that the subquery can be correlated with the outer query on the
Product and Year columns to return the sum of units sold for the product and
year of each row returned by the outer query. More usually you would do this
in a report, however, in the way I've described above.

Ken Sheridan
Stafford, England

 

[conyers_roger]

Ken,

Thank you for your reply. I tried generating a report, like you
described, and it worked. But the query still doesn't work. Somehow
the query seems to be unable to aggregate the values by year. Just
like you had described, I used the Sum and Group by features, but it
didn't work. Just wondering, could it be that
Access just isn't designed to perform such a query?

Roger

Roger:

To get an aggregated value such as a sum, avg, min, max etc you group the
query by the columns over which you want the values aggregated. In your case
this would be the Product and Year columns. You would sum the Units Sold
column. It sound like you've summed the year column. In SQL the query would
be like this:

SELECT Product.Year, SUM([Units Sold]) AS TotalNumberSold
FROM YourTable
GROUP BY Product.Year;

In query design view you first select Totals from the View menu, then leave
the Total row for Product and Year as Group By, baut select Sum in the Total
row for Units Sold. Don't include the Region column in the query at all or
you'll get the values aggregated by product, year and region, which will be
exactly the same as the original data of course as each group will be just
one row.

You can also do it in a report, in which you could if you wished show both
the detailed and aggregated values. To do this you'd base the report on the
table and use the reports internal Sorting and Grouping mechanism. You would
group the report first Product, then on Year giving the Year grouping a group
footer. In the footer you'd put an unbound text box with a ControlSource of:

=Sum([Units Sold])

This would give you subtotals per year for each product. If you wished you
could also give the Product grouping a group footer and out a text box in
that with an identical ControlSource. This would give you the total numbers
of each product sold over all years.

If you only wish to show the aggregated values in a report just leave the
detail section empty and make it zero height.

You can even combine the details and the aggregated values in a query if you
wish, by using a correlated subquery like so:

SELECT Product, [Units Sold], Region, Year
(SELECT SUM([Units Sold])
FROM YourTable AS T2
WHERE T2.Product = T1.Product
AND T2.Year = T1.Year) AS TotalSoldInYear
FROM YourTable AS T1;

The aliases T1 and T2 are used to differentiate between the two instances of
the table, so that the subquery can be correlated with the outer query on the
Product and Year columns to return the sum of units sold for the product and
year of each row returned by the outer query. More usually you would do this
in a report, however, in the way I've described above.

Ken Sheridan
Stafford, England

Hi all, I apologize if my question sounds rather simplistic, but I bet
many novices have encountered similar problems.
I'm trying to query a database table that approximately resembles
the following:

PRODUCT UNITS SOLD REGION YEAR
X 100 West 2001
X 25 South 2001
Y 50 South 2002
Z 150 North 2001


Is there a way I can create a query that summarizes the units of each
product sold on a yearly bases? i.e. for the above table that would be
125 units of Product X sold in 2001.

I tried the query's Total function and tried to use the Sum function
for various fields, but was not able to get the correct results.
Somehow, the query resuses to total the number of products sold for a
particular year. It just ends up summing up the years--for example, if
year 2001 occurs twice, it shows it as 4002--which is meaningless. Do
I have to insert a new column/field and try another formula, like DSUM?

When I created a cross-tab query, it was able to perform the summary,
but I was not able to do it in a regular query. Is there a formula
that would help me perform the calculation in a non cross-tab query?

Any response will be appreciated.
Roger

 

[Smartin]

Hi Roger,

Ken's suggestion should work, and Access certainly can aggregate data in
the way you want. Can you explain what you mean when you say "the query
still doesn't work" with examples?

It will probably help too if you paste the query's SQL here (from the
query design view, select View | SQL View. Copy all that and paste it here).

Ken,

Thank you for your reply. I tried generating a report, like you
described, and it worked. But the query still doesn't work. Somehow
the query seems to be unable to aggregate the values by year. Just
like you had described, I used the Sum and Group by features, but it
didn't work. Just wondering, could it be that
Access just isn't designed to perform such a query?

Roger

Roger:

To get an aggregated value such as a sum, avg, min, max etc you group the
query by the columns over which you want the values aggregated. In your case
this would be the Product and Year columns. You would sum the Units Sold
column. It sound like you've summed the year column. In SQL the query would
be like this:

SELECT Product.Year, SUM([Units Sold]) AS TotalNumberSold
FROM YourTable
GROUP BY Product.Year;

In query design view you first select Totals from the View menu, then leave
the Total row for Product and Year as Group By, baut select Sum in the Total
row for Units Sold. Don't include the Region column in the query at all or
you'll get the values aggregated by product, year and region, which will be
exactly the same as the original data of course as each group will be just
one row.

You can also do it in a report, in which you could if you wished show both
the detailed and aggregated values. To do this you'd base the report on the
table and use the reports internal Sorting and Grouping mechanism. You would
group the report first Product, then on Year giving the Year grouping a group
footer. In the footer you'd put an unbound text box with a ControlSource of:

=Sum([Units Sold])

This would give you subtotals per year for each product. If you wished you
could also give the Product grouping a group footer and out a text box in
that with an identical ControlSource. This would give you the total numbers
of each product sold over all years.

If you only wish to show the aggregated values in a report just leave the
detail section empty and make it zero height.

You can even combine the details and the aggregated values in a query if you
wish, by using a correlated subquery like so:

SELECT Product, [Units Sold], Region, Year
(SELECT SUM([Units Sold])
FROM YourTable AS T2
WHERE T2.Product = T1.Product
AND T2.Year = T1.Year) AS TotalSoldInYear
FROM YourTable AS T1;

The aliases T1 and T2 are used to differentiate between the two instances of
the table, so that the subquery can be correlated with the outer query on the
Product and Year columns to return the sum of units sold for the product and
year of each row returned by the outer query. More usually you would do this
in a report, however, in the way I've described above.

Ken Sheridan
Stafford, England

Hi all, I apologize if my question sounds rather simplistic, but I bet
many novices have encountered similar problems.
I'm trying to query a database table that approximately resembles
the following:

PRODUCT UNITS SOLD REGION YEAR
X 100 West 2001
X 25 South 2001
Y 50 South 2002
Z 150 North 2001


Is there a way I can create a query that summarizes the units of each
product sold on a yearly bases? i.e. for the above table that would be
125 units of Product X sold in 2001.

I tried the query's Total function and tried to use the Sum function
for various fields, but was not able to get the correct results.
Somehow, the query resuses to total the number of products sold for a
particular year. It just ends up summing up the years--for example, if
year 2001 occurs twice, it shows it as 4002--which is meaningless. Do
I have to insert a new column/field and try another formula, like DSUM?

When I created a cross-tab query, it was able to perform the summary,
but I was not able to do it in a regular query. Is there a formula
that would help me perform the calculation in a non cross-tab query?

Any response will be appreciated.
Roger

 

[conyers_roger]

Hi Smartin, and thanks for responding.

I was hoping to get a summary for each year, e.g. 125 units sold in
2001, etc. But even when I used the Sum for Units Sold, I still didn't
get the yearly summaries. Here's the query's SQL:

SELECT Table1.PRODUCT, Sum(Table1.[UNITS SOLD]) AS [SumOfUNITS SOLD],
Table1.YEAR
FROM Table1
GROUP BY Table1.PRODUCT, Table1.YEAR, Table1.REGION;

Roger

Hi Roger,

Ken's suggestion should work, and Access certainly can aggregate data in
the way you want. Can you explain what you mean when you say "the query
still doesn't work" with examples?

It will probably help too if you paste the query's SQL here (from the
query design view, select View | SQL View. Copy all that and paste it here).

Ken,

Thank you for your reply. I tried generating a report, like you
described, and it worked. But the query still doesn't work. Somehow
the query seems to be unable to aggregate the values by year. Just
like you had described, I used the Sum and Group by features, but it
didn't work. Just wondering, could it be that
Access just isn't designed to perform such a query?

Roger

Roger:

To get an aggregated value such as a sum, avg, min, max etc you group the
query by the columns over which you want the values aggregated. In your case
this would be the Product and Year columns. You would sum the Units Sold
column. It sound like you've summed the year column. In SQL the query would
be like this:

SELECT Product.Year, SUM([Units Sold]) AS TotalNumberSold
FROM YourTable
GROUP BY Product.Year;

In query design view you first select Totals from the View menu, then leave
the Total row for Product and Year as Group By, baut select Sum in the Total
row for Units Sold. Don't include the Region column in the query at all or
you'll get the values aggregated by product, year and region, which will be
exactly the same as the original data of course as each group will be just
one row.

You can also do it in a report, in which you could if you wished show both
the detailed and aggregated values. To do this you'd base the report on the
table and use the reports internal Sorting and Grouping mechanism. You would
group the report first Product, then on Year giving the Year grouping a group
footer. In the footer you'd put an unbound text box with a ControlSource of:

=Sum([Units Sold])

This would give you subtotals per year for each product. If you wished you
could also give the Product grouping a group footer and out a text box in
that with an identical ControlSource. This would give you the total numbers
of each product sold over all years.

If you only wish to show the aggregated values in a report just leave the
detail section empty and make it zero height.

You can even combine the details and the aggregated values in a query if you
wish, by using a correlated subquery like so:

SELECT Product, [Units Sold], Region, Year
(SELECT SUM([Units Sold])
FROM YourTable AS T2
WHERE T2.Product = T1.Product
AND T2.Year = T1.Year) AS TotalSoldInYear
FROM YourTable AS T1;

The aliases T1 and T2 are used to differentiate between the two instances of
the table, so that the subquery can be correlated with the outer query on the
Product and Year columns to return the sum of units sold for the product and
year of each row returned by the outer query. More usually you would do this
in a report, however, in the way I've described above.

Ken Sheridan
Stafford, England

:

Hi all, I apologize if my question sounds rather simplistic, but I bet
many novices have encountered similar problems.
I'm trying to query a database table that approximately resembles
the following:

PRODUCT UNITS SOLD REGION YEAR
X 100 West 2001
X 25 South 2001
Y 50 South 2002
Z 150 North 2001


Is there a way I can create a query that summarizes the units of each
product sold on a yearly bases? i.e. for the above table that would be
125 units of Product X sold in 2001.

I tried the query's Total function and tried to use the Sum function
for various fields, but was not able to get the correct results.
Somehow, the query resuses to total the number of products sold for a
particular year. It just ends up summing up the years--for example, if
year 2001 occurs twice, it shows it as 4002--which is meaningless. Do
I have to insert a new column/field and try another formula, like DSUM?

When I created a cross-tab query, it was able to perform the summary,
but I was not able to do it in a regular query. Is there a formula
that would help me perform the calculation in a non cross-tab query?

Any response will be appreciated.
Roger

 

[Ken Snell \(MVP\)]

Year is a reserved word, so you need to enclose it in [ ] characters:

SELECT Table1.PRODUCT, Sum(Table1.[UNITS SOLD]) AS [SumOfUNITS SOLD],
Table1.[YEAR]
FROM Table1
GROUP BY Table1.PRODUCT, Table1.[YEAR], Table1.REGION;

Why group on Region if you are not including it as a field in the output of
the query?

--

Ken Snell
<MS ACCESS MVP>

Hi Smartin, and thanks for responding.

I was hoping to get a summary for each year, e.g. 125 units sold in
2001, etc. But even when I used the Sum for Units Sold, I still didn't
get the yearly summaries. Here's the query's SQL:

SELECT Table1.PRODUCT, Sum(Table1.[UNITS SOLD]) AS [SumOfUNITS SOLD],
Table1.YEAR
FROM Table1
GROUP BY Table1.PRODUCT, Table1.YEAR, Table1.REGION;

Roger

Hi Roger,

Ken's suggestion should work, and Access certainly can aggregate data in
the way you want. Can you explain what you mean when you say "the query
still doesn't work" with examples?

It will probably help too if you paste the query's SQL here (from the
query design view, select View | SQL View. Copy all that and paste it
here).

Ken,

Thank you for your reply. I tried generating a report, like you
described, and it worked. But the query still doesn't work. Somehow
the query seems to be unable to aggregate the values by year. Just
like you had described, I used the Sum and Group by features, but it
didn't work. Just wondering, could it be that
Access just isn't designed to perform such a query?

Roger


Ken Sheridan wrote:
Roger:

To get an aggregated value such as a sum, avg, min, max etc you group
the
query by the columns over which you want the values aggregated. In
your case
this would be the Product and Year columns. You would sum the Units
Sold
column. It sound like you've summed the year column. In SQL the query
would
be like this:

SELECT Product.Year, SUM([Units Sold]) AS TotalNumberSold
FROM YourTable
GROUP BY Product.Year;

In query design view you first select Totals from the View menu, then
leave
the Total row for Product and Year as Group By, baut select Sum in the
Total
row for Units Sold. Don't include the Region column in the query at
all or
you'll get the values aggregated by product, year and region, which
will be
exactly the same as the original data of course as each group will be
just
one row.

You can also do it in a report, in which you could if you wished show
both
the detailed and aggregated values. To do this you'd base the report
on the
table and use the reports internal Sorting and Grouping mechanism.
You would
group the report first Product, then on Year giving the Year grouping
a group
footer. In the footer you'd put an unbound text box with a
ControlSource of:

=Sum([Units Sold])

This would give you subtotals per year for each product. If you
wished you
could also give the Product grouping a group footer and out a text box
in
that with an identical ControlSource. This would give you the total
numbers
of each product sold over all years.

If you only wish to show the aggregated values in a report just leave
the
detail section empty and make it zero height.

You can even combine the details and the aggregated values in a query
if you
wish, by using a correlated subquery like so:

SELECT Product, [Units Sold], Region, Year
(SELECT SUM([Units Sold])
FROM YourTable AS T2
WHERE T2.Product = T1.Product
AND T2.Year = T1.Year) AS TotalSoldInYear
FROM YourTable AS T1;

The aliases T1 and T2 are used to differentiate between the two
instances of
the table, so that the subquery can be correlated with the outer query
on the
Product and Year columns to return the sum of units sold for the
product and
year of each row returned by the outer query. More usually you would
do this
in a report, however, in the way I've described above.

Ken Sheridan
Stafford, England

:

Hi all, I apologize if my question sounds rather simplistic, but I
bet
many novices have encountered similar problems.
I'm trying to query a database table that approximately resembles
the following:

PRODUCT UNITS SOLD REGION YEAR
X 100 West 2001
X 25 South 2001
Y 50 South 2002
Z 150 North 2001


Is there a way I can create a query that summarizes the units of each
product sold on a yearly bases? i.e. for the above table that would
be
125 units of Product X sold in 2001.

I tried the query's Total function and tried to use the Sum function
for various fields, but was not able to get the correct results.
Somehow, the query resuses to total the number of products sold for a
particular year. It just ends up summing up the years--for example,
if
year 2001 occurs twice, it shows it as 4002--which is meaningless.
Do
I have to insert a new column/field and try another formula, like
DSUM?

When I created a cross-tab query, it was able to perform the summary,
but I was not able to do it in a regular query. Is there a formula
that would help me perform the calculation in a non cross-tab query?

Any response will be appreciated.
Roger

 

[Guest]

Roger:

It should work. Post back with the SQL of the query as you've built it. To
do this, in query design view switch to SQL view and copy everything that's
in the window and paste it into your reply.

BTW I notice there was a typo in the SQL I sent you. It had full stop
rather than a comma between Product and Year in the SELECT and GROUP BY
clauses.

Ken Sheridan
Stafford, England

 

[conyers_roger]

Ken, here is the SQL query:

SELECT Table1.PRODUCT, Sum(Table1.[UNITS SOLD]) AS [SumOfUNITS SOLD],
Table1.YEAR
FROM Table1
GROUP BY Table1.PRODUCT, Table1.YEAR, Table1.REGION;

Thanks foe all your help,
Roger

 

[Guest]

Roger:

Remove the Region column from the GROUP BY clause. You want the grouping to
be by Product and Year only or the result will be the same as the original
table. It shouldn't need brackets around the column names, other than [UNITS
SOLD], which includes a space, even though YEAR is the name of a built in
function. The fact that its qualified by the table name should be enough to
identify it as the column name. But you can put the brackets in to play safe
if you wish:

SELECT Table1.[PRODUCT], Table1.[YEAR],
SUM(Table1.[UNITS SOLD]) AS [SumOfUNITS SOLD],
FROM Table1
GROUP BY Table1.[PRODUCT], Table1.[YEAR];

Ken Sheridan
Stafford, England

 

[conyers_roger]

Ken,

It worked! So the Region column was the culprit. Somehow I was under
the impression that since the Region column's Show box was not checked
off, then it didn't play any role in the query.
Thank you very much for your help, patience and tenacity, Ken.

Roger

Roger:

Remove the Region column from the GROUP BY clause. You want the grouping to
be by Product and Year only or the result will be the same as the original
table. It shouldn't need brackets around the column names, other than [UNITS
SOLD], which includes a space, even though YEAR is the name of a built in
function. The fact that its qualified by the table name should be enough to
identify it as the column name. But you can put the brackets in to play safe
if you wish:

SELECT Table1.[PRODUCT], Table1.[YEAR],
SUM(Table1.[UNITS SOLD]) AS [SumOfUNITS SOLD],
FROM Table1
GROUP BY Table1.[PRODUCT], Table1.[YEAR];

Ken Sheridan
Stafford, England

Ken, here is the SQL query:

SELECT Table1.PRODUCT, Sum(Table1.[UNITS SOLD]) AS [SumOfUNITS SOLD],
Table1.YEAR
FROM Table1
GROUP BY Table1.PRODUCT, Table1.YEAR, Table1.REGION;

Thanks foe all your help,
Roger