calculating other fields based on a formula in a report

M

Michael

I have a report that has a calculated weight (based on a query )that is the
sum of other fields in the query.

How can I store this information or pass it on to another field on the same
report?

Below is the formula and it gives me info for the weight "Esigenza in KG"
for each "OrderDetailID"

=[cases]*[pallets]*[CasesPerRow]*[netweight]/1000


Each "OrderDetailID" may have a different or the same product with a
different or same amount.


I need to find the total weight each product within this query

the report looks something like this

OrderDetailID product cases pallets casesperrow
netweight Esigenza in KG
1 Cherries 88 5
8 125gr 440
2 Cherries 88 5
8 125gr 440
3 Apples 88 6
8 125gr 528
4 Cherries 88 4.5 10
125gr 495



Cherries total 1375kg
Apple total 528 kg

my problem is that I don't know how to tell the query to do this
calculation. How do I tie the esigenza in kg to the product? now it is a
total of a sum for orderdetailID 1 which has nothing to do with cherries.
Also I am confused how I will get the totals of products later.. guess I
did not plan very well. I have been trying to not have redundant data but I
do need to have a list of products sold by weight.
thank you
Michael
 
D

Duane Hookom

You should be able to create a totals query to sum Esigenza by Product. Use
this query as the record source for a subreport. Add the subreport to a
footer section of your main report.
 
M

Michael

Thank you for your reply, but the esigenza only exist on the report not in
the query. My question was how to get the weight into a query or form so I
can manipulate it.
thanks
michael

Duane Hookom said:
You should be able to create a totals query to sum Esigenza by Product.
Use this query as the record source for a subreport. Add the subreport to
a footer section of your main report.

--
Duane Hookom
MS Access MVP
--

Michael said:
I have a report that has a calculated weight (based on a query )that is
the sum of other fields in the query.

How can I store this information or pass it on to another field on the
same report?

Below is the formula and it gives me info for the weight "Esigenza in KG"
for each "OrderDetailID"

=[cases]*[pallets]*[CasesPerRow]*[netweight]/1000


Each "OrderDetailID" may have a different or the same product with a
different or same amount.


I need to find the total weight each product within this query

the report looks something like this

OrderDetailID product cases pallets casesperrow
netweight Esigenza in KG
1 Cherries 88 5 8 125gr
440
2 Cherries 88 5 8 125gr
440
3 Apples 88 6 8 125gr
528
4 Cherries 88 4.5 10 125gr
495



Cherries total 1375kg
Apple total 528 kg

my problem is that I don't know how to tell the query to do this
calculation. How do I tie the esigenza in kg to the product? now it is a
total of a sum for orderdetailID 1 which has nothing to do with cherries.
Also I am confused how I will get the totals of products later.. guess I
did not plan very well. I have been trying to not have redundant data but
I do need to have a list of products sold by weight.
thank you
Michael
Click to expand...
Click to expand...
 
M

Michael

answered my own question with my reply to you. put the calculation in the
query instead of the form.. thanks Duane for making me think lol
michael
Michael said:
Thank you for your reply, but the esigenza only exist on the report not in
the query. My question was how to get the weight into a query or form so I
can manipulate it.
thanks
michael

Duane Hookom said:
You should be able to create a totals query to sum Esigenza by Product.
Use this query as the record source for a subreport. Add the subreport to
a footer section of your main report.

--
Duane Hookom
MS Access MVP
--

Michael said:
I have a report that has a calculated weight (based on a query )that is
the sum of other fields in the query.

How can I store this information or pass it on to another field on the
same report?

Below is the formula and it gives me info for the weight "Esigenza in
KG" for each "OrderDetailID"

=[cases]*[pallets]*[CasesPerRow]*[netweight]/1000


Each "OrderDetailID" may have a different or the same product with a
different or same amount.


I need to find the total weight each product within this query

the report looks something like this

OrderDetailID product cases pallets casesperrow
netweight Esigenza in KG
1 Cherries 88 5 8 125gr 440
2 Cherries 88 5 8 125gr 440
3 Apples 88 6 8 125gr 528
4 Cherries 88 4.5 10 125gr
495



Cherries total 1375kg
Apple total 528 kg

my problem is that I don't know how to tell the query to do this
calculation. How do I tie the esigenza in kg to the product? now it is a
total of a sum for orderdetailID 1 which has nothing to do with
cherries. Also I am confused how I will get the totals of products
later.. guess I did not plan very well. I have been trying to not have
redundant data but I do need to have a list of products sold by weight.
thank you
Michael
Click to expand...
Click to expand...
Click to expand...
 
M

Michael

Guess I did not think hard enough.

I have the following Sql

SELECT [order details query].Esigenz, [order details query].Prodotto
FROM [order details query]
GROUP BY [order details query].Esigenz, [order details query].Prodotto
HAVING ((([order details query].Prodotto)="Latt.Verde")) OR ((([order
details query].Prodotto)="Latt.Verde (L)"));

but gives me a list of the products and their weight.. but I cannot get a
total.. have tried many combination. Can anyone tell me how to either run a
running total or how to get the sum of all the values in esigenz?

What I really need to do is get a total of each product. I can make a query
for each product, but I do not think this is the best way to accomplish what
I need.
thank you
Michael
 
D

Duane Hookom

Shouldn't you have a field in your totals query like:
TotalOf:[cases]*[pallets]*[CasesPerRow]*[netweight]/1000
 
M

Michael

Hi Duane..

"> Shouldn't you have a field in your totals query like:
TotalOf:[cases]*[pallets]*[CasesPerRow]*[netweight]/1000"
Click to expand...

that is the formula for weight "Esigenza in KG" for each "OrderDetailID"

Esigenza in KG"=[cases]*[pallets]*[CasesPerRow]*[netweight]/1000

now that I have this in the query I need to use it to get a total of
esigenza in kg for each product.


cut from older post
I need to find the total weight each product within this query the report
looks something like this

OrderDetailID product cases pallets casesperrow
netweight Esigenza in KG
1 Cherries 88 5
8 125gr 440
2 Cherries 88 5
8 125gr 440
3 Apples 88 6
8 125gr 528
4 Cherries 88 4.5 10
125gr 495
Cherries total
1375 kg
Apple total
528 kG







Duane Hookom said:
Shouldn't you have a field in your totals query like:
TotalOf:[cases]*[pallets]*[CasesPerRow]*[netweight]/1000
Click to expand...
 
M

Michael

I got it by using the below sql, but i have to make one query for each
product. Is there a better way?
Thank you
Michael


SELECT Sum([order details query].Esigenz) AS SumOfEsigenz, [order details
query].Prodotto
FROM [order details query]
GROUP BY [order details query].Prodotto
HAVING ((([order details query].Prodotto)="Rucola Selvatica" Or ([order
details query].Prodotto)="Rucola Selvatica (L)"));







Michael said:
Hi Duane..

"> Shouldn't you have a field in your totals query like:
TotalOf:[cases]*[pallets]*[CasesPerRow]*[netweight]/1000"
Click to expand...

that is the formula for weight "Esigenza in KG" for each "OrderDetailID"

Esigenza in KG"=[cases]*[pallets]*[CasesPerRow]*[netweight]/1000

now that I have this in the query I need to use it to get a total of
esigenza in kg for each product.


cut from older post
I need to find the total weight each product within this query the report
looks something like this

OrderDetailID product cases pallets casesperrow
netweight Esigenza in KG
1 Cherries 88 5 8
125gr 440
2 Cherries 88 5 8
125gr 440
3 Apples 88 6 8
125gr 528
4 Cherries 88 4.5
10 125gr 495
Cherries total 1375 kg
Apple total 528 kG







Duane Hookom said:
Shouldn't you have a field in your totals query like:
TotalOf:[cases]*[pallets]*[CasesPerRow]*[netweight]/1000
Click to expand...
Click to expand...
 
M

Michael

got it .. thanks all

SELECT Sum([order details query].Esigenz) AS SumOfEsigenz, [order details
query].Prodotto
FROM [order details query]
GROUP BY [order details query].Prodotto;

Michael said:
I got it by using the below sql, but i have to make one query for each
product. Is there a better way?
Thank you
Michael


SELECT Sum([order details query].Esigenz) AS SumOfEsigenz, [order details
query].Prodotto
FROM [order details query]
GROUP BY [order details query].Prodotto
HAVING ((([order details query].Prodotto)="Rucola Selvatica" Or ([order
details query].Prodotto)="Rucola Selvatica (L)"));







Michael said:
Hi Duane..

"> Shouldn't you have a field in your totals query like:
TotalOf:[cases]*[pallets]*[CasesPerRow]*[netweight]/1000"
Click to expand...

that is the formula for weight "Esigenza in KG" for each "OrderDetailID"

Esigenza in KG"=[cases]*[pallets]*[CasesPerRow]*[netweight]/1000

now that I have this in the query I need to use it to get a total of
esigenza in kg for each product.


cut from older post
I need to find the total weight each product within this query the report
looks something like this

OrderDetailID product cases pallets casesperrow
netweight Esigenza in KG
1 Cherries 88 5 8 125gr
440
2 Cherries 88 5 8 125gr
440
3 Apples 88 6 8 125gr
528
4 Cherries 88 4.5 10 125gr
495
Cherries total 1375 kg
Apple total 528 kG







Duane Hookom said:
Shouldn't you have a field in your totals query like:
TotalOf:[cases]*[pallets]*[CasesPerRow]*[netweight]/1000
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

Why are you filtering for a single product and then suggesting you need to
make a query for each product?

--
Duane Hookom
MS Access MVP


Michael said:
I got it by using the below sql, but i have to make one query for each
product. Is there a better way?
Thank you
Michael


SELECT Sum([order details query].Esigenz) AS SumOfEsigenz, [order details
query].Prodotto
FROM [order details query]
GROUP BY [order details query].Prodotto
HAVING ((([order details query].Prodotto)="Rucola Selvatica" Or ([order
details query].Prodotto)="Rucola Selvatica (L)"));







Michael said:
Hi Duane..

"> Shouldn't you have a field in your totals query like:
TotalOf:[cases]*[pallets]*[CasesPerRow]*[netweight]/1000"
Click to expand...

that is the formula for weight "Esigenza in KG" for each "OrderDetailID"

Esigenza in KG"=[cases]*[pallets]*[CasesPerRow]*[netweight]/1000

now that I have this in the query I need to use it to get a total of
esigenza in kg for each product.


cut from older post
I need to find the total weight each product within this query the report
looks something like this

OrderDetailID product cases pallets casesperrow
netweight Esigenza in KG
1 Cherries 88 5 8 125gr
440
2 Cherries 88 5 8 125gr
440
3 Apples 88 6 8 125gr
528
4 Cherries 88 4.5 10 125gr
495
Cherries total 1375 kg
Apple total 528 kG







Duane Hookom said:
Shouldn't you have a field in your totals query like:
TotalOf:[cases]*[pallets]*[CasesPerRow]*[netweight]/1000
Click to expand...
Click to expand...
Click to expand...
 
M

Michael

Hi duane.. thought I had it, but still not able to get what I need...

I am using the sql below..I had just posted I guess when you were sending
your last posting, This gets me a list of all the products in the database
by totals.. and this is what I thought I needed.. but I need the totals for
the report that I am making which is based on shipping date as sent from a
form with 2 text boxes with the startdate and enddate... not sure how to add
this to the sql at this point,
thank you
Michael


SELECT Sum([order details query].Esigenz) AS SumOfEsigenz, [order details
query].Prodotto
FROM [order details query]
GROUP BY [order details query].Prodotto;





Duane Hookom said:
Why are you filtering for a single product and then suggesting you need to
make a query for each product?

--
Duane Hookom
MS Access MVP


Michael said:
I got it by using the below sql, but i have to make one query for each
product. Is there a better way?
Thank you
Michael


SELECT Sum([order details query].Esigenz) AS SumOfEsigenz, [order details
query].Prodotto
FROM [order details query]
GROUP BY [order details query].Prodotto
HAVING ((([order details query].Prodotto)="Rucola Selvatica" Or ([order
details query].Prodotto)="Rucola Selvatica (L)"));







Michael said:
Hi Duane..

"> Shouldn't you have a field in your totals query like:
TotalOf:[cases]*[pallets]*[CasesPerRow]*[netweight]/1000"

that is the formula for weight "Esigenza in KG" for each
"OrderDetailID"

Esigenza in KG"=[cases]*[pallets]*[CasesPerRow]*[netweight]/1000

now that I have this in the query I need to use it to get a total of
esigenza in kg for each product.


cut from older post
I need to find the total weight each product within this query the
report looks something like this

OrderDetailID product cases pallets casesperrow
netweight Esigenza in KG
1 Cherries 88 5 8 125gr 440
2 Cherries 88 5 8 125gr 440
3 Apples 88 6 8 125gr 528
4 Cherries 88 4.5 10 125gr
495
Cherries total 1375 kg
Apple total 528 kG







"Duane Hookom" <[email protected]> ha scritto nel messaggio
Shouldn't you have a field in your totals query like:
TotalOf:[cases]*[pallets]*[CasesPerRow]*[netweight]/1000
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

Create a subreport based on this query and add it to your report footer
section.

--
Duane Hookom
MS Access MVP


Michael said:
Hi duane.. thought I had it, but still not able to get what I need...

I am using the sql below..I had just posted I guess when you were sending
your last posting, This gets me a list of all the products in the database
by totals.. and this is what I thought I needed.. but I need the totals
for the report that I am making which is based on shipping date as sent
from a form with 2 text boxes with the startdate and enddate... not sure
how to add this to the sql at this point,
thank you
Michael


SELECT Sum([order details query].Esigenz) AS SumOfEsigenz, [order details
query].Prodotto
FROM [order details query]
GROUP BY [order details query].Prodotto;





Duane Hookom said:
Why are you filtering for a single product and then suggesting you need
to make a query for each product?

--
Duane Hookom
MS Access MVP


Michael said:
I got it by using the below sql, but i have to make one query for each
product. Is there a better way?
Thank you
Michael


SELECT Sum([order details query].Esigenz) AS SumOfEsigenz, [order
details query].Prodotto
FROM [order details query]
GROUP BY [order details query].Prodotto
HAVING ((([order details query].Prodotto)="Rucola Selvatica" Or ([order
details query].Prodotto)="Rucola Selvatica (L)"));







"Michael" <[email protected]> ha scritto nel messaggio
Hi Duane..

"> Shouldn't you have a field in your totals query like:
TotalOf:[cases]*[pallets]*[CasesPerRow]*[netweight]/1000"

that is the formula for weight "Esigenza in KG" for each
"OrderDetailID"

Esigenza in KG"=[cases]*[pallets]*[CasesPerRow]*[netweight]/1000

now that I have this in the query I need to use it to get a total of
esigenza in kg for each product.


cut from older post
I need to find the total weight each product within this query the
report looks something like this

OrderDetailID product cases pallets casesperrow
netweight Esigenza in KG
1 Cherries 88 5 8 125gr 440
2 Cherries 88 5 8 125gr 440
3 Apples 88 6 8 125gr 528
4 Cherries 88 4.5 10 125gr
495
Cherries total 1375 kg
Apple total 528 kG







"Duane Hookom" <[email protected]> ha scritto nel messaggio
Shouldn't you have a field in your totals query like:
TotalOf:[cases]*[pallets]*[CasesPerRow]*[netweight]/1000
Click to expand...
Click to expand...
Click to expand...
 
M

Michael

Hello.. I did this and for some reason, it only shows one product.. this has
been the report from hell.. but thanks for your help
michael


"
 
D

Duane Hookom

Did you allow the subreport to expand? Is the subreport located in the
Report Footer or Page Footer?
 
M

Michael

I have it set to autosize and can grow... the report is in the report footer
thanks
michael
 
M

Michael

Thank you for your help Duane, I got it to work by dragging the subreport
from the list of reports and dropping on the form. don't know why that did
it.. but it works..
thank you again
michael
 

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

problem getting totals in a query 2
Function or formula to use 2
SQL Statment to create a Report 3
page break in report 4
make a multi page report based on a value in qty field 2
Dividing Value from Detail Section by Value from Total Section 1
Hide zeros in totals of crosstab query 1
suppressing a textbox in a report 1

Top