Need help thinking this one out... (1 Viewer)

[mdcory]

Ok, I hope I can explain this so everyone can understand what I need. I know all the relationship stuff, linking and tying the records together.

I need a way to keep track of parts that go into machines, these parts can be changed frequently and interchanged from machine to machine, and I need to know what part is in it at the time and where parts have been.

So, each machine has a serial number as well as all the parts that could be installed into those machines. That is the simple part. But where I am getting confused is what is the best way to keep track of the part. As in where is the part right now (is it in a machine, on the shelf or being repaired).

Also, I would like to be able to have it so when the tech gets it on his bench for repair he can enter the SN and find what machne it was in before it was taken out and replaced with another part.

I want to be able to pull up a form with the machines info, what part SN's are in it and what part SN's have been in the machine. And the other way, pull up a form that shows a particular kind of part and which machines they are in.

Should I make it so each record is timestamped and when I pull up a machines form it only shows the most recent record of the parts in that machine. There is only 4 or 5 common parts that are changed, each kind of part having their own table.

I have sat a pondered this for a while and just can't come up with a great way to do this. I had it so when a part was replaced and it was entered in the form the old part was "tagged" as old with an append query and the machine form showed the current part in the machine by showing the record that wasn't "tagged". I am wanting to be able to use a PDA with a barcode scanner to get SN's, scan the machine SN and then the new part, then sometime during the day syncing them. So I need to keep it simple.

Whoa, I hope you are all still awake and followed me on that. Thanks in advance.

Matthew

 

[grnzbra]

You are looking at a many to many relationship that would have a parts table, a machine table and a partslocation table which would have the foreign keys for both parts and machines, a timestamp and any other info about the combination of part and machine. If the part can be taken out of the machine and moved back into inventory, you would need some kind if indicator to specify if the record is an install or a removal. There would be a record for each part/machine combination or two if you are indicating both instal and removal. (Or perhaps not if you have inventory as a location such that a removal from a machine to inventory would be regarded as an install into inventory)

 

[The_Doc_Man]

First and foremost, divide this problem up into parts.

If you want to know not only where the part IS but where it WAS, you will have a larger set of tables. But having worked with maintenance histories, I understand that need.

Basically, here is your issue. You need an "anchor" for things. If I recall the previous experience correctly, you will need a "primary machine" table with either the machine's S/N or an autonumber as the table's prime key. Put in a date for when the machine was commissioned. Have a date for "decommissioned" in case it breaks irreparably. It will need a status code that says "usable" or "dtad" - (deader than a doornail). Use the "dead" status when you can no longer repair it but still need part histories that can show they were used in that machine.

Next, you will need a parts table that has the part S/N and whatever descriptives you need. Show the date where that part arrived. If the part dies to the point of being irreparable, include a status code - like for machines - that says "OK" or "DTAD" again.

Now build a junction table with a starting and ending time stamp, and a foreign key that points to a machine record and a foreign key that points to a parts record.

To decide that a part is in a given machine, set its starting time stamp to whenever it was installed. Set the ending time stamp to some impossible date in the future. To decide that the parts is on a shelf, reset its ending time stamp to the time it went on the shelf. You might also what a "current status" code that allows you to say "in use" "on shelf" "at repair station" or whatever.

OK, part history: Report based on the history table. Group by part number. Header shows when part was purchased/first put into stock/inventory. Within part number sort junction table by start date. Detail entry will show "arrived" "put in machine X" "repaired" "put in machine Y" etc. Outer join to machine table across machine's FK would produce continuous records from arrival to demise. It is an OUTER join because there is no machine to which it was associated for some of the time.

Machine history: Report based on the history table. Group by MACHINE number, sort junction table by start date. HEADER will show machine's date of being commissioned/decommissioned. Detail entry will show the progression of parts brought in/taken out for that machine. Part's FK will lead to the part number.

 

[mdcory]

Thanks for the help, I am going to start in heavy on this, this week. I'll probably have more questions. Thanks again...
Matthew