Database Structure Help

[denileigh]

Hello all,

I have a database that we've been working in for years. We have a purchase order table (form, etc) as well as a work order table (forms, etc) for all the jobs in house.

I've been asked to set it up so that up to 10 work orders can be linked to each purchase order.

I went into the Purchase Order table and added WO1-WO10 fields and put combo boxes on the Purchase Order form to select from. It's working fine.

My question is how do I link that back from the work order when there are 10 possible fields it could match in the purchase order table and how would I create the corresponding queries and reports?

Was that not the best way to structure that?? All help and/or ideas as how to better handle that request is greatly appreciated.

Thanks,
Dianne

 

[plog]

No that's not the best way. When you feel the need to numerate fields (W01, W02, W03, etc.), its time for a new table.

All that data needs to go in its own table. I'd have to see your full structure for specific advice, but that's how you accomplish this issue.

 

[CJ_London]

unfortunately you have set it up incorrectly.

Rather than adding 10 w/order ID fields to the purchase order, you should add one purchase orderID to the w/order.

You would need some code to limit the number allowed to 10 if required

 

[denileigh]

Thanks for your input however, after further questioning I've been told that there can be multiple work orders per purchase order and multiple purchase orders per work order. NOW WHAT?

 

[plog]

A junction table (http://en.wikipedia.org/wiki/Junction_table). It is used to define entities that are in a many-many relationship. It would be comprised of at least 2 fields--WorkOrderID (which would be foreign key to Work Order table) and PurchaseOrderID (which would be foreign key to Purchase Order table).

 

[gemma-the-husky]

you need to question further.

why does a work order link to a PO at all?

normally a work order draws from stock/inventory, and maybe cause a PO to be raised if there is a stock out/stock re-order level. This is not quite the same as having the PO linked directly to the WO. Maybe some products are ordered directly for works orders.

So you may have a situation were works orders are fulfilled in 2 ways - either by drawing from stock and/or specific orders for the specific work order.

Don't dash into this. This is exactly the sort of situation that normally requires a specification to be drawn up, and signed off.

otherwise users take things for granted, you spend a lot of time developing something, and then you get the "We thought you knew about that ", and "How do we do that" for things that were never properly explained, requiring a lot of difficult re-engineering.