Calculated field

[Mat1994]

Hi All,

I working on Access 2007-2016. I have a question about calculated fields in tables.
I creating an airport database. In a table (tbl_Runway), I have 5 fields I'd like to automatically calculate, TORA, TODA, ASDA, CWY and SWY.
The equations are :
TODA=TORA+CWY
ASDA=TORA+SWY
CWY=TODA-TORA
SWY=ASDA-TORA
TORA=TODA-CWY=ASDA-SWY

I tried inputing this equations in an expression builder but as they all depend on one another, so it doesn't work. I know it's possible to do this in the forms, but I'd like to be able to do it in the tables.

I'd like to input this equations but still be able to change a value if needed.
Can someone hep me?

Thank you for your help,
Mat

 

[theDBguy]

Hi,

I think you’re having problems because you have a circular reference. Also, a calculated field’s resulting value cannot be changed. So, you may end up just using a form, if you knew how to do that.

 

[Frothingslosh]

First of all, you shouldn't be storing calculated values in your tables unless there's an overriding business reason to do so. As a basic example, there's no reason to store invoice amounts in a database; you just store order quantities, unit prices, and discount amounts if applicable, and generate the totals at runtime.

Second, as theDBguy mentioned, your formulas are completely messed up. You provided this:

TODA=TORA+CWY
ASDA=TORA+SWY
CWY=TODA-TORA
SWY=ASDA-TORA
TORA=TODA-CWY=ASDA-SWY

The problem is, that if you solve them algebraically, you get this:

TODA = TORA + (TODA - TORA)
ASDA = TORA + (ASDA - TORA)
CWY = (TORA + CWY) - TORA
SWY = (TORA + SWY) - TORA

TODA = TODA
ASDA = ASDA
CWY = CWY
SWY = SWY

TORA = TODA - CWY
TORA = TODA - (TODA-TORA)
TORA = TODA - TODA + TORA
TORA = TORA

TORA = ASDA - SWY
TORA = ASDA - (ASDA - TORA)
TORA = ASDA - ASDA + TORA
TORA = TORA

TORA = TORA = TORA Heads up pearl harbor

Before you do anything else, you need to go back and review those formulas, because all permutations only solve to themselves.

 

[Mat1994]

Thank you.
Sorry, I've just talked to my manager. He told me, that he would like to input TORA/TODA/ASDA and it automatically calculates CWY/SWY or he inputs TORA/CWY/SWY and it automatically calculates TODA and ASDA.

So I understood I can't do it in the tables but I could do it in the forms.

Assuming I manage to code the equations. My question now is, how do you save the data in the table for specific primary key?

Mat

 

[Frothingslosh]

So here is the question: Is this data that has to be permanently saved, or is it just used on the fly and then discarded? You could create a calculation section that has both options available, you can use a pop-up form, you can use a calc section that lets you select between the two approaches, etc. From there, you could display the info, save the info, or do things with it.

I think what you really need to do is step back and, IN NON-TECHNICAL ENGLISH, explain what precisely you need to save. Forget your database terminology, just explain things like you're trying to tell a regular high school kid with no database knowledge what this is about. What are you tracking, why are you tracking it, and what is going to be done with it?

Without knowing that, none of us will be able to help you that much. And try to avoid jargon if you can. You might know what TODA, ASDA, TORA, CWY, and SWY are, but I had to go look them up. Knowing them as something other than abbreviations actually helps to understand what's going on. From what I read at https://aviationthrust.in/notes/clearway-stopway-tora-toda-asda-ldadeclared-runway-distances/ , I see that these are actual physical runway statistics, but according to the example, the formulas you listed don't actually always apply.

 

[Mat1994]

Sorry, I tried to be too sybnthetic.

I'm doing an internship for an airport company. They have asked me to create a database with all the information about the airport in the world. I have no training in Access, I'm using Access 2007-2016. I started using Access 3 weeks ago.

I'm trying to create a database with all the airport information stored in the tables, and an interface (the forms) so the user can visualize the data, edit it, save it and export it in a specific format. So the idea, is that the user choose an airport and all the information about it autopopulates itself.

For the moment, I've created the tables with only a few data in it. Now I'm creating the forms for the users to see the data and edit it (I'll take care f the export part later).

To come back to the calculated fields. After talking to my manager, he would like to input 3 variables and it automatically calculates the missing one. I guess I be able to do that on my own.

However, what i don't know is : How to populate the information form the tables, let the user edit it (if needed), and save the data in the tables.

Please let me know if it's still no clear,
Thank you for your help,
Mat

 

[Frothingslosh]

Do you have any database training at all? It can be a very complex discipline, unfortunately.

As a rule, before you even START with the software, you need to figure out what data users are going to be seeing (reports), then figure out what data needs to be saved and/or tracked. Once you have that, THEN you start on the tables, but you want to work it out on paper first. The forms, while they generally take the most time, should be the LAST thing you build.

At that point, you need to look into database normalization, which while complex, can be summed up as 'each table should be about one 'thing''.

In regards to what you specifically asked about, it sounds like you're going to want fields for all five of those values. The problem is that based on the link I provided earlier, not all airports follow the formulas you provided. Specifically, look at the differences between diagrams B, C, and E. B shows the TODA being the sum of ASDA and CWY, while E shows TODA being the sum of ASDA and the difference of SWY and CWY, while C shows TODA being ASDA MINUS the SWY.

That's the kind of thing you need to resolve before you get going.

You might also consider looking through this link to see if there's anything that might be of use: http://www.databaseanswers.org/data_models/ . There might be something in section 4 (Air Transport), especially the various Airport Management links.

 

[Mat1994]

I've got no training at all. For the moment, I only use tutorial on the internet. I have booked a basic training session next week, but in the mean time I have to continue on the project.

The previous intern has done sorting out what data has to be stored, the data the user need to see, the data the user may edit. She sorted out the variable in differents tables. She even normalized the database. So I'm the one creating the Access databse based on the information she provided.

For the equations, the engineers would like to :
Or input TORA, TODA, ASDA and it automatically calculates CWY and SWY
Or input TORA, CWY, SWY and it automatically calculates TODA and ASDA

So to calculate CWY, SWY I'll use CWY=TODA-TORA and SWY=ASDA-TORA
And to calculate TODA, ASDA I'll use TODA=TORA+CWY and ASDA=TORA+SWY

This equations are the one that has to be entered. The link you shared is correct however they aren't the ones I wish to use.

Maybe I wasn't clear enough. When the user choose an airport, I would like the fields mentioned to autopopulate themselves with the data saved in the tables. However, this data can change. So I would like the user to be able to edit the data by inputing TORA/TODA/ASDA and it calculate CWY/SWY (and vise-versa).
So, coding the equations shouldn't be a problem. However, I don't know how to show to data to the user, let the user edit it and save it in the table.

In the form, I've created the 5 fields. These fields will let the user see the number. I've also created 2 buttons were I'll write the code to solve the equations. How can I get the data of the tables to pop-up in these fields, let the user edit them, convert them (that I can do) and then save them in the tables?

Mat

 

[Frothingslosh]

Okay, there are a TON of different ways to do it. One simple method is to create the form based on whatever table has these values, and include them. Then you can include a pair of buttons - one for determining TODA and ASDA, the other for determining CWY and SWY. (You can also use a pop-up screen, triggers as fields are updated, a special update/calculation section...it's really up to you.)

Some sample 30 second code might look something like this:

Private Sub cmdTodaAsda_Click()

Dim Msg As String

    [COLOR="SeaGreen"]'BASIC VALIDATION[/COLOR]
    Msg = "Please fix the following errors:" & vbCrLf & vbCrLf
    
    If Nz(Me.TORA, 0) = 0 Then Msg = Msg & "*Please enter TORA"
    If Nz(Me.CWY, 0) = 0 Then Msg = Msg & "*Please enter CWY"
    If Nz(Me.SWY, 0) = 0 Then Msg = Msg & "*Please enter SWY"
    
    [COLOR="seagreen"]'If an error was found, notify the user.[/COLOR]
    If Msg <> "Please fix the following errors:" & vbCrLf & vbCrLf Then
        Beep
        MsgBox Msg, vbCritical
    Else
        [COLOR="seagreen"]'Calculate ASDA and TODA[/COLOR]
        Me.ASDA = Me.TORA + Me.SWY
        Me.TODA = Me.TORA + Me.CWY
    End If
    
End Sub

Note that it does a very basic validation as well.

If you want to save the changes immediately, you can add either Me.Dirty = True or DoCmd.RunCommand acCmdSaveRecord. Otherwise, you can leave it until the user is done editing and saves their changes themselves.

I'm attaching the Access 2010 DB I threw together for a sample for you. Please bear in mind that it is the result of about 3 minutes' work - you do NOT want to turn in anything that looks like that.

I did not code the other button - that's left for you to do. The fun part of UI implementation is that there are LOTS of ways to do most things, espcially something as open-ended as 'calculate either of these pairs of fields from values entered in other fields', and this is no exception.

 

[Mat1994]

Thank you.

I have some questions on what you did.

Concerning the convert commande :
So I've tried your convert code on your form and it works. I created the commande button to convert to CWY/SWY and it works.
However, when I create the commande button to convert TODA and ASDA. The code doesn't work at 100%.
For example : When I enter in the fields TORA=100, CWY=15 and SWY=6 I get TODA=10014 and ASDA=1006
Do you know why?

Also, like you, I have a runway ID but mine is a combobox. I would like to, when choosing a runway ID, to populate the fields TORA, TODA, ASDA, CWY and SWY with the value stored in the tables. Then let the user edit the value and save then.
I know I can use in the fields =[runwayID].column but this doesn't allow the user to edit the value.
Do you know another way to populate the fields? And let the user edit the value?

Concerning saving the value :
In your form it works. When you convert, it save the all the values in the tables. In my case, it tried adding If Me.Dirty Then Me.Dirty = False (I tried with True) and I tried adding DoCmd.RunCommand acCmdSaveRecord
None, seem to work. I wrote them between "End If" and "End Sub" and I tried writing it just before the End If.
I think, it's because it doesn't know where to save the data (like to which corresponding runway ID). Am I correct?

Mat

 

[Frothingslosh]

Thank you.

I have some questions on what you did.

Okay, taking this from the top.

Concerning the convert commande :
So I've tried your convert code on your form and it works. I created the commande button to convert to CWY/SWY and it works.
However, when I create the commande button to convert TODA and ASDA. The code doesn't work at 100%.
For example : When I enter in the fields TORA=100, CWY=15 and SWY=6 I get TODA=10014 and ASDA=1006
Do you know why?

You'll need to cut and paste the exact code you used. (Please use code tags - they preserve formatting, making it much easier to read.) I just entered those same values into the attached demo database and came up with 115.00 and 106.00.

Also, like you, I have a runway ID but mine is a combobox. I would like to, when choosing a runway ID, to populate the fields TORA, TODA, ASDA, CWY and SWY with the value stored in the tables. Then let the user edit the value and save then.
I know I can use in the fields =[runwayID].column but this doesn't allow the user to edit the value.
Do you know another way to populate the fields? And let the user edit the value?

Okay, time for a quick lesson.

The field RunwayID is a Primary Key. That means that it is THE field that the database uses to keep track of which record is which. As a result, values in this field absolutely, positively MUST be unique. I achieved this through use of a data type called Autonumber. This field type CANNOT be edited by the user; it is, instead, automatically generated and assigned by the database when the record is first created.

In addition, RunwayID is what's called a 'surrogate key': it is completely separate from the data and literally its only purpose is to keep track of which record is which. For lack of a better term, it's the 'label' Access uses when looking for records.

The table also contains what we call a 'natural key', meaning a field or combination of fields which uniquely identify each record. In this case, it's Airport and Runway Number. Every airport has a different code, and no airport has multiple runways with the same number. That means that every combination of Airport and Runway Number MUST be unique. So, as a result, if you open the table in design mode and look at the indexes, you'll see I actually created a unique index on those two fields because I'm sneaky like that. Also to ensure that there's no duplication, but mainly the sneaky thing.

Anyway, one rule that most of us agree on here is that primary keys, especially when a surrogate key is used, should never so much as be SEEN by the user. They exist to let the database keep track of stuff, and should NEVER be changed. Surrogate keys make this easy, while natural keys can occasionally be changed, such as what could happen if an airport expands, adds more runways, then decides to renumber them. Some folks would disagree, but I'm part of the 'never let them see a PK' crowd. If you want to let the users set the primary key, you can, but be advised that changing a PK can literally break a database beyond salvaging. All you need to do to make that change is to change it from AutoNumber to Number (it will default to Long Integer, meaning it caps at roughly 2 billion).

As to locating the data, instead of having the user enter a runwayid and then populating the boxes, you would create things like 'cascading combo boxes' (look it up, then come back if you don't get it), a list box showing airport and runway number, or a pop-up form listing all the airport/runway combos (just to give three examples), then use code to move to or filter to the indicated record. Or you could have a form that lists each airport and runway number combo, then opens a form, subform, or the like showing the detailed info.

For straight data entry, on the other hand, just have them add a new record (a quick way is to use the navigation buttons on the bottom of the form, and have them click on the one with a right-pointing arrowhead and a gold star, or you can create a command button that does the same). On the new record, they can just start entering data. (On my version, the RunwayID is generated automatically, so they don't need to worry about it.) Users can save data automatically by tabbing off the last control and onto a new record, by using the navigation buttons to move to another record, or by closing the form (among other ways), or you can add a 'save record' command button.

Concerning saving the value :
In your form it works. When you convert, it save the all the values in the tables. In my case, it tried adding If Me.Dirty Then Me.Dirty = False (I tried with True) and I tried adding DoCmd.RunCommand acCmdSaveRecord
None, seem to work. I wrote them between "End If" and "End Sub" and I tried writing it just before the End If.
I think, it's because it doesn't know where to save the data (like to which corresponding runway ID). Am I correct?

Mat

I'd have to see your form itself, but it sounds like you haven't bound the form to your runways table. If that's intentional, let me tell you right now, unbound forms are a LOT of work, and really not something an access newbie should try. If it's unintentional or it's bound but not saving, let me know.

Oh, and by the way: Welcome to the deep end.

 

[Mat1994]

Thank you for the welcome to the deep end! The project is a lot of work and no one in the office knows how Access works.

For the problem I had for the convert commande. I manage to solve the problem by using Val().
For example : Me.TODA=VAL(TORA)+Val(me.CWY)

For the rest, you've given me a lot of information. I'm going to rethink my tables and the key (primary and surrogate). It's going to take me so time to test what works and what doesn't.

Also, you are right. I use unbound fields. The reason I do this, is because I can set the propreties as I want. For example, I can change the field to a combobox, cascading combobox, populate the right fields, select the row source, column count... The other reason, is that when the fields were bound, for some reason, it didn't populate the fields I wanted. And after changing things and looking for solutions, the fields ended up unbound. I know it's very hard and takes time.
If you have any tips for dealing with unbound fields, I'll happily take them.

My plan now, is to step back, rethink my key, create a new form and try again to autopopulate the correct fields and see if the user can edit them.

PS : If you want, I can send you my work in a private message so you can see what I managed to do for the moment.

 

[Frothingslosh]

Honestly, you should rethink your entire approach. Unbound forms are reinventing the wheel, as you have to provide all the functionality bound forms provide, including (as you discovered) data validation and confirmation.

If you had bound the form, you wouldn't need to use the Val function, for example, unless you had created the underlying fields as text instead of numbers. Unbound forms will require you to validate every input (which isn't a bad thing except that bound forms do a lot of it for you), as well as create and manipulate recordsets manually, and in a far slower manner than bound forms. On top of that, a number of VERY useful events like the assorted Update events just will not trigger.

There are times and places for using unbound forms, but this doesn't sound like it, especially in the hands of someone new to the application.

And if you need to modify rowsources and recordsources on the fly, that's done the same way whether a form is bound or not. If you had fields not populating correctly, it wasn't because the form was bound, but rather because the form was built incorrectly in some way. Note that in my sample database, the form was bound and worked perfectly, with everything populating correctly, even after records were changed.

As a rule, we frown on working through PM. Half of the point is so that people in the future who have the same issue can see what was done and how the issue was fixed. The single time I chose to do that, it was to deal with dry business and procedural requirements that no viewer would have found useful, and we still added a summary and the fixed version to the thread when we were done.

Instead of PM'ing me the database, strip it and attach it to your post. To do that, remove everything that isn't relevant to the issue. Kludge in some code if you need to set global values that the form needs. Remove any tables, forms, queries, macros, and modules that aren't absolutely necessary to run this form. Remove all sensitive, confidential, or proprietary information, and just put sample data in whatever tables you keep. The data doesn't have to be live, it just has to work. You can attach it to the post via the advanced editor.

 

[Frothingslosh]

And don't take my comment about the form being built incorrectly personally. Half of this job is fixing mistakes. I first used Access when, from the looks of your username, you were 1 at most, and I'm STILL learning how to do things.

 

[Mat1994]

No hard feeling on your comment. The fact I asked the question is to get a answer. And it comes from the fact I did something wrong in the first place.


I'm going to rethink through my approach. I'll probably come back with more questions.

I've attached my database, so you can have a look and give me a feedback if you want. I've left everything I managed to do (that's not a lot).
The data I inputed are only example I made up to match the data of a reel airport database.


Thanks you for your help,
Mat

 

[Frothingslosh]

Methinks you forgot to attach your database.

 

[Mat1994]

Yes, sorry. Here it is.

 

[Frothingslosh]

There are a lot of issues with the database you posted; too many for me to deal with while I'm waiting on queries to run at work. I'll take a closer look after work, but to start with, you need to go back over every table and double-check the structures. It has Autonumber ID fields that are not used as primary keys, description fields as primary keys (never NEVER do that), and field names that include things other than letters, numbers, and underscores, which makes working with them MUCH tougher than it has to be. It doesn't appear that data types are optimized at all, and the tables are not sufficiently normalized.

And that's just from looking at the first two tables.

 

[Mat1994]

Thank you!
I'll deleted my queries and my forms. I'll concentre on normalizing the tables correctly.
If you have more comments, I'll be glad to hear them.

It's maybe to much asking, but when I finished normalizing the tables, can I post the database here to get a feedback?

Mat

 

[Frothingslosh]

Go for it.

Honestly, before you do any of that, you should google 'Database Normalization' and read up on it. If the tables aren't normalized correctly, you wind up with problems that keep getting bigger and bigger, and it's very time-consuming to fix them after the fact. (That's a big part of the reason why forms are created LAST.)