Relationship constuffulation or wrong methodology?

[Oyseka]

Hi All, I hope you all have a great year.

I am trying to bring into a Job Card in a single record all the Service Parts that may be applied to a particular product and I don't have the faintest Idea how to do it. The tables and relationships are:

 

Installation Items=>Job Card: Installation Number

Job Card=>Service Items:Job Number and Product ID

Service Items=> Service Parts: Svc ID

Service Parts=> Product Parts:Svc ID

Service Parts=>Products2;ID

 

What is meant to happen is that when a job card is created via the Installation Items relationship, the job card is printed and passed to the service technician, the technician records on the job card against both the job number and Product ID number what parts were used on that particular service. On return to the office I then want to record in the database that information.

 

Any help appreciated

Gasman.fp7.zip

[Wim Decorte]

Do you want to track all the service items (= products) for each installation item on a job card?

Then the relationship as you have won't work.  You need a new table named something like "products_used", although it looks like the "Service_Items" table may work for that.  Remove the product ID from job cards, it is not needed there.  You probably need to add a installation ID field in service items

[Oyseka]

Thank you for your response.

 

I can't remove the product ID as that is the unique identifier for each piece of equipment and it is only by using it that the service history of a particular fire extinguisher or other pice of equipment can be tracked over it's lifetime.

[Wim Decorte]

I didn't explain it well... I meant taking out the product ID from the Job Cards table as a foreign key there.  The fact that you have a product ID there means that one  job card can only be related with one product.  And that's probably not what you want.

[Oyseka]

Sorry but I'm dumb. I've removed the Product_ID as a key field but I'm still getting garbage in the Job Card. The Svc_ID field is a repeating field with 15 repetitions, that is the maximum number of product parts that can be used on any one product. But if I enter a Svc_ID code in the Job Card it fills all the records on the job card with the same value because the relationship is now just with the Job Card number so I have no way to link the Svc_ID with a particular Product_ID

[Wim Decorte]

Svc_ID in job Card should not be a repeating field.  

 

I think there is some confusion of terms, at least for me looking in form the outside.  Are service items just line items on a job card?

[Oyseka]

That is why I don't know if the methodology is correct, I have never used a repeating field before and I was unsure if this was the correct way to utilise it.

Yes, service items are line items from the service parts. The terms used are:

Product; This is a list of the possible fire appliances that are installed and are identified by the field:  ID

Product Parts; this is a list of all the parts that could be used in a service on the Products and are identified by the field: Svc_ID

Service Parts; this breaks down which Product Parts can be used with which Product and is identified by the field: Svc_ID.

 

If you view the layout Installation_Job_Card_Print and perform a find on Inst_No L00001, that would show all the appliances that are installed at that clients location. that list would then be printed and given to the technician. When the technician arrives to service the products they go to a product and read the Product_ID number and any service parts that are used they would record in the boxes against the Product Parts legend at the bottom of the print.

So if you look at Product_ID PR0054 there may be written in the boxes 01.03,08,09 which means that the technician performed a service, did a pressure test, did a Co2 recharge and put a new safety pin in.

When the technician now returns to the office and hands in the completed job sheet I want to go to the Job Card and against Product_ID record those Service Items with the Svc_ID.

So if you now view the Job_Card layout and view Inst_No L00001 I think you will see what I am trying to do

 

Thank you for assisting

[Oyseka]

Does anyone have any ideas for this. At the moment the only way I can see of doing it is to create 15 TO's of the Service parts Table which seems fairly ludicrous.