Order ID help/question

[KenshiroUK]

Order ID help/question *SOLVED*

Hi all, hopefully this will make sense as I type it out. I currently have an purchase orders database which is working fine. When an order is created through my form the details are stored in 2 tables 'Orders' & Orders 'Details'. Both are linked with an Order ID number which is dynamically created because it is an auto number. Both of these tables are combined within a table called 'Process Orders'. which again works fine.

Now my problem, I have been asked to do a quick reorder. This basically takes the details from 'Processed Orders' and appends some of the details in 'Orders' and 'Orders Details'. However the Orders part has worked and I successfully created a new order id. The problem is however Orders details doe not have that new created order id because it has been appended and therefore cannot link the 2 tables together.

Is there any method I can do to get that newly created Order ID back into 'Order Details' to link the 2 tables together?

 

[jdraw]

Please tell us more about the Process Order table.
How does this fit in your business and how is it related to "quick reorder"?
How do "Process Order" and "Order" differ?

 

[KenshiroUK]

Okay the Processed Orders is combination of Orders and Orders details. The process orders also contains prices from Unit price, Discount, Charge and total price. This table is mainly just for reference to look at, it also contains quantity ordered and quantity to follow. This will be used later on for the back orders part.

So since all the details are in that table for an order, I have created 2 append queries, that append the details back into both my Orders table, which now has generated a new Order ID and into OrderDetails, which has created an OrderDetailsID (for multiple orders on the same order). But it comes back to the same problem, how do I get that newly created Order ID number into OrderDetails?

I almost forgot I have a query at the moment, that prompts the user for an Order ID, Purchase Order ID in order to pull up the correct details to reorder. It just needs to be stored back into those 2 tables.

 

[jdraw]

I fail to see the need for Process Order table. Seems you have Orders and each Order can have many Order Details.
What is the difference between Order and Purchase Order?

Here are some excellent video tutorials dealing with
Customer, Order, OrderDetails, Items.


http://www.youtube.com/watch?v=IiVq8M5DBkk Logical data modeling

http://www.youtube.com/watch?v=BGMwuOtRfqU Candidate key

http://www.youtube.com/watch?v=ZiB-BKCzS_I Normalization

http://www.youtube.com/watch?v=pJ47btpjAhA Normalization example

http://www.youtube.com/watch?v=q3Wg2fZENK0 1st Normal form

http://www.youtube.com/watch?v=vji0pfliHZI 2nd Normal form

http://www.youtube.com/watch?v=HH-QR7t-kMo 3rd Normal form

http://www.youtube.com/watch?v=q1GaaGHHAqM E_R Diagramming

Good luck.