Multiple rows per record?

[treva26]

I have a table of products and a table of orders

Each product requires multiple processes to complete

Can I make a query that will lookup the products in the orders table and show a list of all the processes that need to be done to complete all the products on order?

The bit I dont get is how can a query return multiple rows for each product?

 

[coyote]

Multiple Rows

Hi Treva26
when you say the products require multiple process to complete what do you mean.
The way I see it you store all these processes somewhere so all you do is query the products table and order table using either the OrderID or ProductID and you will get your answer
N.B I don't think there is a way to show multiple rows

 

[treva26]

My tables are in the attached database.

Each product requires a different number of steps to create.

So I want to make a list of all the steps (or "jobs" or "processes") required for each product on the orders list.


For example:
Product1 needs to be cut then welded then painted.
Product2 needs to be cut then painted.
Then another order for product1 needs to be cut then welded then painted.


So the Jobs query needs to give me a list like this:

ORDER___PRODUCT___STEP/PROCESS
-------------------------------------
_1______product1____cut
_1______product 1____weld
_1______product 1____paint
_2______product 2____cut
_2______product 2____paint
_3______product1____cut
_3______product 1____weld
_3______product 1____paint

 

[CraigDolphin]

A quick glance and I think you need to do some reading on a topic called 'table normalization'. You also need to learn about junction tables. Search and you shall find. What you are describing are classic many-to-many relationships.

Consider: You want to track three things (so far). 1. Orders. 2. Products. 3 Processes. Straight away, we know that we need three tables to track each of these entities.

Now, one order can include many products and a particular product might appear in many orders. This is a many to many situation which calls for a junction table.

A product can have many processes. A particular process might be required for many products. Again, we have a many to many situation which calls for a second junction table.

So we have identified at least 5 tables that you need.

tOrders
OrderID (auto, primary key)
OrderDate
etc etc

tProducts
ProductID (auto, pk)
ProductCode (text)
ProductDescription
etc etc

tOrderedProducts (this is the first junction table)
OrderedProductID (auto, pk)
OrderID (FK)
ProductID (FK)

tProcesses
ProcessID (auto, pk)
ProcessName (text)

tProductProcesses (second junction table)
ProductProcessID (auto, pk)
ProductID (FK)
ProcessID (FK)


In the relationships window of your db, you connect the pk's of each table to the fk fields in other tables with the same name.

You probably want to include other information as well. Make sure that you only put fields in the table that the information is specific to. For example, if a certain process costs X-dollars to perform, then you would want a process cost in tProcesses. If a Product costs Y dollars, then you'd put a ProductCost field in tProducts.

After you have populated your tables, then you simply add the relevant tables to the query builder, select the fields you want to see, maybe do some grouping or sorting, and away you go.