Tables

[rodgthedodge]

Hi,, I have a database with several tables
Order request
pre site survey
on site survey
Pilots log
all the tables have a combination of the same fields, ie all have an
order number
date
Pilots name

is it possible to enter data into one table and share this with all the others ? does that make sense.

 

[June7]

Build queries that join tables.

If you have 3 fields defining primary/foreign key values, then you have to build the queries with compound links.

Have you studied introductory Access tutorials?

 

[Frothingslosh]

One quick question for you, rodg. Do pre site survey and on site survey have the same fields? If they do, you should probably combine the two as 'Surveys' with one additional field indicating the survey type.

Proper normalization right up front will make your life much, much easier in the long run.

 

[plog]

all the tables have a combination of the same fields, ie all have an
order number
date
Pilots name

It sounds like you need a table with those 3 fields in it, along with a primary key which you then use as a foreign key in the other tables. So instead of those 3 fields in each table, you simply have a number that links to a record in this new table.

 

[jdraw]

rodg,

I agree with the comments posted so far. However, I'd like to see a description of your "project" in business terms (plain, simple English). It could be that the description will show you and readers what things are involved and how they may be related. All that to say there may be some design options, and will clarify your requirement.
Good luck with your project.

 

[rodgthedodge]

Here is the database its on dropbox
I created relationships, I now presume that is the wrong way to do it

w w w.dropbox.com/s/ks3dbrvyt0bxv6w/Pilots%20%20Log%20Feb%202016.accdb?dl=0

w w w.dropbox.com/s/sn65t57xgnd151u/relationships.JPG?dl=0

 

[jdraw]

I have looked at your tables and relationships briefly(accdb file).You have done a lot of physical Access work. However, I can't offer comments on your design because I don't know your business and processes.

My guess is that your design does not meet your business requirement in great detail. The fields/attributes may be there, but the structure seems suspect to me.
From an Access view, you should refrain from using field and object names that include embedded spaces. Spaces in name swill lead to syntax frustration.
I'm not sure what the purpose is for most fields in your ...survey tables.

You can lay out your draft data model (proposed tables and relationships) and test it without using Access. Many developers will advise that pencil and paper and a clear description of the business will save many hours of effort in database design, and getting the tables and relationships designed and tested is the most critical part of database.

Good luck.

 

[plog]

I can offer thoughts on your design--not because I understand your data, but because I see big glaring errors:

There should only be one path between tables in relationships. You essentially have a loop among the 3 tables on the right--each connected directly to the others. That is incorrect. I don't know which relationships are the correct ones, but what you have is wrong.

Another issue I see is the same fields in multiple tables (Piolot, Observer, Latitude, Longitude, etc.). This too is wrong. Instead you store that data at the lowest level you need it at and then your relationships make it available to the other tables in those relationships.

I believe I was right in my initial assesment (you need a table to combine all those shared tables, then use its foreign key). However, I really don't understand your data, I just see the big errors.

Lastly, 2 nitpicks about your naming: you should only use alphanumeric characters in names. That means, no spaces nor special characters (prenthesis, commas, etc.) in table and field names. It just makes coding easier down the line. Also, 'Date' is a reserved word and very generic. You should prefix it with what that data is for instead of just 'Date' (OrderDate, FlightDate, etc.)