Starting Over Database Tables and Relationships (1 Viewer)

billgyrotech

Banned
Local time
Today, 15:54
Joined
Apr 18, 2013
Messages
258
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:

Mark_

Longboard on the internet
Local time
Today, 13:54
Joined
Sep 12, 2017
Messages
2,111
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.
 

GinaWhipp

AWF VIP
Local time
Today, 16:54
Joined
Jun 21, 2011
Messages
5,901
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.
 

billgyrotech

Banned
Local time
Today, 15:54
Joined
Apr 18, 2013
Messages
258
The AFRs is for doing repairs and having parts attached to each one. There will be reports to follow that are needed.
 

billgyrotech

Banned
Local time
Today, 15:54
Joined
Apr 18, 2013
Messages
258
Please close this topic I need to do some reading on how to start a database properly. Sorry
 

plog

Banishment Pending
Local time
Today, 15:54
Joined
May 11, 2011
Messages
11,611
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.
 

GinaWhipp

AWF VIP
Local time
Today, 16:54
Joined
Jun 21, 2011
Messages
5,901
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.
 

jdraw

Super Moderator
Staff member
Local time
Today, 16:54
Joined
Jan 23, 2006
Messages
15,364
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/Tutorials/Entity_Relationship.zip
http://www.rogersaccesslibrary.com/Tutorials/ClassInformation.zip

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

Good luck.
 

Users who are viewing this thread

Top Bottom