Need text box to display value from another 1

[PaveFE]

Hi,

I have a table that has several fields that a number will be input into on the form (based on the table). However I need the last field to calculate a sum of the total input.
For Example.
Field1 = 1
Field2 = 3
Field3 = 1
Field4 = 5 (needs to calculate total of Fields 1 thru 3)

or I can have fields 1 thru 3 on a form, then insert a text box on the form and make the control source do the calculation, however, I still need this total to be in a field in the table. Can I make another field in the table display the value calculated in the box on the form?

However it can be done, I need to do this.

Thanks,
VAD

 

[VicRauch]

in the AfterUpdate [red]event[/red] for all three fields, do this calculation:

Field4 = Nz(Field1) + Nz(Field2) + Nz(Field3)

 

[Knicks]

Vic's answer should satisfy,

Generally it is not considered good form to have fields in tables be a result of a calculation. You can always get totals at the query, form, and report level from your numeric data.............

 

[ssatech]

How's it going PaveFE? Did you not post the same question already in thread702-1224800. As already said not good to store calculated values in a table.

"however, I still need this total to be in a field in the table. Can I make another field in the table display the value calculated in the box on the form?"

But if you insist, try adding another field on your form that is bound to your table's field. Then in the After Update event of your field (Field3)

Try the following -

Me.NameOfTableField.Value=Me.Field3.Value

or by code:

Dim db As DAO.Database
Dim rst As Recordset

DoCmd.RunCommand acCmdSaveRecord
Set rst = CurrentDb.OpenRecordset("SELECT * from TableName where NameID = " & NameID)
rst.Edit
rst!NameOfTableField = Me.Field3

rst.UPDATE
rst.Close
Set rst = Nothing

 

[VicRauch]

Something else that should be said: If you really "need" to store this calculated field, think about running an update query often to add these three fields together and updating Field4 fresh. I have never understood how this type of thing can happen, but it does, and this update query IS needed.

The best way to handle this, is whenever you need to see the total of the three fields, just do it in a select query.

 

[PaveFE]

Okay, after finally convinced my "handlers" that putting the calculated value in the table was a bad idea, they have agreed. But now I need to figure out how else to do it.
Here's the scenario:
I have a table that will be for aircrew to determine a risk factor for the flight. There are numerous factors broken up by 5 different categories. Each category needs a subtotal, then I need a total of all categories. The form is built from the table (obviously), but I will want this form to print so they can sign it. The Flight Authorization number is the primary key for each record.
How do I set up a query for the calculations?

Thanks to everyone for the help.

Vince

 

[VicRauch]

I am assuming all the data you are dealing with is in this one table. (Study up on normalizing tables if all the data is in one table.)
Let's assume for your 5 categories, and various fields per category, we have this naming convention:
Cat01FldA; Cat01FldB; Cat01FldC
Cat02FldA; Cat02FldB; Cat02FldC; Cat02FldD
etc.
So, in your query, you would define 5 category subtotals and their summation fields:
SumCat01: Cat01FldA + Cat01FldB +
Cat01FldC
SumCat02: Cat02FldA + Cat02FldB +
Cat02FldC + Cat02FldD
These subtotal calculations would be the columns within a query. Of course, you will need additional columns to identify which flight, etc these subtotals refer too.
After defining all 5 subtotal calculations, then define your total calculation as such:
TotalAllCats: SumCat01 + SumCat02 + SumCat03 + SumCat04 + SumCat05

Generally speaking, I have found printing a form just is not usually acceptable. I have always designed a report to look like the form, and it makes a much better document to sign.

HTH
Vic

 

[PaveFE]

Currently I have all the data required in one table (ORM Worksheet). The table just has all the fields required for the entire form, but the fields are not named as different categories as I don't think I really need that (I could be wrong). The form I built is done using tab control to split it up(5 tabs, 1 for each category). I guess I could have made 5 tables and then total up each in the query, but since all the data pertains to one subject (Risk Assessment), I figured I would put it all in one table.

The categories are:
Man
Machine
Media
Management
Mission

Each needs a subtotal and then a total of all, which as long as it shows on the report (I use that method too), I'm good. I just don't know how to get those to total up.
I know how to do the calculations (=[fld1]+[fld2]+[fld3], etc. but where do I put it (I assume in criteria section of the query), but what goes in the field section? Or as stated elsewhere, can this be done on the report? I would just need to know the step by step per say. I'm very familiar with Access, but just not to the point that I know all the procedures.
Hopefully this makes sense, it is hard to describe.
I would send a picture, but I don't see how that can be done on here.

Thanks,

Vince

 

[VicRauch]

Vince,
When I used the fields Cat01FldA, etc, the Cat01 was just representing your first category, which seems to be Man. So, I don't know what your field names are, but the first field in your Man category I represented as CatFldA, second field would be CatFldB, and so on.
The syntax I gave you is what you need to use within the "Field:" line (row) of the query builder grid. I would send you a picture, but I can't get to my web site right now. Remember, the names are ONLY examples. I don't know your real names, you will have to substitute the real names and finish all 5 categories.

HTH,
Vic

 

[PaveFE]

Now I got it. Thanks for all the help.

 

[PaveFE]

Okay, one more.

If I have a total number in one box, I want a formula that would put in text in another box that would be based on the number in the first box (i.e. First box = 0-9 second box would state "Low", 10-19 would state "normal", etc.)

I'm thinking the equation would go in the query, but what would the formula be and how would I put it in the query?

Thanks again, this place is great.

Vince

 

[VicRauch]

I would write a VBA Function to do this. That way, where ever you want this description (rating), you can use the function. And, if the requirements for Low, Normal, etc. ever change, you have only one place to go to do the maintenance.

 Function DetermineRating(mNumber) as String
  If mNumber < 10 Then
    DetermineRating = "Low"
  ElseIf mNumber < 20 Then
    DetermineRating = "Normal"
  ElseIf mNumber < 30 Then
    DetermineRating = "High"
  Else
    DetermineRating = "Excellent"
  End If
End Function

Now, in your query, you can enter this line, assuming "TotalNumber" is the number to be rated:
"Rating: DetermineRating([TotalNumber])"

Vic

 

[PaveFE]

Whoa, you lost me.
Where would I go to write this VBA?
The field in my query that has the total number is called "Expr6"
I don't yet have anything built (i.e. text box) to display the text that I want to appear.

Can you dumb this down for me? Sorry, and thanks.

Vince

 

[VicRauch]

Vince,

You are showing as a "programmer" in this forum, so I just assumed. What do you program?

Create a new Module, copy the code I gave you into the new module, save the module, give it a meaningful name as you save it.

Open the query that you are writing as the source for the form you are creating, and add the line I gave you to the "Field:" row. (in the bottom part of the query window.

That's all there is to it.

Vic

 

[PaveFE]

I tried to find out where I could change it to "Noob" (although I'm not that far behind), but couldn't see where. I guess when I joined that seemed like the most logical choice. I am familiar with MS Access in a lot of ways, but I don't use it often enough to remember everything, so that's why I hit up the forums. Thanks for the help.

Vince

 

[PaveFE]

I found something that works.

I inserted a text box on the form and put the following in the control source:
=Iif([Expr6]>=0 And [Expr6]<10,"Low",Iif([Expr6]>=10 And [Expr6]<20,"Normal",Iif([Expr6]>=20 And [Expr6]<25,"Moderate",Iif([Expr6]>=25 And [Expr6]<32,"High",Iif([Expr6]>=32 And [Expr6]<99,"Very High")))))

Works like a champ.

Thanks anyways,

Vince

 

[VicRauch]

Vince,
I started to explain to you the method you just found, but thought I would take the "high road" and show you the better method so your database would be easy to maintain. When you need this same function in another place, you will be able to copy what you have just coded and paste it in the new place you need it. After you do that three or four times, the user is going to say, "We have changed the Moderate and the High values. Now Moderate is from 20 to 28, and High is from 29 to 31. You will have to remember all 4 or 5 places you have this coded. Actually, this might not ever happen with this formula, but it will happen with some forumla before long, and then another, and another.
I was just trying to help you down the line. And actually, VBA is not scary, but is VERY useful.
Vic

 

[PaveFE]

No worries, I thank you for your help. I understand what you are saying, but this data believe it or not is not really for database use, they just want to be able to fill out this form on a computer and print it out (Hey, I'm just doing what the bosses want). I've taken classes on Access and have learned about normalizing data and such, but I didn't get much choice on this one. I'm not too worried about VBA, just not used to doing it.

Thanks again for all your help.

Vince