Correct Relationships?

[nobbie1]

Quick question for anyone out there that could possibly help me

I've set up this database as a project for work. Nothing that is needing to be done to soon, just something I wanted to learn how to do.

I've made 3 tables:
tbLine (LineID, TechID, LineName)
tbLineproblems ( LineProblemsID, LineID, Date, Belts,Printers...)
tbTechs (TechID, FirstName, LastName...)

One tech can possibly, not usually work on more than one line each night. Also one line can be worked on by a number of techs.

I believe I have the relationships set up correctly.

The Tech table won't be updated frequently, only if we have to remove or add more techs. The line table would very rarely have to be updated since these lines are here to stay. Lineproblems will have entries into it each night as the techs enter in what problems encountered and solved that night.

I've attached my view of the Relationship window in a Word Document format if anyone would like to take a look at it. If need be I can zip up and attach my database from what I have figured out so far. Thanks in advance for any help

 

[Newman]

You said: A tech can work on many lines and a line can be worked by many techs. Then, it is a many-to-many relation.

In your relationship design, it is a one-to-many relation.
(A tech can work on many lines, but a line is worked by only one tech.)

With a many-to-many relation, you need to create another table, which will contain the IDs of tblTechs and tblLines, and remove the Tech ID in the tblLines.

Then you link tblProblems with the new table adding the Tech ID in tblProblems and using both IDs to link the tables.

This way, you'll be able to tell who got which problem.

You should also take a look at your Outer Joins. I just don't understand why you would need them.

 

[RichMorrison]

You wrote
<<
One tech can possibly, not usually work on more than one line each night. Also one line can be worked on by a number of techs.
>>

That's the classic "many-to-many" relationship (illegal in several states)

You need a junction table to relate Techs and LineProblems. The junction table contains 2 "key" fields; TechID and LineProblemID. You write code to add a record when a Tech is assigned to a LineProblem.

You can query and report on LineProblems to Techs and Techs to LineProblems as needed.

HTH,
RichM

 

[Newman]

Hi RichM,
You got me confused here...
Where is my mistake? How could you know who worked on which lines if there has been no problems? (nobbie1: Is it needed?)
I have to say I would have added a Date field in my jonction table.
Thanks for your help!

 

[nobbie1]

Ok, I think I know where to go on this.

Basically what I'll be reportin on is a couple things. Each night I'll want to know what tech worked on what line and what he did on that line if anything. Since it's a distribution warehouse, the lines always have problems (something is always breaking). I may want to query on a certain tech to see what line he's worked on and which repairs were most effective.

Would also like to query on all repairs done on a line for a certain time period

Newman, So are you saying I shouldn't need these outer joins for the tables and just use inner joins with my queries? Should I move my date field from the tbProblems to the junction table which contains the TechID and LineID as suggested?

Correct me if I'm wrong, but it almost sounds like I might need 2 junction tables. One to relate techs with line and one that would relate line with line problems. Or am I going off track again?

Thanks for all the help, things are starting to become clearer

 

[Newman]

The second jonction table is not needed.
I would like to know what is your tblProblems exactly.
If the KINDS of problems are always the same, you could have another table for the list of possible problems and then your tblLineProblem would be the jonction table.

The way that I see it, everything is fine. I'm just waiting for RichM to give more details on his design. It looks like there could be something missing in mine.

 

[RichMorrison]

Newman
<<
Hi RichM,
You got me confused here...
Where is my mistake? How could you know who worked on which lines if there has been no problems?
>>

I think we were replying at the same time.

I would use ProblemID and TechID in the junction table because we probably want to know which Tech worked on which Problem.

If we want to know which Tech worked on which Line, then join Tech to "junction",
"junction" to Problem,
Problem to Line based on the common LineID field.

N'est pas?

RichM

 

[Newman]

Once again, we posted at the same moment.

They may say that Mile-O and Vas are the same person, but they certainly can't say that you and I are the same person.

I guess nobbie1 now has a better idea, but I'll let you give you opinion on my last post.

Sorry nobbie1 if I gave you a bad direction.

 

[nobbie1]

Thanks a bunch guys I think I know how to get it all set up.

Yes each of the 7 lines we have all have the same category of common problems. Or sections that define the line. Basically the LineProblem table consists of having the following fields: Date, Belts, Printers, Channels, Stations, Shipping and Misc. Along with the appropriate ID Keys

So each night right now, the techs fill out a paper form with the above items and list the particulars for that section of the line.

An example of the data they'd enter would be something like this:

Station: C-1 Packstation 1 Side 2, Changed out Rodless Cylinder

I'm hoping to elminate the paper trail and make it easier to track data changes and what not

Currently at home now, but when I get into work I'll switch around my relationships and tables a bit and post what I came up with and let ya review it

Thanks again for helping me out. Been reading through my Access 2002 Bible and things are starting to make sense for this project I started

 

[RichMorrison]

Newman/nobbie,

I think it's kind of like this:

The "Line Operator" goes to some Access form and selects the Line from a combo box and the Problem from another combo box. The Operator clicks some "Add" button and a new row is created in the Problem table.

The Technician arrives at the Line area somehow and says "here I am to save the day". The Operator goes back to the Access form and finds the Problem. Then the Operator selects the Technician name from a combo box and clicks another "Add" button and a new row is created in the junction table. This means that a Technician has been assigned to the Problem.

The Technician fixes the problem and enters some comments on an Access form for the specific Problem. This would update the Problem table with some kind of solution.

RichM

 

[nobbie1]

Rich,

Not quite.

Ok, Currently the Techs are each assigned to a line each night. The line is broken down to a different sections, which are listed in my LineProblems table. If a technician does anything on that line and he/she usually does they note it down. At the end of the shift they turn in their paper reports with everything listed down that they worded on.

This is mainly for the Techs, the Line Operators don't go near the technical stuff....they just stay in their assigned areas and call out over the radio or walkie talkie if they have a problem.

The way I'm setting up the Form for the tech data entry is something like this.

I've got a Query set up to select the Technicians First and Last Name based off of his ID. So when the Form is opened for Data entry there's a pop up box that asks for their ID. This is then filled into the Form for them. Keeps things Uniform. Then I'll have them Select the line they worked on from a combo box. Then below that would be seperate Data entry fields on the form which correspond to the columns in the table Line Problems.

 

[RichMorrison]

Well.............. it sounds like the cast/roles may be not what I described. That's OK, I was just guessing.

Any way, it looks like you've got it.

RichM

 

[nobbie1]

Rich or Newman

Hopefully I was understanding you and Newman

I've zipped up the database project with I believe are the correct changes.

This is the direction I'm heading.

Once I'm satisfied the Data entry form works correctly and all data is being entered in correctly I'll whip out a few reports and call it done First version anyways. As I get deeper into using Access and VB I'll add more

Anyways thanks for helping me out.

 

[nobbie1]

Ok, forgot to check that last database I had zipped up.

This is one is my latest attempt to get my tables and the relationships right. After that I'll work with my data entry form.

If anyone could take a look at it and make sure I've set up the relationships right I'd appreciate it.

Just to give you an idea of what I'd like my database to do when finished (if I'm on the right track) is for each of the techs to enter in what they've done on the line that shift. The supervisor will want to be able to see what has been done per line, or per tech. Also they might want to see what has been done on a line in a given time period or what a tech has done in a given time period.

I'm pretty new to working with databases in Access. I'm trying to learn as I go. Would like to hear any suggestions / comments just to make sure I'm on the right track.

Thanks