Go Back   Access World Forums > Microsoft Access Discussion > Theory and practice of database design

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 05-10-2017, 01:42 AM   #1
rodgthedodge
Newly Registered User
 
Join Date: May 2017
Posts: 3
Thanks: 1
Thanked 0 Times in 0 Posts
rodgthedodge is on a distinguished road
Tables

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.

rodgthedodge is offline   Reply With Quote
Old 05-10-2017, 01:56 AM   #2
June7
Newly Registered User
 
Join Date: Mar 2014
Posts: 72
Thanks: 0
Thanked 18 Times in 18 Posts
June7 is on a distinguished road
Re: Tables

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?
June7 is offline   Reply With Quote
Old 05-10-2017, 03:23 AM   #3
Frothingslosh
Premier Pale Stale Ale
 
Frothingslosh's Avatar
 
Join Date: Oct 2012
Location: Flint, Michigan, USA
Posts: 2,524
Thanks: 56
Thanked 357 Times in 323 Posts
Frothingslosh will become famous soon enough Frothingslosh will become famous soon enough
Re: Tables

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.

__________________
"I do not feel obliged to believe that the same God who has endowed us with sense, reason, and intellect has intended us to forgo their use and by some other means to give us knowledge which we can attain by them." -Galileo Galilei
Frothingslosh is offline   Reply With Quote
Old 05-10-2017, 04:32 AM   #4
plog
AWF VIP
 
Join Date: May 2011
Posts: 8,050
Thanks: 10
Thanked 1,936 Times in 1,897 Posts
plog has a spectacular aura about plog has a spectacular aura about plog has a spectacular aura about
Re: Tables

Quote:
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.
plog is offline   Reply With Quote
Old 05-10-2017, 04:38 AM   #5
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 10,698
Thanks: 33
Thanked 1,703 Times in 1,655 Posts
jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice
Re: Tables

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

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is online now   Reply With Quote
Old 05-11-2017, 03:16 AM   #6
rodgthedodge
Newly Registered User
 
Join Date: May 2017
Posts: 3
Thanks: 1
Thanked 0 Times in 0 Posts
rodgthedodge is on a distinguished road
Re: Tables

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

Last edited by rodgthedodge; 05-11-2017 at 03:43 AM.
rodgthedodge is offline   Reply With Quote
Old 05-11-2017, 03:40 AM   #7
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 10,698
Thanks: 33
Thanked 1,703 Times in 1,655 Posts
jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice
Re: Tables

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.

__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is online now   Reply With Quote
Old 05-11-2017, 04:36 AM   #8
plog
AWF VIP
 
Join Date: May 2011
Posts: 8,050
Thanks: 10
Thanked 1,936 Times in 1,897 Posts
plog has a spectacular aura about plog has a spectacular aura about plog has a spectacular aura about
Re: Tables

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

plog is offline   Reply With Quote
The Following User Says Thank You to plog For This Useful Post:
rodgthedodge (05-23-2017)
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Backup Tables connected to SQL Server as Lokal Tables in accdb File Reflex_ht Modules & VBA 1 11-26-2012 10:15 AM
Forms that update/create records in multiple tables, including junction tables excal Forms 5 07-11-2011 08:46 PM
Linking Tables and Creating Reports based on those linked tables rexmorgan Tables 1 01-30-2011 08:03 PM
Update query using 3 tables: source, joining, destination tables J.Rousseau Queries 1 04-19-2007 07:31 PM
Moving data from 2 related tables to 2 new tables as an archive JJKramer Tables 4 04-04-2007 07:18 AM




All times are GMT -8. The time now is 12:09 PM.


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

Sponsored Links

How to advertise

Media Kit


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