Saving data calculated on a form

[Zaeed]

Ok, i'm currently building a database, with data coming from a series of spreasheets. From those spreadsheets, a number of values are read, and from them a risk rating is determined (low, medium, high) This value is not from the spreadsheets, and is determined when a particular record is displayed.

My issue is that this risk value calculated needs to be used for reporting purposes, and thus needs to be saved somehow.

Can somebody point me in the right direction here please, since i'm drawing a blank on how to do it.

 

[Alansidman]

Data bases are used to store data. Calculated fields should not be stored. If you need to report on calculations, you can do queries and then export the queries or use them as the basis of a report. It will always be accurate and up to date. Do not confuse the use of data bases with spreadsheets.

Alan

 

[Zaeed]

im not confusing the two, i just wasnt sure on how to use the value calculated on the form in a report.

ill try the query method thanks

 

[Mike375]

If you make calculated fields in a query then those fields can be displayed in a Form or Report. Alternatively, you can use unbound textboxes on your Report and have the required formula for the textbox's data source.

 

[Zaeed]

mmm, the problem is that the reports i want to generate would be along the lines of all records with a High Risk, which means that I would have to build a query that first goes through all the records and calculates the Risk, and then pick out the ones with High Risk

 

[Mike375]

mmm, the problem is that the reports i want to generate would be along the lines of all records with a High Risk, which means that I would have to build a query that first goes through all the records and calculates the Risk, and then pick out the ones with High Risk

I think I must be misreading you as this sounds very simple.

The query has a calculated field that has as its result High, Medium and Low and the criteria on that calculated field is set as High or whatever and a Report is based on that query.

 

[Zaeed]

im not sure, i think i've confused myself here. it may be very simple. i'll try and explain it clearly and you can tell me what you think.

Importing a bunch of spreadsheets.

From the data, I have a couple of fields with numbers, these are then sumed, based on that number, a risk rating is calculated. (low medium high)
Thats all well and good.

The database will have a reporting section. Here there will be an option to display all records based on their risk rating, (combo box).

What I gather from the replies is that I should have a query that calculates the risk here? is that what your saying?

its been a long day.. be nice

 

[Mike375]

What I gather from the replies is that I should have a query that calculates the risk here? is that what your saying?

Yes, make a query and add a calculated field (or fields as required) and the calculated field will give you the result for both display in a report (or a form) and at the same time allow records to be selected based on High, Medium etc.

An unbound textbox could be used to display the result of calculations on a report or form but you need the result in a query since you want to select records.

 

[Zaeed]

cool, thanks for that.