Need help determining the number of data tables and their relationships.

[BruceWaller]

I am trying to track project activities and establish history for a sales and manufacturing process. We are a dealer and everything we do is pretty much custom-built. We do not maintain inventory. This database will not be used to monitor inventory levels, issue purchase orders, or generate invoices. It will not be used to store contact info for customer, vendors, or employees. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
Here are the particulars:<o></o>
<o> </o>
A sales order with line items is opened when the sale is made and usually, (but not always) one internal work order is opened to capture costs for materials, labor, and vendor purchase orders. In many cases, our sales orders consist of one line item purchased from a single vendor. On the other hand, the single sales order could result in two work orders with multiple items purchased from multiple vendors.<o></o>
<o> </o>
I also need to enter labor hours broken into five categories for eight technicians against the work orders, and track sales order totals for six salesman .<o></o>
<o> </o>
When I am done I need to be able to pull up a sales order and see all the related work orders. The work order should display all the line items with associated vendors as well as the total labor hours. (Note: I do not have to correlate costs between the line items, work orders and sales orders.) <o></o>
<o> </o>
I am trying to define tables and relationships… <o></o>
<o> </o>
1. I am thinking I need a Sales Order Table with a “One to many” relationship to the Work Order Table since a sales order can have more than one work order.<o></o>
<o> </o>
2. Then I was going to link the Work Order Table in a “One to Many” relationship with a “Line Items” table that would show each item on the work order and the associated vendor.<o></o>
<o> </o>
3. Do I need a vendor’s table since we only use about twenty and we won’t be looking up contact info? <o></o>
<o> </o>
4. Do I need separate tables for the eight techs, six salesman and five labor categories? <o></o>
<o> </o>
5. Or for issues #3 and #4 can I just use the look-up wizard to create value lists in a combo-box for data entry, query, and reporting purposes?<o></o>
<o> </o>
Any help would be appreciated. Thanks for taking the time to read this. <o></o>
<o> </o>

 

[Norie]

1,2 sound fine.

3 A vendor's table might not be essential but it could be handy.

4 Not sure what you mean here.

Do you mean 3 separate tables one for salesmen, one for techs etc?

Or a table for each tech, for each of the salesmen etc?

What common fields would there be, and what other fields would be needed?

5 Again not sure what you mean, you shouldn't need them for queries, if you were to use forms for the data entry you could use SQL for the record sources.

You wouldn't really have comboboxes on a report, but if you mean a form where you select the criteria for a report then SQL again for the record souces.

I know I've maybe not been clear about 4 but I think more information is needed.

PS I realise you probably don't mean a table each but you never know.