Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 12-04-2018, 04:11 PM   #16
Frothingslosh
Premier Pale Stale Ale
 
Frothingslosh's Avatar
 
Join Date: Oct 2012
Location: Flint, Michigan, USA
Posts: 3,144
Thanks: 81
Thanked 442 Times in 402 Posts
Frothingslosh will become famous soon enough Frothingslosh will become famous soon enough
Re: Calculated field

Methinks you forgot to attach your database.

__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Frothingslosh is offline   Reply With Quote
Old 12-04-2018, 04:32 PM   #17
Mat1994
Newly Registered User
 
Join Date: Nov 2018
Posts: 94
Thanks: 7
Thanked 1 Time in 1 Post
Mat1994 is on a distinguished road
Re: Calculated field

Yes, sorry. Here it is.
Attached Files
File Type: accdb DataBase.accdb (1.22 MB, 23 views)
Mat1994 is offline   Reply With Quote
Old 12-05-2018, 07:39 AM   #18
Frothingslosh
Premier Pale Stale Ale
 
Frothingslosh's Avatar
 
Join Date: Oct 2012
Location: Flint, Michigan, USA
Posts: 3,144
Thanks: 81
Thanked 442 Times in 402 Posts
Frothingslosh will become famous soon enough Frothingslosh will become famous soon enough
Re: Calculated field

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.

__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Frothingslosh is offline   Reply With Quote
Old 12-05-2018, 10:02 AM   #19
Mat1994
Newly Registered User
 
Join Date: Nov 2018
Posts: 94
Thanks: 7
Thanked 1 Time in 1 Post
Mat1994 is on a distinguished road
Re: Calculated field

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
Mat1994 is offline   Reply With Quote
Old 12-05-2018, 10:08 AM   #20
Frothingslosh
Premier Pale Stale Ale
 
Frothingslosh's Avatar
 
Join Date: Oct 2012
Location: Flint, Michigan, USA
Posts: 3,144
Thanks: 81
Thanked 442 Times in 402 Posts
Frothingslosh will become famous soon enough Frothingslosh will become famous soon enough
Re: Calculated field

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.)
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Frothingslosh is offline   Reply With Quote
Old 12-05-2018, 03:43 PM   #21
Mat1994
Newly Registered User
 
Join Date: Nov 2018
Posts: 94
Thanks: 7
Thanked 1 Time in 1 Post
Mat1994 is on a distinguished road
Re: Calculated field

Thank you for the tip on the normalization.

I have a few questions concerning the tables and the normalization.
-When a field can be a combination of numbers and lettre (example : latitude in decimal). What's the best field type? short text or number?

-I've attached the database for you to have a look. Because I'm not entirely sure of what I'm doing.
In my project :

I've created a table (tbl_Airport_info) where each airport is identified by a unique ICAO (4-lettre code), so I've set ICAO to be a primary key so that all the field of that table depend on a unique ICAO.

In a second table (tbl_Runway), an airport can have multiple runways (each runway has a unique number), so I set the runwayname as a primary key and called another field ICAO. Therefore, the runwayname is unique and depend on the ICAO. And the field ICAO in this tables makes it possible to have a relation between the airport and the runway tables. I am correct?

Following this logic, I've created a tables (tbl_Obstacles), where a runway can have multiple obstacles. In this table, I've set the ObstacleID (autonumber) has a primary key, and added a field runwayname. This allows a relation between the tbl_Obstacles and runwayname (because a unique runway can have multiple obstacles). I am correct?

Also, I've created a table (tbl_Procedure) where a unique runway can have multiple procedure. Therefore, I created a ProcedureID (autonumber) has a primary key, and added a field runwayname. This allows a relation between the table tbl_Runway and the table tbl_Procedure. I am correct?

This one, is not clear in my head. Each airport can have multiple aircraft (planes) assigned to it. Should I create a new table (tbl_Aircraft) where I set a AircraftID (autonumber) has a primary key and add two field ICAO and Actype (name I chosen for the aircraft type). Isn't there a better way? Because for example, 5 airport have 5 aircraft. That means I'll have 25 lignes to do. In the final database, we'll have hundreds of airport, so that could mean thousands of lignes.

I am understanding the method correctly?

Please excuse me for the jargon. I hope I was clear enough. Please let me know if you have any questions and feel free to make a comment on the database.
Attached Files
File Type: accdb DataBase_V7_WIP.accdb (1.41 MB, 16 views)
Mat1994 is offline   Reply With Quote
Old 12-05-2018, 03:48 PM   #22
Mat1994
Newly Registered User
 
Join Date: Nov 2018
Posts: 94
Thanks: 7
Thanked 1 Time in 1 Post
Mat1994 is on a distinguished road
Re: Calculated field

Also, if this is done properly (setting the tables correctly) the next step is to create the relationships (in Database tools)?

Mat1994 is offline   Reply With Quote
Old 12-06-2018, 04:04 AM   #23
Frothingslosh
Premier Pale Stale Ale
 
Frothingslosh's Avatar
 
Join Date: Oct 2012
Location: Flint, Michigan, USA
Posts: 3,144
Thanks: 81
Thanked 442 Times in 402 Posts
Frothingslosh will become famous soon enough Frothingslosh will become famous soon enough
Re: Calculated field

Sorry I didn't post anything last night - an emergency popped up at work yesterday evening, and we didn't get it cleared up until close to 9. I'll try to get you some commentary tonight.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Frothingslosh is offline   Reply With Quote
The Following User Says Thank You to Frothingslosh For This Useful Post:
Mat1994 (12-06-2018)
Old 12-06-2018, 04:13 PM   #24
Frothingslosh
Premier Pale Stale Ale
 
Frothingslosh's Avatar
 
Join Date: Oct 2012
Location: Flint, Michigan, USA
Posts: 3,144
Thanks: 81
Thanked 442 Times in 402 Posts
Frothingslosh will become famous soon enough Frothingslosh will become famous soon enough
Re: Calculated field

Okay, going though V7_WIP, here's some stuff I see:

tbl_Administrator
What is the purpose of this table? What do the records mean? Is this meant to be the User table? Is it a version log? What does it do, and why is each field in here? If this is a user table, than it violates every normal form there is. As a user table, the only fields in the table should be ones about the user - User name and password. If it's a version table, why is UserID the primary key? And what does 'source data' have to do with the user or database version? This looks like it needs to be three tables: a user table, a version table, and a 'data' table (which would be a lookup table - Google it).
Also:
UserName: I generally recommend saving first and last name separately. Makes searching easier. There's also no point in indexing it unless you plan on searching this table by this field, which is normally a bad idea.
VersionNum: Are you going to stick with simple numbers, or use the more traditional x.yy.zz format? I generally recommend the latter, which requires either a text format or three different fields.

tblAircraft
What is ICAO?
Actype looks like it needs to reference an 'AircraftTypes' lookup table.
Do you intend to store any other data about aircraft, such as manufacturer, maintenance history, current location, arrival and departure history, etc?

tbl_AirportInfo
This, of all tables, needs to use a surrogate key such as AirportID (autonumber) unless there is literally zero chance of ICAO either being duplicated or ever changing. If there is even the slightest possibility of ICAO changing, then make the surrogate key and just leave a unique index on ICAO to prevent duplicates.
Actype: ACType needs to be its own table. If you start listing 5, 10, 357 entries in a single field, it's going to be a pure nightmare to get the data out. On top of this, isn't tbl_Aircraft supposed to be your list of what planes come here?
AirportUsage: What are you trying to do here? It sounds like this should be two different fields: one Yes/no indicating whether or not the airport is active or inactive, and a second indicating its destination status.
MagHdg and MagVar: These look like they need to be in the Runways table, not the Airport table.
NumRwys: Unnecessary. The runways table should include all the runways in the airport, and you can simply count them when a count is needed.
RwyMeasType: Normalization violation. This field is about runways, not airports, and should be in that table.

tbl_Obstacles
RunwayName: See RunwayName down in the tbl_Runway comments.
Tree: Unnecessary. Trees should be listed among the other Obstacle types. Your UI can be designed to show special flags if trees are among the listed obstacles.
ObsType: Instead of doing a writeup, you should create a list of obstacle types, assign ObstacleTypeID as the PK, and move your obstacle descriptions there (ObstType and Number (THIS IS A RESERVED WORD - NEVER USE THIS AS A FIELD NAME. SAME WITH DESCRIPTION!) at a minimum). Your description in ObstType should be a generic discription, and this table could have a description of any unique characteristics. (This will also allow for easy future searches.)
SourceData: Needs a better field description - the one here sounds like it's describing the airport, not the obstacle.

tbl_Procedure
What does this table cover? What kind of procedures? Construction? Maintenance? Takeoff and landing? How does it relate back to the other tables? If it's a catch-all for all sorts of procedures, it might be a good idea to include a field for procedure types (again, using a lookup table).
MinGradient: What does a go-around gradient have to do with, say, refueling procedures or a part replacement schedule?
I think this particular table needs to be completely reworked into a standard format that you can use for any procedure that you'll be storing in it. Find the common items and build a table around that. Note that a general 'procedures' table is going to be very hard to build, but will also be MUCH easier to do searches on in the future.

tbl_Runway
This one actually looks good! Just one question:
RunwayName: Are you absolutely, positively 100% certain that RunwayName values will never, ever, under any circumstances whatsoever be changed? If the answer to that is anything other than 100% yes, then make a RunwayID autonumber field the primary key and just drop a unique index on RunwayName. Also, if you do that, replace RunwayName in tbl_Obstacles with RunwayID.

Okay, that's a first pass through. I can't guarantee that my chances will fully normalize it yet, but this is all design stuff you're going to need to look at.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Frothingslosh is offline   Reply With Quote
Old 12-11-2018, 12:41 PM   #25
Mat1994
Newly Registered User
 
Join Date: Nov 2018
Posts: 94
Thanks: 7
Thanked 1 Time in 1 Post
Mat1994 is on a distinguished road
Re: Calculated field

Thank you for your feedback.

tbl_Administrator
I've decided to stop working on this table for now. What I had in mind for this table was the user table with username and password. However, I also wanted to create a setting where you can choose (when opening the database) if the user wanted to edit the data or check it. Because, when one engineer edits the data, a different engineer has to check if the data is correct. That's why I created a field validateDate and ModifiedDate, so we can keep track of who did the latest editing or the latest checking.
'source data': We don't invent the data we input. So we need to track where the source comes from. That's why this field is repeated in the obstacle table. So the 'source data' in the tbl_Administrator provides the source of information for the airport and the one in the table tbl_Obstacles provides the source from a specfic obstable.

tbl_Airport_info:
I added a surrogate key (AirportID). Thank you for the advise, that if there is the slightest possibility the data to change then it shouldn't be a primary key and to create a surrogate key instead.
Actype: I'll come back to it later.
AirportUsage: I followed your advise. I created a yes/no indicating if the airport is active or not. And a second field with the airport status. In the form, I'll create a event where if the airport is inactive, the airport status can't be selected. (I'll already managed to do this in a previous version of the database)
NumRwys: ok, I deleted it. I'll add it when I need it.
RWYMeasType: You're 100% right. I changed it to the correct table (tbl_runway)
MagHdg: I changed it to the correct table

tbl_Obstable:
RunwayName: Like the comment on the surrogate key (AirportID), you're right. I'm not 100% it won't change. So I changed it to a RunwayID.
ObstType: I created a new table. Because for now, there is only 3 types of obstacle. But in the future, if the number of types changes, it will be a lot easier to add an obstacle type to a new table then in this existing field. I also changed the names of the reserved words.

tbl_Procedure:
This table covers the procedure of the event the aircraft has one engine failure while flying. I can't go to much in details because I don't fully understand the data that is going to be stored in it.


Actype:
This is a tricky one. Yes it referes to the list of planes. And no, we don't want extra data about the aircraft (just the list of planes). So, I created a table tbl_Aircraft where I'll store all the planes. I'm having trouble creating the relationships with the other tables. So the logic of the database is : one airport can have many runway, one runway can have many obstables, one obstacle can have many procedure. Plus, one airport can have many aircraft and one runway can have many aircraft. The engineers would like to see the list of planes corresponding to the airport and the list of planes corresponding to the runway separately. Why? Because when a list of plane is assigned to an airport, our team can choose to restrict the list when assigning the planes to the runway. (for example, when 5 planes are assigned to an airport, our team can choose to allow only 2 planes to use the runway).
So for me to create the relationship between the tbl_Aircraft (list of planes) and the tbl_Airport_info and the tbl_Runway. I thought of adding in the tbl_Aircraft two fields, AirportID and RunwayID. However, if I do this, it means that for each plane I need to assign the airport and all the combination of runway possible. This is going to create a huge data? Is there a better way of linking the aircraft with the airport and the runway?

If it's not clear, let me know and I explain things differently.
Thank you,
Mat
Mat1994 is offline   Reply With Quote
Old 12-11-2018, 01:15 PM   #26
Frothingslosh
Premier Pale Stale Ale
 
Frothingslosh's Avatar
 
Join Date: Oct 2012
Location: Flint, Michigan, USA
Posts: 3,144
Thanks: 81
Thanked 442 Times in 402 Posts
Frothingslosh will become famous soon enough Frothingslosh will become famous soon enough
Re: Calculated field

I don't have a lot of time at the moment, but if your runways are already assigned to airports (and they damned well should be), then you don't need to add AirportID to tblAircraft, since it's implicit in the relationship to RunwayID.

It sounds like you need to work out exactly how to handle the relationship between aircraft, airports, and runways. In English, what exactly are you tracking? Takeoff and landing schedules? Is each aircraft assigned a runway permanently? Are these permanent or short-term assignments? Do you need to keep a history? It sounds like you need an 'assignments' or 'events' table as well that would track a runway, an aircraft, and any other pertinent information.

If aircraft get assigned to the airport as a whole AND permanently assigned to runways as well, then that's a different situation, as we need to revisit the whole structure, especially if they can be assigned to multiple airports at the same time. As plog likes to point out, you should basically never have relationships that result in a loop. That almost always ends up causing problems down the road, and indicates a failure to normalize correctly.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Frothingslosh is offline   Reply With Quote
Old 12-11-2018, 01:25 PM   #27
June7
Newly Registered User
 
June7's Avatar
 
Join Date: Mar 2014
Posts: 1,378
Thanks: 0
Thanked 334 Times in 334 Posts
June7 will become famous soon enough
Re: Calculated field

Latitude and longitude in decimal should be number (double) type fields. There are no letters in these values. A decimal point is part of a number.

I used to work for state DOT and also had to build a db for airports, although I think mine was much simpler in content, very basic info. I used FAA airport IATA as PK/FK (I know, it's text, but it's a small db) and FAA SiteID for other descriptive info. We also used a proprietary app to track pavement inspection info (PAVER). That app was based on Access mdb model and I had to set up my Airport db to interact with that db (think they've rewritten PAVER user interface into .net platform, guessing backend still Access). Also had to interact with ArcGIS personal geodatabase (again, mdb model). Using FAA IATA as well as the runway/taxiway ID's we assigned was easiest way to link these data sources.

Yes, runway/taxiway designations can change. FAA assigns runway designation based on compass heading and when magnetic north changes, they tend to rename the runways, even by 1 degree. Then airports frequently get rebuilt or added to - adding a new taxiway could be mean renaming a bunch of them.

Didn't care about planes. How can a plane be assigned to a runway? Traffic controllers decide which runway (assuming more than one) planes use and assignment can depend on environmental conditions (wind direction is big factor).
__________________
To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by June7; 12-11-2018 at 02:16 PM.
June7 is offline   Reply With Quote
Old 12-16-2018, 12:49 PM   #28
Mat1994
Newly Registered User
 
Join Date: Nov 2018
Posts: 94
Thanks: 7
Thanked 1 Time in 1 Post
Mat1994 is on a distinguished road
Re: Calculated field

I 've managed to create the relationship between the aircraft and the tbl_Airport and tbl_Runway. To do so, I created a table tbl_Aircraft with 2 fields, AircraftID and Actype.
Then, in both table (Airport and Runway) i added a field ActypeAirport and ActypeRunway using a data type 'Lookup Wizard' where 'I want the lookup field to get the values form another table or query. Then in Database Tools, relationships, I linked the Actype (from tbl_Aircraft) to ActypeAirport (form tbl_Airport) and to ActypeRunway from (tbl_Runway). I did not tick the enforce referential integrity.

A plane can be assigned to a runway because planes are differents in any way, and they don't use the same runway. Some runway, can allow some Airbus planes to use it but no Boeing planes. A small plane (a tourist one) doesn't use the same runway as a big planes (commercial flight).
For the latitude/longitude, at the moment, are inputted in degree.


For the moment the database works ok. The relationship seem good and the forms I created work approximatly. I still have some issues but I think I can solve them myself.

Thank you very much for all your help! You've helped me a lot.
If you have more questions, you're free to ask
Mat

Mat1994 is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Calculated field in a query/table based on a calculated field joannkt General 5 11-07-2013 11:06 AM
Subform linked by calculated field gives field cannot be updated error evwool Forms 0 11-28-2012 01:28 PM
Using 2 field values from the same field to populate calculated control on report ML! Reports 5 09-02-2010 06:16 AM
Basing a calculated field on another calculated field duckster Queries 1 04-12-2006 09:39 PM
Whish is faster? - Calculated field in QUERY or Calculated field in REPORT? Cosmos75 Reports 3 03-18-2003 09:26 AM




All times are GMT -8. The time now is 01:23 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World