Dsum grouped by

J

John

I have a table with three fields. Date, production and quantity

Now I want to make a query that is group by Month, Sum of a specific product
(pr month), and sum of total products (pr month) - can I do that in one
query with Dsum ?

Example
Date Product Qry
11-11-2005 10 5
11-12-2005 11 10
17-12-2005 11 10
01-12-2005 10 5
01-12-2005 10 5
01-12-2005 11 10


I wan't to have one query that results in: (If I chooce product 10)
Month SumofProduct SumTotal
11 5 25
12 10 20

Possible? - Thx for helping
John
 
T

Tom Ellison

Dear John:

How about getting the result without DSUM? As long as it's the correct
answer.

Could you try this:

SELECT Month([Date]) AS Month, Product,
SUM(Qry) AS SumofProduct
FROM YourTable
GROUP BY Month([Date]), Product
ORDER BY Month([Date]), Product

In the above query I have added a column to show the Product. I'm not sure
how you want to "choose" a product, and filtering by that depends on how you
choose it.

I cannot tell what the SumTotal column means. The raw data you show appears
to be in DD-MM-YYYY format (there's not month 17, so that must be day). So,
you only have one row showing for Month = 11. I don't see how you got 25
from that. Since I don't know what SumTotal is meant to be, I cannot
formulate a way to get it. Perhaps you would explain or make corrections
and I'll try again.

Change "YourTable" to the actual name of the table (or query) from which
this is to run.

I added sorting to show all results for each month together, then by
Product.

Tom Ellison
 
J

John

Hi Tom

Thanks for your answer - The sumTotal is all qty added for all products for
each month.

When using an example I should make sure that it is right :)

so In the example in month 11, 5 pieces of product 10 was sold, and 5 pieces
of all products was sold in total in month 11. in month 12 10 pieces of 10
was sold, and 40 pieces in total.

It is correct that the format is dd-mm-yyyy.

But then I think I can't use your surgestion - am I right?

/John

The result of the query should offcause be:


Tom Ellison said:
Dear John:

How about getting the result without DSUM? As long as it's the correct
answer.

Could you try this:

SELECT Month([Date]) AS Month, Product,
SUM(Qry) AS SumofProduct
FROM YourTable
GROUP BY Month([Date]), Product
ORDER BY Month([Date]), Product

In the above query I have added a column to show the Product. I'm not
sure how you want to "choose" a product, and filtering by that depends on
how you choose it.

I cannot tell what the SumTotal column means. The raw data you show
appears to be in DD-MM-YYYY format (there's not month 17, so that must be
day). So, you only have one row showing for Month = 11. I don't see how
you got 25 from that. Since I don't know what SumTotal is meant to be, I
cannot formulate a way to get it. Perhaps you would explain or make
corrections and I'll try again.

Change "YourTable" to the actual name of the table (or query) from which
this is to run.

I added sorting to show all results for each month together, then by
Product.

Tom Ellison


John said:
I have a table with three fields. Date, production and quantity

Now I want to make a query that is group by Month, Sum of a specific
product (pr month), and sum of total products (pr month) - can I do that
in one query with Dsum ?

Example
Date Product Qry
11-11-2005 10 5
11-12-2005 11 10
17-12-2005 11 10
01-12-2005 10 5
01-12-2005 10 5
01-12-2005 11 10


I wan't to have one query that results in: (If I chooce product 10)
Month SumofProduct SumTotal
11 5 25
12 10 20

Possible? - Thx for helping
John
Click to expand...
Click to expand...
 
T

Tom Ellison

Dear John:

I believe what you need to add the SumTotal column would make the whole
query:

SELECT Month([Date]) AS Month, Product,
SUM(Qry) AS SumofProduct,
(SELECT SUM(Qry)
FROM YourTable T1
WHERE T1.Momth([Date]) = T.Month([Date]))
AS SumTotal
FROM YourTable T
GROUP BY Month([Date]), Product
ORDER BY Month([Date]), Product

The SumTotal column would be the same for every row within any given month.

If you are going to report this, with the SumTotal only at the end of each
month, then you wouldn't need to do so much of this in the query. A report
will give totals at level breaks without this query work.

Tom Ellison


John said:
Hi Tom

Thanks for your answer - The sumTotal is all qty added for all products
for each month.

When using an example I should make sure that it is right :)

so In the example in month 11, 5 pieces of product 10 was sold, and 5
pieces of all products was sold in total in month 11. in month 12 10
pieces of 10 was sold, and 40 pieces in total.

It is correct that the format is dd-mm-yyyy.

But then I think I can't use your surgestion - am I right?

/John

The result of the query should offcause be:


Tom Ellison said:
Dear John:

How about getting the result without DSUM? As long as it's the correct
answer.

Could you try this:

SELECT Month([Date]) AS Month, Product,
SUM(Qry) AS SumofProduct
FROM YourTable
GROUP BY Month([Date]), Product
ORDER BY Month([Date]), Product

In the above query I have added a column to show the Product. I'm not
sure how you want to "choose" a product, and filtering by that depends on
how you choose it.

I cannot tell what the SumTotal column means. The raw data you show
appears to be in DD-MM-YYYY format (there's not month 17, so that must be
day). So, you only have one row showing for Month = 11. I don't see how
you got 25 from that. Since I don't know what SumTotal is meant to be, I
cannot formulate a way to get it. Perhaps you would explain or make
corrections and I'll try again.

Change "YourTable" to the actual name of the table (or query) from which
this is to run.

I added sorting to show all results for each month together, then by
Product.

Tom Ellison


John said:
I have a table with three fields. Date, production and quantity

Now I want to make a query that is group by Month, Sum of a specific
product (pr month), and sum of total products (pr month) - can I do that
in one query with Dsum ?

Example
Date Product Qry
11-11-2005 10 5
11-12-2005 11 10
17-12-2005 11 10
01-12-2005 10 5
01-12-2005 10 5
01-12-2005 11 10


I wan't to have one query that results in: (If I chooce product 10)
Month SumofProduct SumTotal
11 5 25
12 10 20

Possible? - Thx for helping
John
Click to expand...
Click to expand...
Click to expand...
 
J

John

Thx again

Unfortunately it did't help because it is the same table I wan't to get the
sumtotal from. I tried this:

INSERT INTO Tbl_TEMP ( Aar, Maaned, Oplag, Reklamationer )
SELECT Year([Dato]) AS Aar, Month([dato]) AS Maaned,
Sum(Tbl_OPLAG_AVIS.Antal) AS SumOfAntal, (SELECT Sum(Tbl_Oplag_avis.Antal)
AS SumOfAntal FROM Tbl_Oplag_avis WHERE (((Month([Dato]))=Month([Dato]))))
AS Oplagsrate
FROM Tbl_OPLAG_AVIS
WHERE (((Tbl_OPLAG_AVIS.Dato)>=#3/18/2005# And
(Tbl_OPLAG_AVIS.Dato)<=#2/1/2006#))
GROUP BY Year([Dato]), Month([dato])
ORDER BY Year([Dato]), Month([dato])

But this doesn't work (Here sumtotal and sum(Antal) should return the same
value.

/John

Tom Ellison said:
Dear John:

I believe what you need to add the SumTotal column would make the whole
query:

SELECT Month([Date]) AS Month, Product,
SUM(Qry) AS SumofProduct,
(SELECT SUM(Qry)
FROM YourTable T1
WHERE T1.Momth([Date]) = T.Month([Date]))
AS SumTotal
FROM YourTable T
GROUP BY Month([Date]), Product
ORDER BY Month([Date]), Product

The SumTotal column would be the same for every row within any given
month.

If you are going to report this, with the SumTotal only at the end of each
month, then you wouldn't need to do so much of this in the query. A
report will give totals at level breaks without this query work.

Tom Ellison


John said:
Hi Tom

Thanks for your answer - The sumTotal is all qty added for all products
for each month.

When using an example I should make sure that it is right :)

so In the example in month 11, 5 pieces of product 10 was sold, and 5
pieces of all products was sold in total in month 11. in month 12 10
pieces of 10 was sold, and 40 pieces in total.

It is correct that the format is dd-mm-yyyy.

But then I think I can't use your surgestion - am I right?

/John

The result of the query should offcause be:


Tom Ellison said:
Dear John:

How about getting the result without DSUM? As long as it's the correct
answer.

Could you try this:

SELECT Month([Date]) AS Month, Product,
SUM(Qry) AS SumofProduct
FROM YourTable
GROUP BY Month([Date]), Product
ORDER BY Month([Date]), Product

In the above query I have added a column to show the Product. I'm not
sure how you want to "choose" a product, and filtering by that depends
on how you choose it.

I cannot tell what the SumTotal column means. The raw data you show
appears to be in DD-MM-YYYY format (there's not month 17, so that must
be day). So, you only have one row showing for Month = 11. I don't see
how you got 25 from that. Since I don't know what SumTotal is meant to
be, I cannot formulate a way to get it. Perhaps you would explain or
make corrections and I'll try again.

Change "YourTable" to the actual name of the table (or query) from which
this is to run.

I added sorting to show all results for each month together, then by
Product.

Tom Ellison


I have a table with three fields. Date, production and quantity

Now I want to make a query that is group by Month, Sum of a specific
product (pr month), and sum of total products (pr month) - can I do
that in one query with Dsum ?

Example
Date Product Qry
11-11-2005 10 5
11-12-2005 11 10
17-12-2005 11 10
01-12-2005 10 5
01-12-2005 10 5
01-12-2005 11 10


I wan't to have one query that results in: (If I chooce product 10)
Month SumofProduct SumTotal
11 5 25
12 10 20

Possible? - Thx for helping
John
Click to expand...
Click to expand...
Click to expand...
 
T

Tom Ellison

Dear John:

The way you have modified the query I wrote is not going to work. You must
retain the aliases. Working from what you wrote, perhaps this:

SELECT Year([Dato]), Month([Dato]) AS Maaned,
SUM(Antal) AS SumofAntal,
(SELECT SUM(T1.Antal)
FROM Tbl_Oplag_avis T1
WHERE T1.Month([Dato]) = T.Month([Dato]))
AS Oplagsrate
FROM Tbl_Oplag_avis T
WHERE Dato Between #3/18/2005# And #2/1/2006#
GROUP BY Year([Dato]), Month([Dato])
ORDER BY Year([Dato]), Month([Dato])

I hope I've made the spellings of tables and columns correctly.

I don't know what happened to "Product" but you can reinsert it if needed.

Tom Ellison


John said:
Thx again

Unfortunately it did't help because it is the same table I wan't to get
the sumtotal from. I tried this:

INSERT INTO Tbl_TEMP ( Aar, Maaned, Oplag, Reklamationer )
SELECT Year([Dato]) AS Aar, Month([dato]) AS Maaned,
Sum(Tbl_OPLAG_AVIS.Antal) AS SumOfAntal, (SELECT Sum(Tbl_Oplag_avis.Antal)
AS SumOfAntal FROM Tbl_Oplag_avis WHERE
(((Month([Dato]))=Month([Dato])))) AS Oplagsrate
FROM Tbl_OPLAG_AVIS
WHERE (((Tbl_OPLAG_AVIS.Dato)>=#3/18/2005# And
(Tbl_OPLAG_AVIS.Dato)<=#2/1/2006#))
GROUP BY Year([Dato]), Month([dato])
ORDER BY Year([Dato]), Month([dato])

But this doesn't work (Here sumtotal and sum(Antal) should return the same
value.

/John

Tom Ellison said:
Dear John:

I believe what you need to add the SumTotal column would make the whole
query:

SELECT Month([Date]) AS Month, Product,
SUM(Qry) AS SumofProduct,
(SELECT SUM(Qry)
FROM YourTable T1
WHERE T1.Momth([Date]) = T.Month([Date]))
AS SumTotal
FROM YourTable T
GROUP BY Month([Date]), Product
ORDER BY Month([Date]), Product

The SumTotal column would be the same for every row within any given
month.

If you are going to report this, with the SumTotal only at the end of
each month, then you wouldn't need to do so much of this in the query. A
report will give totals at level breaks without this query work.

Tom Ellison


John said:
Hi Tom

Thanks for your answer - The sumTotal is all qty added for all products
for each month.

When using an example I should make sure that it is right :)

so In the example in month 11, 5 pieces of product 10 was sold, and 5
pieces of all products was sold in total in month 11. in month 12 10
pieces of 10 was sold, and 40 pieces in total.

It is correct that the format is dd-mm-yyyy.

But then I think I can't use your surgestion - am I right?

/John

The result of the query should offcause be:


"Tom Ellison" <[email protected]> skrev i en meddelelse
Dear John:

How about getting the result without DSUM? As long as it's the correct
answer.

Could you try this:

SELECT Month([Date]) AS Month, Product,
SUM(Qry) AS SumofProduct
FROM YourTable
GROUP BY Month([Date]), Product
ORDER BY Month([Date]), Product

In the above query I have added a column to show the Product. I'm not
sure how you want to "choose" a product, and filtering by that depends
on how you choose it.

I cannot tell what the SumTotal column means. The raw data you show
appears to be in DD-MM-YYYY format (there's not month 17, so that must
be day). So, you only have one row showing for Month = 11. I don't
see how you got 25 from that. Since I don't know what SumTotal is
meant to be, I cannot formulate a way to get it. Perhaps you would
explain or make corrections and I'll try again.

Change "YourTable" to the actual name of the table (or query) from
which this is to run.

I added sorting to show all results for each month together, then by
Product.

Tom Ellison


I have a table with three fields. Date, production and quantity

Now I want to make a query that is group by Month, Sum of a specific
product (pr month), and sum of total products (pr month) - can I do
that in one query with Dsum ?

Example
Date Product Qry
11-11-2005 10 5
11-12-2005 11 10
17-12-2005 11 10
01-12-2005 10 5
01-12-2005 10 5
01-12-2005 11 10


I wan't to have one query that results in: (If I chooce product 10)
Month SumofProduct SumTotal
11 5 25
12 10 20

Possible? - Thx for helping
John
Click to expand...
Click to expand...
Click to expand...
 
J

John

Hi Tom

I really appriciate your effort.

I tried to paste in you code, but it says the undefined function T1.Month ?!

Thx
John
Tom Ellison said:
Dear John:

The way you have modified the query I wrote is not going to work. You
must retain the aliases. Working from what you wrote, perhaps this:

SELECT Year([Dato]), Month([Dato]) AS Maaned,
SUM(Antal) AS SumofAntal,
(SELECT SUM(T1.Antal)
FROM Tbl_Oplag_avis T1
WHERE T1.Month([Dato]) = T.Month([Dato]))
AS Oplagsrate
FROM Tbl_Oplag_avis T
WHERE Dato Between #3/18/2005# And #2/1/2006#
GROUP BY Year([Dato]), Month([Dato])
ORDER BY Year([Dato]), Month([Dato])

I hope I've made the spellings of tables and columns correctly.

I don't know what happened to "Product" but you can reinsert it if needed.

Tom Ellison


John said:
Thx again

Unfortunately it did't help because it is the same table I wan't to get
the sumtotal from. I tried this:

INSERT INTO Tbl_TEMP ( Aar, Maaned, Oplag, Reklamationer )
SELECT Year([Dato]) AS Aar, Month([dato]) AS Maaned,
Sum(Tbl_OPLAG_AVIS.Antal) AS SumOfAntal, (SELECT
Sum(Tbl_Oplag_avis.Antal) AS SumOfAntal FROM Tbl_Oplag_avis WHERE
(((Month([Dato]))=Month([Dato])))) AS Oplagsrate
FROM Tbl_OPLAG_AVIS
WHERE (((Tbl_OPLAG_AVIS.Dato)>=#3/18/2005# And
(Tbl_OPLAG_AVIS.Dato)<=#2/1/2006#))
GROUP BY Year([Dato]), Month([dato])
ORDER BY Year([Dato]), Month([dato])

But this doesn't work (Here sumtotal and sum(Antal) should return the
same value.

/John

Tom Ellison said:
Dear John:

I believe what you need to add the SumTotal column would make the whole
query:

SELECT Month([Date]) AS Month, Product,
SUM(Qry) AS SumofProduct,
(SELECT SUM(Qry)
FROM YourTable T1
WHERE T1.Momth([Date]) = T.Month([Date]))
AS SumTotal
FROM YourTable T
GROUP BY Month([Date]), Product
ORDER BY Month([Date]), Product

The SumTotal column would be the same for every row within any given
month.

If you are going to report this, with the SumTotal only at the end of
each month, then you wouldn't need to do so much of this in the query.
A report will give totals at level breaks without this query work.

Tom Ellison


Hi Tom

Thanks for your answer - The sumTotal is all qty added for all products
for each month.

When using an example I should make sure that it is right :)

so In the example in month 11, 5 pieces of product 10 was sold, and 5
pieces of all products was sold in total in month 11. in month 12 10
pieces of 10 was sold, and 40 pieces in total.

It is correct that the format is dd-mm-yyyy.

But then I think I can't use your surgestion - am I right?

/John

The result of the query should offcause be:


"Tom Ellison" <[email protected]> skrev i en meddelelse
Dear John:

How about getting the result without DSUM? As long as it's the
correct answer.

Could you try this:

SELECT Month([Date]) AS Month, Product,
SUM(Qry) AS SumofProduct
FROM YourTable
GROUP BY Month([Date]), Product
ORDER BY Month([Date]), Product

In the above query I have added a column to show the Product. I'm not
sure how you want to "choose" a product, and filtering by that depends
on how you choose it.

I cannot tell what the SumTotal column means. The raw data you show
appears to be in DD-MM-YYYY format (there's not month 17, so that must
be day). So, you only have one row showing for Month = 11. I don't
see how you got 25 from that. Since I don't know what SumTotal is
meant to be, I cannot formulate a way to get it. Perhaps you would
explain or make corrections and I'll try again.

Change "YourTable" to the actual name of the table (or query) from
which this is to run.

I added sorting to show all results for each month together, then by
Product.

Tom Ellison


I have a table with three fields. Date, production and quantity

Now I want to make a query that is group by Month, Sum of a specific
product (pr month), and sum of total products (pr month) - can I do
that in one query with Dsum ?

Example
Date Product Qry
11-11-2005 10 5
11-12-2005 11 10
17-12-2005 11 10
01-12-2005 10 5
01-12-2005 10 5
01-12-2005 11 10


I wan't to have one query that results in: (If I chooce product 10)
Month SumofProduct SumTotal
11 5 25
12 10 20

Possible? - Thx for helping
John
Click to expand...
Click to expand...
Click to expand...
 
T

Tom Ellison

Dear John:

Hopefully, this wasn't the best I can code. The alias belongs with the
column name, not with the function. Please allow me to try again:

SELECT Year([Dato]), Month([Dato]) AS Maaned,
SUM(Antal) AS SumofAntal,
(SELECT SUM(T1.Antal)
FROM Tbl_Oplag_avis T1
WHERE Month([T1.Dato]) = Month([T.Dato]))
AS Oplagsrate
FROM Tbl_Oplag_avis T
WHERE Dato Between #3/18/2005# And #2/1/2006#
GROUP BY Year([Dato]), Month([Dato])
ORDER BY Year([Dato]), Month([Dato])

I believe I have fixed that error. Don't know about any others. It's much
easier (and less embarassing) to code where you can test it before others
see it!

Tom Ellison


John said:
Hi Tom

I really appriciate your effort.

I tried to paste in you code, but it says the undefined function T1.Month
?!

Thx
John
Tom Ellison said:
Dear John:

The way you have modified the query I wrote is not going to work. You
must retain the aliases. Working from what you wrote, perhaps this:

SELECT Year([Dato]), Month([Dato]) AS Maaned,
SUM(Antal) AS SumofAntal,
(SELECT SUM(T1.Antal)
FROM Tbl_Oplag_avis T1
WHERE T1.Month([Dato]) = T.Month([Dato]))
AS Oplagsrate
FROM Tbl_Oplag_avis T
WHERE Dato Between #3/18/2005# And #2/1/2006#
GROUP BY Year([Dato]), Month([Dato])
ORDER BY Year([Dato]), Month([Dato])

I hope I've made the spellings of tables and columns correctly.

I don't know what happened to "Product" but you can reinsert it if
needed.

Tom Ellison


John said:
Thx again

Unfortunately it did't help because it is the same table I wan't to get
the sumtotal from. I tried this:

INSERT INTO Tbl_TEMP ( Aar, Maaned, Oplag, Reklamationer )
SELECT Year([Dato]) AS Aar, Month([dato]) AS Maaned,
Sum(Tbl_OPLAG_AVIS.Antal) AS SumOfAntal, (SELECT
Sum(Tbl_Oplag_avis.Antal) AS SumOfAntal FROM Tbl_Oplag_avis WHERE
(((Month([Dato]))=Month([Dato])))) AS Oplagsrate
FROM Tbl_OPLAG_AVIS
WHERE (((Tbl_OPLAG_AVIS.Dato)>=#3/18/2005# And
(Tbl_OPLAG_AVIS.Dato)<=#2/1/2006#))
GROUP BY Year([Dato]), Month([dato])
ORDER BY Year([Dato]), Month([dato])

But this doesn't work (Here sumtotal and sum(Antal) should return the
same value.

/John

"Tom Ellison" <[email protected]> skrev i en meddelelse
Dear John:

I believe what you need to add the SumTotal column would make the whole
query:

SELECT Month([Date]) AS Month, Product,
SUM(Qry) AS SumofProduct,
(SELECT SUM(Qry)
FROM YourTable T1
WHERE T1.Momth([Date]) = T.Month([Date]))
AS SumTotal
FROM YourTable T
GROUP BY Month([Date]), Product
ORDER BY Month([Date]), Product

The SumTotal column would be the same for every row within any given
month.

If you are going to report this, with the SumTotal only at the end of
each month, then you wouldn't need to do so much of this in the query.
A report will give totals at level breaks without this query work.

Tom Ellison


Hi Tom

Thanks for your answer - The sumTotal is all qty added for all
products for each month.

When using an example I should make sure that it is right :)

so In the example in month 11, 5 pieces of product 10 was sold, and 5
pieces of all products was sold in total in month 11. in month 12 10
pieces of 10 was sold, and 40 pieces in total.

It is correct that the format is dd-mm-yyyy.

But then I think I can't use your surgestion - am I right?

/John

The result of the query should offcause be:


"Tom Ellison" <[email protected]> skrev i en meddelelse
Dear John:

How about getting the result without DSUM? As long as it's the
correct answer.

Could you try this:

SELECT Month([Date]) AS Month, Product,
SUM(Qry) AS SumofProduct
FROM YourTable
GROUP BY Month([Date]), Product
ORDER BY Month([Date]), Product

In the above query I have added a column to show the Product. I'm
not sure how you want to "choose" a product, and filtering by that
depends on how you choose it.

I cannot tell what the SumTotal column means. The raw data you show
appears to be in DD-MM-YYYY format (there's not month 17, so that
must be day). So, you only have one row showing for Month = 11. I
don't see how you got 25 from that. Since I don't know what SumTotal
is meant to be, I cannot formulate a way to get it. Perhaps you
would explain or make corrections and I'll try again.

Change "YourTable" to the actual name of the table (or query) from
which this is to run.

I added sorting to show all results for each month together, then by
Product.

Tom Ellison


I have a table with three fields. Date, production and quantity

Now I want to make a query that is group by Month, Sum of a specific
product (pr month), and sum of total products (pr month) - can I do
that in one query with Dsum ?

Example
Date Product Qry
11-11-2005 10 5
11-12-2005 11 10
17-12-2005 11 10
01-12-2005 10 5
01-12-2005 10 5
01-12-2005 11 10


I wan't to have one query that results in: (If I chooce product 10)
Month SumofProduct SumTotal
11 5 25
12 10 20

Possible? - Thx for helping
John
Click to expand...
Click to expand...
Click to expand...
 
J

John

Yeeees - it works

Thank you so much Tom!

Tom Ellison said:
Dear John:

Hopefully, this wasn't the best I can code. The alias belongs with the
column name, not with the function. Please allow me to try again:

SELECT Year([Dato]), Month([Dato]) AS Maaned,
SUM(Antal) AS SumofAntal,
(SELECT SUM(T1.Antal)
FROM Tbl_Oplag_avis T1
WHERE Month([T1.Dato]) = Month([T.Dato]))
AS Oplagsrate
FROM Tbl_Oplag_avis T
WHERE Dato Between #3/18/2005# And #2/1/2006#
GROUP BY Year([Dato]), Month([Dato])
ORDER BY Year([Dato]), Month([Dato])

I believe I have fixed that error. Don't know about any others. It's
much easier (and less embarassing) to code where you can test it before
others see it!

Tom Ellison


John said:
Hi Tom

I really appriciate your effort.

I tried to paste in you code, but it says the undefined function T1.Month
?!

Thx
John
Tom Ellison said:
Dear John:

The way you have modified the query I wrote is not going to work. You
must retain the aliases. Working from what you wrote, perhaps this:

SELECT Year([Dato]), Month([Dato]) AS Maaned,
SUM(Antal) AS SumofAntal,
(SELECT SUM(T1.Antal)
FROM Tbl_Oplag_avis T1
WHERE T1.Month([Dato]) = T.Month([Dato]))
AS Oplagsrate
FROM Tbl_Oplag_avis T
WHERE Dato Between #3/18/2005# And #2/1/2006#
GROUP BY Year([Dato]), Month([Dato])
ORDER BY Year([Dato]), Month([Dato])

I hope I've made the spellings of tables and columns correctly.

I don't know what happened to "Product" but you can reinsert it if
needed.

Tom Ellison


Thx again

Unfortunately it did't help because it is the same table I wan't to get
the sumtotal from. I tried this:

INSERT INTO Tbl_TEMP ( Aar, Maaned, Oplag, Reklamationer )
SELECT Year([Dato]) AS Aar, Month([dato]) AS Maaned,
Sum(Tbl_OPLAG_AVIS.Antal) AS SumOfAntal, (SELECT
Sum(Tbl_Oplag_avis.Antal) AS SumOfAntal FROM Tbl_Oplag_avis WHERE
(((Month([Dato]))=Month([Dato])))) AS Oplagsrate
FROM Tbl_OPLAG_AVIS
WHERE (((Tbl_OPLAG_AVIS.Dato)>=#3/18/2005# And
(Tbl_OPLAG_AVIS.Dato)<=#2/1/2006#))
GROUP BY Year([Dato]), Month([dato])
ORDER BY Year([Dato]), Month([dato])

But this doesn't work (Here sumtotal and sum(Antal) should return the
same value.

/John

"Tom Ellison" <[email protected]> skrev i en meddelelse
Dear John:

I believe what you need to add the SumTotal column would make the
whole query:

SELECT Month([Date]) AS Month, Product,
SUM(Qry) AS SumofProduct,
(SELECT SUM(Qry)
FROM YourTable T1
WHERE T1.Momth([Date]) = T.Month([Date]))
AS SumTotal
FROM YourTable T
GROUP BY Month([Date]), Product
ORDER BY Month([Date]), Product

The SumTotal column would be the same for every row within any given
month.

If you are going to report this, with the SumTotal only at the end of
each month, then you wouldn't need to do so much of this in the query.
A report will give totals at level breaks without this query work.

Tom Ellison


Hi Tom

Thanks for your answer - The sumTotal is all qty added for all
products for each month.

When using an example I should make sure that it is right :)

so In the example in month 11, 5 pieces of product 10 was sold, and 5
pieces of all products was sold in total in month 11. in month 12 10
pieces of 10 was sold, and 40 pieces in total.

It is correct that the format is dd-mm-yyyy.

But then I think I can't use your surgestion - am I right?

/John

The result of the query should offcause be:


"Tom Ellison" <[email protected]> skrev i en meddelelse
Dear John:

How about getting the result without DSUM? As long as it's the
correct answer.

Could you try this:

SELECT Month([Date]) AS Month, Product,
SUM(Qry) AS SumofProduct
FROM YourTable
GROUP BY Month([Date]), Product
ORDER BY Month([Date]), Product

In the above query I have added a column to show the Product. I'm
not sure how you want to "choose" a product, and filtering by that
depends on how you choose it.

I cannot tell what the SumTotal column means. The raw data you show
appears to be in DD-MM-YYYY format (there's not month 17, so that
must be day). So, you only have one row showing for Month = 11. I
don't see how you got 25 from that. Since I don't know what
SumTotal is meant to be, I cannot formulate a way to get it.
Perhaps you would explain or make corrections and I'll try again.

Change "YourTable" to the actual name of the table (or query) from
which this is to run.

I added sorting to show all results for each month together, then by
Product.

Tom Ellison


I have a table with three fields. Date, production and quantity

Now I want to make a query that is group by Month, Sum of a
specific product (pr month), and sum of total products (pr month) -
can I do that in one query with Dsum ?

Example
Date Product Qry
11-11-2005 10 5
11-12-2005 11 10
17-12-2005 11 10
01-12-2005 10 5
01-12-2005 10 5
01-12-2005 11 10


I wan't to have one query that results in: (If I chooce product 10)
Month SumofProduct SumTotal
11 5 25
12 10 20

Possible? - Thx for helping
John
Click to expand...
Click to expand...
Click to expand...
 
T

Tom Ellison

Dear John:

Thanks for having enough patience for me to give it a second try and get it
right.

Tom Ellison


John said:
Yeeees - it works

Thank you so much Tom!

Tom Ellison said:
Dear John:

Hopefully, this wasn't the best I can code. The alias belongs with the
column name, not with the function. Please allow me to try again:

SELECT Year([Dato]), Month([Dato]) AS Maaned,
SUM(Antal) AS SumofAntal,
(SELECT SUM(T1.Antal)
FROM Tbl_Oplag_avis T1
WHERE Month([T1.Dato]) = Month([T.Dato]))
AS Oplagsrate
FROM Tbl_Oplag_avis T
WHERE Dato Between #3/18/2005# And #2/1/2006#
GROUP BY Year([Dato]), Month([Dato])
ORDER BY Year([Dato]), Month([Dato])

I believe I have fixed that error. Don't know about any others. It's
much easier (and less embarassing) to code where you can test it before
others see it!

Tom Ellison


John said:
Hi Tom

I really appriciate your effort.

I tried to paste in you code, but it says the undefined function
T1.Month ?!

Thx
John
"Tom Ellison" <[email protected]> skrev i en meddelelse
Dear John:

The way you have modified the query I wrote is not going to work. You
must retain the aliases. Working from what you wrote, perhaps this:

SELECT Year([Dato]), Month([Dato]) AS Maaned,
SUM(Antal) AS SumofAntal,
(SELECT SUM(T1.Antal)
FROM Tbl_Oplag_avis T1
WHERE T1.Month([Dato]) = T.Month([Dato]))
AS Oplagsrate
FROM Tbl_Oplag_avis T
WHERE Dato Between #3/18/2005# And #2/1/2006#
GROUP BY Year([Dato]), Month([Dato])
ORDER BY Year([Dato]), Month([Dato])

I hope I've made the spellings of tables and columns correctly.

I don't know what happened to "Product" but you can reinsert it if
needed.

Tom Ellison


Thx again

Unfortunately it did't help because it is the same table I wan't to
get the sumtotal from. I tried this:

INSERT INTO Tbl_TEMP ( Aar, Maaned, Oplag, Reklamationer )
SELECT Year([Dato]) AS Aar, Month([dato]) AS Maaned,
Sum(Tbl_OPLAG_AVIS.Antal) AS SumOfAntal, (SELECT
Sum(Tbl_Oplag_avis.Antal) AS SumOfAntal FROM Tbl_Oplag_avis WHERE
(((Month([Dato]))=Month([Dato])))) AS Oplagsrate
FROM Tbl_OPLAG_AVIS
WHERE (((Tbl_OPLAG_AVIS.Dato)>=#3/18/2005# And
(Tbl_OPLAG_AVIS.Dato)<=#2/1/2006#))
GROUP BY Year([Dato]), Month([dato])
ORDER BY Year([Dato]), Month([dato])

But this doesn't work (Here sumtotal and sum(Antal) should return the
same value.

/John

"Tom Ellison" <[email protected]> skrev i en meddelelse
Dear John:

I believe what you need to add the SumTotal column would make the
whole query:

SELECT Month([Date]) AS Month, Product,
SUM(Qry) AS SumofProduct,
(SELECT SUM(Qry)
FROM YourTable T1
WHERE T1.Momth([Date]) = T.Month([Date]))
AS SumTotal
FROM YourTable T
GROUP BY Month([Date]), Product
ORDER BY Month([Date]), Product

The SumTotal column would be the same for every row within any given
month.

If you are going to report this, with the SumTotal only at the end of
each month, then you wouldn't need to do so much of this in the
query. A report will give totals at level breaks without this query
work.

Tom Ellison


Hi Tom

Thanks for your answer - The sumTotal is all qty added for all
products for each month.

When using an example I should make sure that it is right :)

so In the example in month 11, 5 pieces of product 10 was sold, and
5 pieces of all products was sold in total in month 11. in month 12
10 pieces of 10 was sold, and 40 pieces in total.

It is correct that the format is dd-mm-yyyy.

But then I think I can't use your surgestion - am I right?

/John

The result of the query should offcause be:


"Tom Ellison" <[email protected]> skrev i en meddelelse
Dear John:

How about getting the result without DSUM? As long as it's the
correct answer.

Could you try this:

SELECT Month([Date]) AS Month, Product,
SUM(Qry) AS SumofProduct
FROM YourTable
GROUP BY Month([Date]), Product
ORDER BY Month([Date]), Product

In the above query I have added a column to show the Product. I'm
not sure how you want to "choose" a product, and filtering by that
depends on how you choose it.

I cannot tell what the SumTotal column means. The raw data you
show appears to be in DD-MM-YYYY format (there's not month 17, so
that must be day). So, you only have one row showing for Month =
11. I don't see how you got 25 from that. Since I don't know what
SumTotal is meant to be, I cannot formulate a way to get it.
Perhaps you would explain or make corrections and I'll try again.

Change "YourTable" to the actual name of the table (or query) from
which this is to run.

I added sorting to show all results for each month together, then
by Product.

Tom Ellison


I have a table with three fields. Date, production and quantity

Now I want to make a query that is group by Month, Sum of a
specific product (pr month), and sum of total products (pr
month) - can I do that in one query with Dsum ?

Example
Date Product Qry
11-11-2005 10 5
11-12-2005 11 10
17-12-2005 11 10
01-12-2005 10 5
01-12-2005 10 5
01-12-2005 11 10


I wan't to have one query that results in: (If I chooce product
10)
Month SumofProduct SumTotal
11 5 25
12 10 20

Possible? - Thx for helping
John
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Linking Child Timelines to Multiple Parents 0
IF then SUM 5
Get the last price 3
QUERY 1
How to build subquery on aggregate query 0
time_report_date-wise_employee-wise 6
Tranform Pivot Query? 3
Numbering....??? 7

Top