Using MS Access/Transferring a bought part from one customer to another customer
QUESTION: I need to create a database in MS Access which keeps track of machines (a machine is made up of a bundle of parts and put together in our warehouse) which is then sold on to our customers.
However some customers can then have the option to transfer the machine on to another customer. This can happen because some customers belong to a group company and can move the machine around to other customers within that group company.
At some point a customer has the option to decommission the machine and have it destroyed. This is done by transferring the machine to and authorized company which deals with this.
Examples : Scenario 1: Customer (A) buys a machine (with serial number) via a sales order and then transfers that machine to customer (B).
Scenario 2: Customer (B) can place a sales order to replace any part of that machine recently transferred by Customer (A).
When I receive the sales order from customer (B) requesting a part replacement for a previously sold machine to customer (A), how can I process the order in the database when the part had belonged to another customer?
Tables Customers - SalesOrders - SalesOrderDetails - Products
ANSWER: I would add an ownership table. Something like:
OwnershipID (PK Autonumber)
OrderID (FK to original order)
CurrentOwnerID (FK to customer list)
On the other hand, if customer B orders a replacement part for Machine X, that's just a new order. I'm not sure if you process replacement parts separately from the original machine order. But a replacement part order can reference the original order, but have new Customer info. It can default to the customer info from the original order, but that can be overwritten.
---------- FOLLOW-UP ----------
QUESTION: Hi Scott,
Thank you for the information.
I have further information and questions:
A machine consists of 10 parts, each with serial numbers. When an order for a machine is placed the machine is built up with the parts and the barcode on the parts are scanned and saved with the machine record.
When a part is required for a machine, the order is linked to the current customer and machine ID.
I have the following questions:
1. Would it be better to add 10 extra fields in the Machines table so I can scan and save the barcodes for each part?
2. Do you think that the attached relationship diagram has any issues?
3. Is there a way to select which order type (Machine or Part) on a form : eg. If a customer orders a new machine I will need to be able to update SalesOrderDetailsMachines table and if a customer orders a part I need to select the SalesOrderDetailsParts table.
ANSWER: 1. Absolutely not. What you have is called a Bill of Materials (BOM). The components of a machine should be stored in a child table, one record for each component with a foreign key to link back to the machine. I would use this:
ComponentID (PK Autonumber)
The last two fields allow you to record when a component is replaced and the OrderID of the replacement order.
2. The structure looks OK, but I would be inclined not to have separate Sales tables, but one table with a field to indicate if its a replacement part or machine order.
3. This is why I answered the way I did in 2. So you can eliminate that issue.
---------- FOLLOW-UP ----------
QUESTION: Hi Scott,
Thank you again for the information.
I would just like to clarify a few things:
The machine Id is not an auto number, it given at the time a new machine is built.
When the customer places an order for a new machine a new Ownership record would be created to link customer, sales order and machine ID.
When a machine is build a new component record would be created for every part that is required for the machine and a serial number would be scanned and saved within the component record. So if the machine has ten parts then ten component records would be required.
1. When a customer places an order for a replacement part where he still owns the machine how would I keep a history of what part has been replaced and how would I link the SalesOrder ID ?
2. If a customer places an order for a part to a machine which belonged to another customer would I just created a new Ownership record which links the customer ID, SalesOrder ID and machine ID and then set a flag that the machine has been moved?
3. Would I need the SalesOrderID field in the components table when I am building a machine?
4. Can you see any issues with the attached relationship diagram?
Whether you use Autonumbers or not is your choice. I prefer them because they perform better in joins.
Correct you would have 10 component records.
1. The new order for the replacement part should reference the original order.
2. That's the way I would do it. But it all references back to the original order.
3. Again, that's the way I would do it.
4. There should be no link between Ownership and Detail. The link should be to the original Sales Order. I would combine the Components table and the Sales Details into one table.