Calculations Help

[winedott]

I have read several post that advise not to store calculations in a table. My calculations will not change. I need to pull a calculated field from the previous record. If I am unable to store the calculation from that record I am unable to get the correct count on the current record. My problem is that I am unable to figure out how to do these calculations.

I have figured out how to pull information forward from the previous record.

Now I just need to know how to store a calculation in a table.

 

[John Big Booty]

Here's an article explaining why storing calculated fields is a bad idea.

As to your current problem without seeing your DB and what you are trying to do it will be hard to advise on the best way forward. Are you able to post a cut down version of your DB?

 

[Mike375]

If it is to be on the current form then

Me!TextboxName = Expressio or it could be

Me!TextboxName = Me!Textboxname with the calculation result

What is on the left of the = is the receiver, that is, in your case the name of the table field.

Here is a real life example

Me!xyz = Right(Trim([abc]), Len(Trim([abc])) - InStr(1, [abc], ":"))

In that example the expression is extracting part of the field [abc] and inteserting it into the field [xyz]

The expression could also have been placed in an unbound text box or it could have been used to create a calculated field in a query. In either case the name of the unbound textbox or the new field name in the query would be on the right of the equal.

A couple of reasons for not storing calculated data wouild be

1) Extra fields in a table.

2) The stored data might get out of date. For example, if you had someone's date of birth then a calculation can give their age. But of the the age is stored then obviously it gets out of date.

 

[winedott]

The best way to explain it would be first this DB is used 2 times a day (once for each shift). Each shift enters their end of shift numbers. This is going to be used a a jail setting.

Ok here goes:

1) I need to store the total amount of inmates brought to jail for shifts 1 and 2.

2) I need to store the total amount of inmates booked for shifts 1 and 2.

3) When those numbers are subtracted from each other this is the way that our numbers are checked to see if they match.

For instance:
If 10 people are arrested on shift 1 and 10 on shift 2 I need to store the number 20 in a total field.

Then the same for the number booked. If only 15 of those 20 were booked then 5 is carried forward to the next record. Then it starts over again. If a total of 20 people were arrested on the next day but 25 were booked to catch up from the previous day then the number carried over would be 0.

I am sure I just confused you.

Ok when you look at this DB just remember that I am very new to access as I am sure you will be able to tell. On the count sheet tab that yellow highlighted fields are the fields that I am talking about.

Arrestees Brought In: has 2 fields 1 for each shift. The yellow one I would like to store the total of the 2.

Left to book: This field is the field that I need to bring forward from the previous record. This field should bring the total left over that was not booked.

I also sent the excel part that we are currently using maybe it will help understand a little more.

 

[John Big Booty]

Sorry I don't have Access '07 here, so I'll have to have a look at the DB latter when I'm on a machine with '07.

 

[John Big Booty]

.......

Left to book: This field is the field that I need to bring forward from the previous record. This field should bring the total left over that was not booked.

.......

This figure is being brought forward to the new record. The thing is though that field does not get populated until you have actually put some new data in any one of the other fields, and move to a new field. As until you have entered data into that form, whilst it is on a new record that new record has not been created and saved, so that field does not get populated.