Table Relationships newbie needs help

[Kirk Thomas]

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.

 

[johnherman]

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

 

[MajP]

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

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.

 

[Kirk Thomas]

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.

 

[Kirk Thomas]

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

 

[MajP]

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?

 

[Kirk Thomas]

Would be a single machine per call, and a single tech per call.

 

[dhookom]

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

 

[johnherman]

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