General Query Help

[slovell]

Hi,
I'm very new to Access DB developing, but I'm learning loads, thanks to this site.

What I'm trying to do, and it's driving me crazy, is the following:
I have a Table that has to fields (Total Daily Weight) and (Total Yearly Weight) and I have a field that calculates the percentage of yearly weight based on the total daily weight.
Now I know you're not supposed to store calculated values, so my question is..How do report on this calculated value?

A little more backround on the database:
The Database tracks waste for companies. They supply me with a total for the year, I weigh the total for the day, and then calculate to % and report on it for them....The problem comes when I go to print a report...I don't get the calculated % of yearly waste..
I keep reading on here that it should be done in a query? Can someone please guide me what I should do? I can put a copy of the DB up if it helps.
Any help would be appreciated.

 

[Brianwarnock]

I don't understand how if in a query you calculate the % that that field will not appear in a report based on the Query.

Brian

 

[odin1701]

Make a query and add your table to it.

Drag each field (daily and yearly weights) to a field in the query.

In a third field in the query you will need to type this in:

Percentage: ([Yearly Wight] / [Daily Weight])

When you run the query you will have that 3rd field called Percentage. You can format it to a certain number of decimals, etc, or do something like:

(([Yearly Wight] / [Daily Weight]) * 100) & "%"

That will give you a result that shows 14% or 10.54% or whatever.

Once you have that query saved, you can design a report based on that query which will have all 3 of those fields.

You can even design a report off of your table and do the formatting and calculations in the report itself, but it may be easier for you to do it in a query.

 

[slovell]

Sorry

I think I might have mis-stated what was happening...Sorry.
The calculation is actually happening on my form in an unbound field.

 

[odin1701]

You need to do the calculation on your report in an unbound field, or base the report off of a query with the calculation in a field.

Maybe it would help if you posted a sample database exhibiting this issue.

 

[slovell]

Success!!!

Thanks odin1701 the first suggestion you made worked perfectly.
I just need to format the field to show less decimal places and I'm all set.
thanks a million for the quick response.

 

[slovell]

Next Step

O.k. I have the query working, and it's calculating perfectly and I'm reporting on it fine. However I noticed that when I run the query it doesn't populate the equation for all the results. Let me explain further.

I have a main form for tracking company information, and a sub form that tracks departments for that company and waste breakdown information. We take measurements for the company in two ways. 1 - a total weight for the day, and 2 - we weigh out categories of waste (within the sub form that tracks the department stuff)..we then calculate what percentage of the daily waste a specific category is. This calculation is done in a query, and works fine for the first department of every company, however when it moves to the next company, all I get is #Error in the field. eg./

Company "X"
Total Daily Weight = 750 Kilograms
Department "Shipping"
Category - Plastic Bottles - 20 Kilograms

I then have a query that calculates what % 20 Kilograms is of 750, then a report based on the query. This works fine; However in my report I look at the next department, with the exact same informaton as above...I see #Error returned in the field.

Does all of that make sense?? Is there a way to make sure my query calculation flows through all of the departments??

Thanks.

 

[ajetrumpet]

Is there a way to make sure my query calculation flows through all of the departments??

I'm pretty sure there is, but I really don't think anyone can get a good idea of what else is involved that might be causing the error unless you post a file to look at....

#ERROR probably tells you the source reference is invalid. It is also a good indicator that a control is not based on a related field of a form table, but rather a one-time calculation.

One last thing too (might not be relevant)....dividing a number by a NULL or 0 will produce the #ERROR message, because mathematical logic says these types of divisions result in an "empty set" (no value). Just another possibility....