INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Table Relationships newbie needs help

Table Relationships newbie needs help

(OP)
Hello,
I am pretty new to access 2016, and I am trying to set up a database for maintenance workers to track the trouble calls they answer on machines in our department. I have set up the tables with what I believe is the relevant information, and done some relationships between them. I was hoping that some of you geniuses could look at the relationship map I made and tell me if you believe it is correct.
I thank you for your time and patience.

RE: Table Relationships newbie needs help

I don't understand the need for MACHINES and MACHINES_1. And they have different relationships, 1:M and M:1. I believe it should be one machine per log entry, thus MACHINES_1 has the proper relationship. You can move PLANT_FLOOR to work off that.

Thus each CALL_LOG is for a single MACHINES, but each MACHINES (actually MACHINES_1 on your diagram) can have multiple CALL_LOG records. And each CALL_LOG is for a single TECHNICIANS, but each TECHNICIANS can have multiple CALL_LOG records.

Finally, each MACHINES can only have one PLANT_FLOOR, but each PLANT_FLOOR can have multiple MACHINES.

==================================
advanced cognitive capabilities and other marketing buzzwords explained with sarcastic simplicity


RE: Table Relationships newbie needs help

Hard to say without knowing your business model, but I cannot understand the relationship between callID and machines and machineID and machines_1. I can pretty much guarantee that is not correct, but not sure what you are trying to do. You have two tables with both the same primary and foreign keys within them.

I am guessing here that this is a one to one; a call log is open for a single machine. Or a many to many; a call log can effect many machines and many call logs could be against the same machines?
If one call log is for one and only one machine, then the call log table should have the Machine_ID foreign key. You open a call log and associate a machine. So call log ID is not in the machine table. Get rid of the top machine table.

If however you open a call log for multiple machines, it gets a little more complicated because you would end up with a many to many relationship. Example.
Call log 1 is for machines A,B,C
Call log 2 is for machines A,D
Call log 3 is for machines B,C

You then have to make a linking table. That would store date like

CODE -->

CallLogID MachineID
1         A
1         B
1         C
2         A
2         D
3         B
3         C 


Same issue for technicians. My guess that is a many to many. I would think you do not dispatch a single technician for a call. So you would need a link table for CallLogID TechnicianID.

RE: Table Relationships newbie needs help

(OP)
Ok, so there should be one call log record for each maintenance call.
The plant floor is the area of the plant where the machines are located.
The machines are multiple machines of different types, but there can be multiple machines of the same manufacturer.
What I am trying to set up is a tracking system to track what machine was worked on, by who(technician), when, what the complaint was, what they did to fix it.
So that any valuable data for troubleshooting in the future can be captured. And see trends on machines etc.
Right now the guys are just sending out emails trying to capture what transpired for maintenance coverage during their shift, and the data is getting lost. And is not easily referenced later.
Any advice is GREATLY appreciated. And once again I thank you for your time and patience.

RE: Table Relationships newbie needs help

(OP)
So here is the change I made. Still don't know what queries I will run yet.

RE: Table Relationships newbie needs help

But the question are
Is a call log associated to a single machine? Or a call can be about several machines.
Is a call log associated to a single technician? Or many technicians go on a call?

RE: Table Relationships newbie needs help

(OP)
Would be a single machine per call, and a single tech per call.

RE: Table Relationships newbie needs help

I would remove the CallID from the Machines table.

Also, it looks like you might be storing multiple [parts used] in the [Call Log] table. If you want multiple parts, you should create a related table that links calls to parts.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: Table Relationships newbie needs help

Agree with Duane about removing CallID from MACHINES table. Otherwise, it seems to be OK based on what I think you're trying to do.

==================================
advanced cognitive capabilities and other marketing buzzwords explained with sarcastic simplicity


Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close