Append Queries

[farrah]

A very happy New Year to you all.
I have set up an append query to create supplier orders from orders placed by customers. This is sort of working but it is giving every order item a new supplier order no. So if x items have been ordered from supp A, x supplier orders are produced. And ideally it should put x number of entries under one supplier order number. I hope this is clear. Please help.
Thanks
Farrah

 

[glynch]

Are you using an autonumber field for your supplier order id field? If so, you'll need a separate field that you can control the value of when you create supplier order records.

Without knowing exactly how customer and supplier records are related to each other, I can't tell you anything more about how to set up a query to create supplier order records from customer order records.

 

[farrah]

Thanks glynch for your reply. Yes I am using Autonumber for the supplier order number. The customers can place an order for more than one item. These items will then have to be ordered from outside suppliers. So for eg. customer places an order for five items, 3 of which will be orederable from supplier A and the rest from supplier B. Then as an admin task, the supplier orders will get created, so supplier A will have the 3 items (identified by a unique supplier order no.) and supplier B two items and these will be printed and sent off. Phew! Does this help?
Thanks for your help
Farrah

 

[Pat Hartman]

You will need to split the append query into two parts. It is necessary to separate the creating of an order from the appending of detail items to that order. The challenge will be to figure out which OrderId should be assigned to the order items.

 

[glynch]

You described a situation where you have a many to many relationship customer order items and supplier orders. I am guessing that one supplier order can contain multiple order items from multiple customers.

In either case I believe you need to store the data for this relationship in a separate table. The customer order table could use an autonumber for the order id if you choose, also a customer id which would relate to a customer table, maybe some date/time info as well. The supplier order table would be constructed in a similar manner.

You were trying to hold the customer order item info in the supplier order table. This can be done, but not if you use an autonumber for the supplier order id.

I would have the order item table contain a customer order id and a supplier order id. So in the example you gave me you would have 1 record in the customer order table, 5 records in the order item table, and two records in the supplier order table.

Example:

Customer Order Table:
CustOrderID - 27
CustID - 15
Date - 01/03/2001

Supplier Order Table (2 records):
SuppOrderID - 55, 59
SuppID - 123, 132
Date - 01/03/2001, 01/03/2001

Order Item Table (5 records):

OrdItemID CustOrderID SuppOrderID
1 27 55
2 27 55
3 27 55
4 27 59
5 27 59

I think I have all of my numbers right, do you see what is happening with this?

 

[glynch]

Sorry, the last post was formatted to ignore the spaces. the three columns ofm numbers should be lined up under the three column headings.

 

[farrah]

Pat and Glynch, thank u both for your responses. Yes in the end I did it in two stages, created the supplier orders with autonumbers and then used the supplier id + customer order number to form a supplier order.
Again many thanks.
Farrah