Using MS Access/Transferring a bought part from one customer to another customer
Thank you again for the information.
Further information I have just been given:
A sales order can be used to purchase one or MORE machines.
The sales order number used for the purchase of the machine can also be used to purchase a replacement part.
The customer can also decide to use a new sales order number to purchase a replacement part.
If a machine is moved to another customer then a new sales order will be given when a replacement part is needed.
Given the above scenarios:
If a customer orders say 5 machines, then five Ownership records would be created. One for each Machine record.
If the customer wants to add another machine to the same order then I just need to create another Ownership record and another machine record.
1. If the customer orders a replacement part for a machine for an existing sales order (assuming I left the Components and SalesOrderDetails tables separate ) would the MachineID come from the Ownership table via the SalesOrderID ?
2. If the customer orders a replacement part for a machine which belonged to another customer, would I just create another SalesOrder , Ownership, SalesOrderDetails and Component records ?
3. Can you see any issues with the attached ER diagram
First, Why would a customer get to choose how an order is processed by your company? Whatever sales order numbers or workflow that your company uses is part of your internal processing and not subject to what the customer wants.
Given that a sales order can cover multiple machines, but that each machine has its own serial number, then replacement parts should be referenced to the serial number of the machine its being purchased for, not the sales order. So it doesn't matter what sales order number is assigned to an order.
Yes, I agree that you would need an Ownership record for each machine.
1. When you finalize a sales order, you should be creating the Ownership record(s). Part of the Ownership record would be the MachineID. That would be your link back to the Sales order.
2. When a replacement part is ordered the customer should be required to provide the MachineID (or some means where your company can identify it). A SalesOrder would be created referenced to the MachineID. This would then require that Detail and component records be created. If the customer is not the registered owner of the machine, then a new Ownership record would need to be created.
3. I would not include Sales OrderID in tblOwnership, just MachineID. Then machineID would be in the tblSalesOrderDetails as would ComponentID. ComponentID would be blank if the order was for a machine and only filled in if the order was for a replacement part. So you would not have SalesorderDetailID in tblComponents.