Multiple references.

[Homegrownandy]

I've actually solved this problem but I don't like the way I did it. Ill try an structure this question as "theory".

The record in question is a "job" and it will link to the "contacts" table where data for all people outside the company is kept. I'm finding it very difficult to understand how one record in jobs can have two links to the same table (contacts).

The way I got around this was copying and pasting the contacts table in the ERD screen. But I highly doubt this is the correct way to do it, Its probably very simple (I hope haha).

So to clarify what I'm asking. ONE "job" has TWO "contacts". It has two fields for these contacts in the jobs and they are a requirement.

If any additional info is needed feel free to ask.

Thanks.

[davegri]

One jobs table can have any number of related records in a contacts table. A field (foreign key) in the contacts table points back to the jobs table.

[John_G]

You need to restructure the database - it is not properly normalized. Since there are multiple contacts for a job, contact information should not be in the Jobs table.

I suspect you have a many-to-many relationship - one job has multiple contacts, and one contact can be for multiple jobs.

If that is the case, you need a third table (call it JobContact) to relate the other two. The third table will contain a minimum of two fields, one a reference to a job, and the other a reference to a contact. By doing this, you can have as many contacts for a job as you like; the fact that there must be (at least?) two doesn't change the concept.

[orange]

What do your links mean in business terms (plain English)?
Is this what you are saying?
1 Job may have 1 or many Contact(s)

Job--->Contact

[Homegrownandy]

john the only information regarding the contact in the jobs table is their ID. but it will be in two different fields.

Orange the links may be different depending on what we are doing. An example may be, Who we issue a document to and then who approves it. Both these things need to be recorded and they are specific to that one job.

another example may be a site operative for that job and the engineer on site. It depends on the project.

Johns suggestion for a junction table may be the solution I need. To be honest I've not done a lot of testing after I had it working. I just couldn't visualise how 2 tables could have 2 links. I'm happy with that anyway but feel free to have more input.

but one job will have many contacts, and one contact can be on multiple jobs yes.

[ItsMe]

...
but one job will have many contacts, and one contact can be on multiple jobs yes.

Many to Many -- So junction table.

[Homegrownandy]

I'm actually using a lot of junction tables. For some reason this really confused me.

I'm actually still confused. It appears to me to be the same issue.

Ill be left with a junction table that has two fields for contacts. Maybe you someone could point me towards an article of something similar?

Maybe I'm the one making this an issue when there isn't one.

I'm almost 100% sure this is really simple but I cant get my head around it.







In the screenshot here table2 is contacts. This is the solution I currently have. Access creates a new table on the ERD to represent the other link.

Table one here is the Jobs table.... but using a junction table; table one could also represent the junction table. The result would be the same. We can forget "jobs" and "contacts" Its a theory question.

[orange]

Still not clear to me. Sounds like a procedure where
a)someone reviews something, and
b)a second someone approves.

Perhaps there are 2 things: a reviewer and an approver???

Could be many to many as others have suggested.
Tell us about a project-- what is a site operative? Role/responsibility?
And what is the engineer's role/responsibility?

Can you tell us more in a 4-5 line description of how these things interact and what the "business is"?

Here is a sample for many to many:
Consider Products and Orders where -

“Each Order contains one or more Products.” (At least one because it doesn't make sense to place an order for no products.)


“Each Product is contained in zero or more Orders.” (Zero because we might not have sold any of this product yet.)

Here OrderedProduct is the junction/linking table.



However, and depending on your requirements and details, you may have something like the following:
(note: I'm sticking with your comment about sending a document to someone and then someone else approves it)

In this diagram/model, Contact is a reference table. Values from the Contact table (individuals) play roles within the business.
So, someone serves as the Reviewer, and someone else from the Contact table serves as the Approver.
These are attributes/fields in the Document table. They are required to identify Who reviewed the Document? and Who approved the Document? The Contact table is just a reference table ---just as you might have a State/Province table to supply State/Prov names for Buyer and Seller addresses etc.

[Homegrownandy]

It will change all the time depending on the project but I can explain this one a bit better and maybe you have another idea how to achieve what I want.

I'm issuing manuals at the moment for work we have done on many sites throughout the UK. These manual are issued on the clients website to an engineer. Then they are later approved (hopefully). Sometimes a different engineer reviews the manual to the one I sent it to. For cost tracking purposes I need to keep records of who it was sent to and who approved it. Once its approved I can send this information to their accounts for payment. Issued to and approved by should be the same person. Only one person will look at these. but if that person is different to the one I sent it to it needs to be documented.


Another situation could be where a site operative is required on the day for access to a site under his control. Also we may require an engineer from the clients company to attend to witness any works we may be doing. The only interaction really with us would be minimal in most cases. But assuming something went wrong we need to have the contact details for that job of these people to hand.

We often have bad processes regarding data, but we don't dictate a lot of it. The client does. We need to adapt to the way they want to do things. The many systems we have here now is to make that process easier.

I could be wrong in assuming there's a better solution than the one I had originally. But since this is one of the main aspects of my job (unintentionally, but its a lot easier than the way this company did things before I came here.) I'm learning as much as possible and checking where I think I am going wrong or think I can do better.

That's the reason I've moved the whole database over to a new one and I'm cutting a lot out. After a year of learning and testing you notice a lot of errors ha-ha.

Also, It's been a while since I've been on here as I've been doing more VBA in excel and the database has been running itself. But it's good to see you haven't changed Orange! ha-ha.

[Homegrownandy]

I don't know why that was so difficult for me o understand. I have it now. I'm using similar solutions elsewhere... anyway.

each contact for a job has to be one record .. it will say who that contact is... what they are on THIS job only.

I can have as many roles as I want this way and don't need to adapt for project specific needs.

Got there in the end.... Any other input on this is welcome.

[Homegrownandy]

looks like ill be busy implementing this on Monday.

[orange]

I would not use names with embedded spaces. I recommend just alphanumerics and "_" (underscore) for names.
Also, I was trying to understand your requirements, and offered some thoughts based on my interpretation. Before implementing anything, mock it up (even pencil and paper) create some scenarios and test data and make sure the idea works as you need it. Then design and develop. Too many are too quick to jump into the nitty-gritty of Access and end up designing as they go.


In Access you can add the same table to a relationship window many times.
eg:
tblState
-stateId
-stateName

tblVendor
-vendorStateId

tblCustomer
-customerStateId

tblSeller
-SellerStateId

tblRepairer
-RepairerStateID

tblWitness
-WitnessStateID

all of these other tables can use the Values from tblState (as reference table). This can simplify maintenance; enforce consistent spelling etc...

Good luck with your project.

[Homegrownandy]

Thanks, I should have said; my data is named properly, this was made purely for this thread and some testing. It will take me quite a while to implement this solution to be honest. (making it works with reports, forms, whatever) But it will keep me busy!