Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 01-16-2018, 01:48 PM   #16
mike60smart
Newly Registered User
 
mike60smart's Avatar
 
Join Date: Aug 2017
Location: Dunbar, Scotland
Posts: 196
Thanks: 21
Thanked 40 Times in 40 Posts
mike60smart is on a distinguished road
Re: Table Design Advice

Hi

I agree with plog, it is recommended that all tables have Autonumber Primary Keys.

Also name the PrimaryKeys with the name of the table.

ie

tblStates
-StateID - PrimaryKey - Autonumber
-State - Text (Full Name of the State)
-ABR- Text - (Abrieviation of the State name)

mike60smart is offline   Reply With Quote
The Following User Says Thank You to mike60smart For This Useful Post:
Harper (01-17-2018)
Old 01-17-2018, 09:34 PM   #17
Harper
Newly Registered User
Silver Supporter
 
Join Date: Dec 2017
Posts: 17
Thanks: 11
Thanked 0 Times in 0 Posts
Harper is on a distinguished road
Re: Table Design Advice

Quote:
Originally Posted by plog View Post
As it stands now, you would bring in 2 different instances of People and JOIN one of them via MangerID and the other via ForemanID. However, you need to fix some things still.

1. Use autonumber primary keys as table IDs. This makes things run more efficiently and releases you from making sure you are creating good ones when you add data. This will require you change the foreign key in tables that relate via those ID fields to numeric data types.

2. You still have numerated field names. See my #3 from before.
I used auto-number primary keys in each table and changed foreign keys in tables. Does it look OK?

I made one table for numerated fields "bonus" and "notes," but wouldn't I still need numerated fields in the master table? Please see my attachment.
Attached Files
File Type: zip DatabaseHarper3.zip (80.8 KB, 15 views)
Harper is offline   Reply With Quote
Old 01-18-2018, 02:44 AM   #18
mike60smart
Newly Registered User
 
mike60smart's Avatar
 
Join Date: Aug 2017
Location: Dunbar, Scotland
Posts: 196
Thanks: 21
Thanked 40 Times in 40 Posts
mike60smart is on a distinguished road
Re: Table Design Advice

Hi Harper

Can you explain the purpose of your Database?

Currently you have not set any relationships between any of the tables.

If you can explain how you would like to enter your data.

mike60smart is offline   Reply With Quote
Old 01-18-2018, 05:33 AM   #19
plog
AWF VIP
 
Join Date: May 2011
Posts: 8,806
Thanks: 10
Thanked 2,114 Times in 2,069 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Table Design Advice

Since you shouldn't have numerated fields anywhere/time, no you shouldn't have them in the master table.

You haven't tied anything together, you simply made a table to appease me. I don't know how the tables work together--complete the Relationship tool. And as mike suggested, give a description of what this database is for. Do not use any database jargon--pretend its career day at an elementary school and you are telling the kids what it is this database does for you.
plog is offline   Reply With Quote
Old 01-21-2018, 08:24 PM   #20
Harper
Newly Registered User
Silver Supporter
 
Join Date: Dec 2017
Posts: 17
Thanks: 11
Thanked 0 Times in 0 Posts
Harper is on a distinguished road
Re: Table Design Advice

Quote:
Originally Posted by plog View Post
Since you shouldn't have numerated fields anywhere/time, no you shouldn't have them in the master table.

You haven't tied anything together, you simply made a table to appease me. I don't know how the tables work together--complete the Relationship tool. And as mike suggested, give a description of what this database is for. Do not use any database jargon--pretend its career day at an elementary school and you are telling the kids what it is this database does for you.
Hi all! I completed the relationship tool. I made the database as a self-learning tool. How'd I describe it...it's used to help a home construction company to track the attributes of its different teams, attributes that may change from year-to-year.

The query I set up is not making sense to me. How'd I extract the manager and foreman names?
Attached Files
File Type: zip DatabaseHarper4.zip (76.0 KB, 12 views)
Harper is offline   Reply With Quote
Old 01-22-2018, 03:10 AM   #21
mike60smart
Newly Registered User
 
mike60smart's Avatar
 
Join Date: Aug 2017
Location: Dunbar, Scotland
Posts: 196
Thanks: 21
Thanked 40 Times in 40 Posts
mike60smart is on a distinguished road
Re: Table Design Advice

Hi Harper

I modified the query to show you how to extract Managers & Foremen

You have still not really explained what you need the database to do for you.

From what you have currently produced I believe you want to record Jobs carried out by Specific teams.

If this is true then yet again your table structure does not allow you to record this information.

Please give us a very simple explanation in idiot English and not use database jargon.

DatabaseHarper4.zip
mike60smart is offline   Reply With Quote
The Following User Says Thank You to mike60smart For This Useful Post:
Harper (01-22-2018)
Old 01-22-2018, 09:44 PM   #22
Harper
Newly Registered User
Silver Supporter
 
Join Date: Dec 2017
Posts: 17
Thanks: 11
Thanked 0 Times in 0 Posts
Harper is on a distinguished road
Re: Table Design Advice

Quote:
Originally Posted by mike60smart View Post
Hi Harper

I modified the query to show you how to extract Managers & Foremen

You have still not really explained what you need the database to do for you.

From what you have currently produced I believe you want to record Jobs carried out by Specific teams.

If this is true then yet again your table structure does not allow you to record this information.

Please give us a very simple explanation in idiot English and not use database jargon.

Attachment 69427
I need the database to tell me things like: how often a foreman and manager worked together. I just need the database to store these attributes for each team.

How would I pull the notes without using numerated fields in the master table?


Harper 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
Need design advice gmatriix Forms 3 06-20-2013 04:57 AM
Advice on db design kalmi General 2 07-18-2011 05:00 PM
advice needed for table layout/design for a newbie jas118 Tables 8 06-05-2010 02:49 PM
Question Design Advice LadyDi General 4 03-02-2009 01:54 PM
Advice on table design Fear Naught Tables 1 03-09-2006 01:45 PM




All times are GMT -8. The time now is 03:18 AM.


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 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World