Multiple Tables for Different Orders

[Colin Bovet]

Hey all, I'm designing a database for a company to track their orders. This company cuts and bends pieces of stainless steel tubing, and they get several different types of orders. Some customers want a couple "random" pieces of tube cut. Others order the same thing time and time again. Still others want to order "bends," which need a CAD image associated with them. Finally, some customers will order a "product" and a "bend" at the same time, and would like them to be under one workorder.
The issue I'm having is in entering and displaying all that information well. I assign a "workorder" to each order, so that helps to track the order through the system. Right now, I have 3 tables -- one for "Parts," one for "Products," and one for "Bends." Is there some way to set Access up so that when a user searches for a certain workorder, it pulls up a report with the relevant information? Essentially it would need to be able to find out which table(s) are associated with the workorder and then change the information it displays accordingly.
I've spent about a month on this problem and haven't been able to resolve it. Is it possible, or should I re-think the way I'm doing everything??

Any help would be GREATLY appreciated!!!

[nico5038]

I would start with investigating the difference between Part, Product and Bend.
For me they are all "Items" that are listed on a client order.
As far as I understand the Bend is to be executed as a work order, but the needed invoice data will be the same, Bend name, quantity and price.
So having one "Item" table will need a Type field to indicate that a workorder is needed for that item.

Getting the idea so far ?

Nic;o)