Storing Calculated Field in Table?

[andmunn]

Hello,

I've googled this and searched, and from what i gathered, this isn't the "best" idea. But i'll give you my input about what i'm trying to achieve.

I perform a variety of audits for my business. For each audit (we audit about 25 different processes), we test various "samples" (25), and each sample has different criteria (10) we test. I have a form which displays the final "score" for each audit, by counting the number of times the criteria were performed well, and comparing that to the number of times the criteria weren't fullfilled. This then calculates an average "total score" (ie// 75%)

I would like to capture this "total score" in my table for the specific audit (i.e.// tblaudit), so it is stored as field. How do i go about doing this?

I specifally want to do this so that i can run "trending report" to see how the score's have improved / decreased for each audit.

Andrew.

 

[highandwild]

Before you think about how you are going to analyse your data you need to get the data structures right.

Do you have a database that you can post yet?

If you want to collect data from the audits and then do the same again for the same audits then your data structure needs to be able to represent multiple instances of the audits. Will each audit be carried out on a regular basis? Each week, month?

There is no need to store totals and averages as these can be calculated as and when they are required.

 

[PNGBill]

You could have a TblAudit that allowed you to have a record for each Audit performed.
Another table TblProc to hold the ref to each of the 25 processes.
A TblCriteria to hold the 10 or more criteria.
A TblRecords that will allow you to input via forms and subforms the data from each of the 25 processes and related 10 criteria.

Each record in the TblRecord will hold the data collected and queries can then produce charts, spreadsheets etc as you wish.

A typical audit may produce 250 TblRecord records.
All 250 will have the same AuditID and 10 records will have the same ProcID and of course, 25 records will have the same AuditID and CriteriaID but Different ProcID.
If the proper data is stored then any additional fields can be queried as required.

Trust this assists

 

[andmunn]

Hello All,

Thanks for your help. PNGBILL - i do beleive i've structured my DATA exactly in the way that you suggested:

tblTest (audit level)
tblSample (25 tests)
tblAttribute (10 criteria per sample)

The "tests" aren't performed at any specific intervals, rather, i've indivudal a "start" and "end" date for each entry in tbltest.

What i want to do is have a score assigned to each entry in tbltest (right now it's calculated in frmtest in the "scorerevisedrating" box.

I then want to have another form which i woudl be able to create report / graphs for each process to see how the rating has gone for the different tests..

I thought this woudl be easiest if i just store the "scorerevisedrating" in a new field in tbltest.

Thanks,
Andrew.

 

[PNGBill]

Should diff factors influence a score result on different days and you need to keep a record of these results and or the factors that effected the result then this data should be stored either in another table or as an updated field on an existing table but if it is just the result of the data that is stored then experts say, do not store.