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

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 05-21-2019, 07:08 AM   #1
billgyrotech
Newly Registered User
 
Join Date: Apr 2013
Posts: 155
Thanks: 13
Thanked 1 Time in 1 Post
billgyrotech is on a distinguished road
Starting Over Database Tables and Relationships

Hello,


I have existing data in tables and want to create relationships. My main table is the AFRs.


Can someone please guide me and help to create the proper relationships ?


Thank you


Last edited by billgyrotech; 05-24-2019 at 05:41 AM.
billgyrotech is offline   Reply With Quote
Old 05-21-2019, 07:28 AM   #2
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 1,822
Thanks: 17
Thanked 344 Times in 341 Posts
Mark_ will become famous soon enough Mark_ will become famous soon enough
Re: Starting Over Database Tables and Relationships

Very hard for us to answer as we'd need YOU to tell us what is supposed to go together. We could take some guesses, but without a specification that tells us what you are trying to do and how you want to accomplish it, we can't give you accurate information.
Mark_ is offline   Reply With Quote
Old 05-21-2019, 07:29 AM   #3
GinaWhipp
AWF VIP
 
GinaWhipp's Avatar
 
Join Date: Jun 2011
Location: Ohio, USA
Posts: 5,291
Thanks: 20
Thanked 875 Times in 860 Posts
GinaWhipp has a spectacular aura about GinaWhipp has a spectacular aura about
Re: Starting Over Database Tables and Relationships

Hmm, well your table set-up needs a little help. Your AFRs table looks like an Excel spreadsheet. They should look more like...

Customers
AFRParts
CustomerAFRParts (Linked to AFRParts and Customers)
CustomerRepairs (Linked to CustomerAFRParts)
etc...

See where I am going?

You should also be storing the Primary Keys not the Customers Name or actual Part Number, etc. Then once Relationships are set up you can *pull* that information.

__________________
Gina Whipp
Microsoft MVP (Access 2010-2015)


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
GinaWhipp is online now   Reply With Quote
Old 05-21-2019, 07:39 AM   #4
billgyrotech
Newly Registered User
 
Join Date: Apr 2013
Posts: 155
Thanks: 13
Thanked 1 Time in 1 Post
billgyrotech is on a distinguished road
Re: Starting Over Database Tables and Relationships

I have no idea how to do this.
billgyrotech is offline   Reply With Quote
Old 05-21-2019, 07:40 AM   #5
billgyrotech
Newly Registered User
 
Join Date: Apr 2013
Posts: 155
Thanks: 13
Thanked 1 Time in 1 Post
billgyrotech is on a distinguished road
Re: Starting Over Database Tables and Relationships

The AFRs is for doing repairs and having parts attached to each one. There will be reports to follow that are needed.
billgyrotech is offline   Reply With Quote
Old 05-21-2019, 07:52 AM   #6
billgyrotech
Newly Registered User
 
Join Date: Apr 2013
Posts: 155
Thanks: 13
Thanked 1 Time in 1 Post
billgyrotech is on a distinguished road
Re: Starting Over Database Tables and Relationships

Please close this topic I need to do some reading on how to start a database properly. Sorry
billgyrotech is offline   Reply With Quote
Old 05-21-2019, 07:57 AM   #7
plog
AWF VIP
 
Join Date: May 2011
Posts: 9,252
Thanks: 10
Thanked 2,236 Times in 2,189 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Starting Over Database Tables and Relationships

What did you post? Is that your existing database or your attempt at normalization?

I suggest you read up on normalization and work througha few tutorials (https://www.w3schools.in/dbms/database-normalization/).

Here's what I see wrong with what you posted:

1. All those tables with just peoples' names should be combined into a 'Role' table. It would be structured like this:

RolePerson, RoleType
Steve Smith, Clerk
Steve Smith, Technician
John Davis, Clerk
Barry Miles, Repairmen
...

That elminiates all tables named after a role.

2. Improper use of foreign keys. You went through the trouble of setting up lookup tables with their own ID fields, but then you don't use those ID fields. For example, You have a Customers table and you have a Customer field in AFRs--so far so good. But in the AFRs.Customer you are storing the text value of Customer table, not their ID from the Customer table. That is incorrect. You should be using the ID value of Customer in AFRs. That's just one example, you do this a lot.

3. Storing values in field names. In AFRs, [12ASpec], [91ASpec] & [93ASpec] should not be field names, they shoudl be values in a field. Those fields need to be taken out of AFRs and put into their own table. It would look like this:

Specs
SpecID, autonumber, primary key
ID_AFRs, number, foreign key to AFRs.ID record that spec value belongs to
SpecType, number, this will hold the 12, 91, or 93 that is now in the field name
SpecValue, ?, this will hold the actual value you are putting into those 3 fields right now

Thats how you should store that data, not in 3 fields in AFRs named after their type. I don't know your data, but I suspect you've done this in more than just those 3 fields.

I suggest you read thru the link I gave above, make the changes I illustrated, set up your Relationship tool after you do and then post a screenshot of it back here for us to check.

plog is offline   Reply With Quote
Old 05-21-2019, 08:24 AM   #8
GinaWhipp
AWF VIP
 
GinaWhipp's Avatar
 
Join Date: Jun 2011
Location: Ohio, USA
Posts: 5,291
Thanks: 20
Thanked 875 Times in 860 Posts
GinaWhipp has a spectacular aura about GinaWhipp has a spectacular aura about
Re: Starting Over Database Tables and Relationships

Quote:
Originally Posted by billgyrotech View Post
Please close this topic I need to do some reading on how to start a database properly. Sorry
Nope, not going to close that is why we are here. There are some links on the bottom right of this page...
https://www.access-diva.com/dm22.html

Have a look and come back with your updated Relationships.

Hmm, I see Plog posted a link as well, so you got some reading to do. Once you get thru that we'll still be here.
__________________
Gina Whipp
Microsoft MVP (Access 2010-2015)


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
GinaWhipp is online now   Reply With Quote
Old 05-21-2019, 08:48 AM   #9
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 11,891
Thanks: 73
Thanked 1,962 Times in 1,910 Posts
jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice
Re: Starting Over Database Tables and Relationships

In addition to the advice from others
Relationships are a reflection of the rules/facts in your business.
Things like:
A Customer may make 1 or Many Orders
An Order may contain 1 or Many Items


For your own benefit and experience, I recommend that you work through 2 or 3 of the tutorials at RogersAccessLibrary.
http://www.rogersaccesslibrary.com/T...lationship.zip
http://www.rogersaccesslibrary.com/T...nformation.zip

Each will take about 45 minutes and you will learn by doing.

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 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
Startup Tables & relationships for employee's database mba_110 Tables 6 03-21-2017 07:24 AM
Patient management database, Help needed for tables and relationships faisals Tables 28 01-09-2010 10:37 AM
New database - help needed with Tables and Relationships please? tonypony Theory and practice of database design 8 07-30-2009 10:33 AM
Building a database, relationships and tables from a text file?? GeekIslands Theory and practice of database design 3 12-05-2008 02:54 PM
Setting up Database - need help with relationships between tables wilkob Tables 7 09-12-2007 01:27 AM




All times are GMT -8. The time now is 09:35 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