Trying to update calculations in datasheet view

[KatieK]

I have created a table with gross, net, terms, total due, etc. Then I created a query to do calculations which seems to work ok. Although if there is no value, the whole calculation returns a 0. I have put a default 1 value in the record to bring up a value.
How do I populate the table with the finished calculations?
Can I do the arithmetic in the datasheet view or is a query necessary?

Thank you for your help!
Katie

 

[KatieK]

You don't need to store the calculated values. Leave them in the query and use the query as the recordsource for your form or report. In a form, if you change one of the base values, the calculation will automatically refresh to reflect the new value.

Just so I am clear....when I enter information into the Form, it will populate the table? So if I have to go back at some point and change a value, I can do that from the datasheet view? and then rerun the report which will rerun the query which will rerun the calculations?
I have to create a report showing all these fields such as what is owed, what has been paid, etc. Do I have to include all the fields that I want on the report, on the query?

 

[KatieK]

You can use multiple queries and join them if it is easier for you. For example, you can have query1 that includes 10 fields and 5 calculated fields and use that query as the recordsource for your form. If your report requires additional columns and you don't want to recreate the calculations, just select query1 and join it to the table that contains the additional columns. In Access, queries and tables are used interchangably. Almost whenever you can use a table, you can substitute a query. There is generally little or no performance penalty for this flexibility either.

Thank you so much. Now....let's say I have 10 companies so there are 10 tables. I want to use one form and have that info populate the table. I have created a combo box to include different info that spans all companies. When I am done with entry and calculations are completed. How do I create seperate reports for each company based on each companies records. Is this another query?

 

[KatieK]

Start by fixing your table schema. 10 tables for 10 companies! Does it occur to you that this is a maintainence nightmare? What if you had a hundred companies or a million? Merge all the company data into a single table and your problems will disapear.

thank you, I did just that. Merged all data into one table. Created combo boxes and bound boxes so all data can be entered easily. Now...my query...I need to calculate the Net, Film Rental, and Balance Due. I have done that. When I try to create a report, I am not able to use a table and a query or 2 queries. How do I get all the info I need onto a report?

 

[KatieK]

If the recordsets are logically joinable, create a query that joins the queries and tables and use the new query as the recordsource for the report.

If there are multiple 1-many relationships among the recordsets, you will need to create a main report with multiple subreports.

Excellent! Thank you for your guidance. It works!