Issue withe Relationships and form (1 Viewer)

[will1128]

Customer can also have multiple jobs. (1 to many)
I have a Jobs table and each Job can have only one location.(1 to one)
For each JobLocation they can have multiple chillers. (1 to many relationship)
I'm trying to create a form which will allow me to enter in a job, then the Job Location and all the information about the chillers at that location.
I believe I need an intermediate table between Locations and Chiller Information to do this, but I can't seem to define the relationships the way I want.
Attached a couple of screen shots of what I'm attempting to do. Please help.

 

[Steve R.]

I believe I need an intermediate table between Locations and Chiller Information to do this, but I can't seem to define the relationships the way I want.

You are on the correct tract. The easiest way to do this is to create a query, which is your intermediate table. I have Access 2007, the create tab is immediately to the right of the home tab. Go to the Query Design Icon (I don't use the wizard) and click on it. You will need to add your tables to the upper part of the query pane and drop your fields into the lower part. You can toggle between design view and data-sheet view. You will need to experiment.

 

[SteveSchapel]

Will,

I believe I need an intermediate table between Locations and Chiller Information...

Only if the Chillers can be in more than one Location. Is that the case?

There are some problems with your table structure. A Job can only be in one Location. And the Customer is defined according to the Location - is that right? But can't there be more than one Job for any given Location? I think the Relationship should be between Jobs.JobsID and Locations.Location_JobID.

It is not clear what the purpose of the Jobs.Job_ChillerID field is, but at the moment it looks wrong to me. Can you explain a bit more about what actually happens in the real world with these chillers, and how they pertain to the locations and the jobs?

 

[will1128]

No, there cannot be more than one job for any location. Although, I'm not sure I understand your question.
If my job location is at 446 W Main St Timbuktu, IA and the Job Name is Building Construction that is one job.
I suppose a Customer doesn't have to be defined by location, but I end up with redundancy because a Customer would have an address as would a JobLocation. Same information would be used twice and my databse wouldn't be normalized.
A chiller is a piece of hardware. Multiple piece of hardware are installed at one location. So I need a way to have multiple pieces of hardware connect to one job location.
Maybe providing data would help.
JobName: Building Construction
JobDate:
JobLocationNumber: 446
JobLocationStreet: Main
JobLocationCity: Timbuktu
JobLocationState:IA
ChillerID_One:
Informmation about ChillerOne.....
ChillerID_Two:
....Information about ChillerTwo
Does this help with what I'm trying to do?

 

[SteveSchapel]

Will,

Ok, thanks for the explanation.

Can I ask please for your further comments. Would you ever do a Job for any Customer at more than one Location? And do your individual pieces of hardware get deployed to more than one Job - or are these permanent onstallations? Sorry - not knowing your business, I am unsure whether the Job entails the installation of the Chillers, or whether the Chillers are used on the site for the execution of the Job, and then used again on other Jobs, and thus need to be historically tracked. Thanks.

 

[will1128]

Yes, it would be possible to do more than one job for a single customer. So a customer can have multiple jobs. JobA would be at LocationA. JobB would be at LocationB. CustomerA could have JobA and JobB.

The hardware actually can be picked up and moved, but they go with the customer. So if the job location is gone, the chiller still exists, but belongs to the customer.

So I would guess the answer to your question is the chillers are used on site for the execution of the job. So yes, they would have to be historically tracked.

I appreciate all the questions. It's helping me think how to work this out.