Multiple option query to calculate a value

I can do forms and designs but I am completely lost when it comes to
calculations. I can work how to place them and understand that result one
can be used in result three.

I have 6 fields which contain the past 6 months values for invoices per month

I need to make a field that shows the average value of these 6

I need to make a field that shows the % of field6 compared to field5

I need to make a field that shows the % of the average result compared to
field6

Unfortunately you first need to fix the table structure. What happens to your
forms, reports, and queries if someone says that you need to track the last
seven months? You have to rebuild them all. Then they say 9 months. Back to
the drawling board again.

Your table has serious normalization problems and just will NOT work
correctly as a relational database. It's set up like a spreadsheet.

I highly suggest getting some relational database training or reading
"Database Design for Mere Mortals" by Hernandez before proceeding any further
on this database.

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"CP" wrote:

I can do forms and designs but I am completely lost when it comes to
calculations. I can work how to place them and understand that result one
can be used in result three.

I have 6 fields which contain the past 6 months values for invoices per month

I need to make a field that shows the average value of these 6

I need to make a field that shows the % of field6 compared to field5

I need to make a field that shows the % of the average result compared to
field6

If for some reason, you cannot fix the data structure you can use a
normalizing union query as the source for getting your data. Post back
if you need further information on how to do this.


John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County



Jerry Whittle wrote:
Unfortunately you first need to fix the table structure. What happens to your
forms, reports, and queries if someone says that you need to track the last
seven months? You have to rebuild them all. Then they say 9 months. Back to
the drawling board again.

Your table has serious normalization problems and just will NOT work
correctly as a relational database. It's set up like a spreadsheet.

I highly suggest getting some relational database training or reading
"Database Design for Mere Mortals" by Hernandez before proceeding any further
on this database.