Question about Relationship and Userform in Access Database

I have changed the product table. Now the product name and the price is repeated over and over again
What is wrong by keeping the Product table the way I made it? The name of the product could be 'Fruitbox 1' which consits of apples, pears etc.  It is also easier for me to create a form in Access.

An OrderHeader would contain information about the entire order - the Customer, Order Date, etc etc

Isn't the order header my order table?

As far as getting the last customer order - if you have history (i.e. the last order for a customer)  you could always pre-populate the Line Items in your form.

This is where I need help, I don't know how to do that.

DB_EE3.accdb

I would assume a Form in Access to handle this would be based on the Order table. You'd have a combo that would allow you to select the Customer, enter the Order Date, etc.

You'd then have a Subform based on the OrderDetail table. That table would have a combo where you'd select the Product the Customer ordered. That table would store the the OrderID, the ProductID, the Quantity and the Cost.

If you setup a form in this manner, you should see a single line with the Product information (the name of the Product, like FruitBox1). You should NOT see the items which make up FruitBox1. You could add a popup form, or something like that, to show the user what is included in FruitBox1.

To get the most recent order, you'd need to query the Orders table for the most recent OrderDate by the Customer:

SELECT TOP 1 * FROM Orders WHERE CustomerID=YourCustomerID ORDER BY ORderDate DESC

From there, you could then determine which item was order (or items), and then add records to the OrderDetail table to recreate that order.

That's exactly how I built my form. The form is the order table. As the order table contains the customer ID, I use a drop down or listbox so that the user sees the names. The subform is the order detail. That's exactly how I did it. (see access db)

Thanks for the query. But how do I put the previous data into the order form?
DB_EE3.accdb

The OrderDetail table needs a quantity field in case the customer wants to purchase more than one item.  That table also should include the UnitPrice.  This is not a duplication of data.  The reason for storing price in the order itself is that price changes over time and if you need to look at old orders and produce historic reports, you need to use the price that was valid at the point in time the product was sold rather than the price of the item today.  

That makes sense. However I still need to put the per-unit price somewhere as my boss doesn't  wants to set the price every time again. Would it make sense to copy the product price to the order detail table?  Here now my changes:

The UnitPrice would be stored in your Products table. That would be the "default" price. You'd fill that into the OrderDetail table when the user selects that PRoduct. They could overwrite it if needed. As Pat mentioned, it would be stored with that OrderDetail, since it becomes a static piece of data after the Order is saved.

I'm not sure you need a CustomerAddresses table, unless an Address can be owned by more than one Customer. If not, then just relate the record in Address directly with a Customer, and move the AddressType column from Customer_Addresses to Addresses.

Your Orders table needs more detail (like OrderDate), but I'm sure you are aware of that.

I'm sorry you were confused about my statement regarding unit price.  It needs to go in two places.  The product table where it is used as the default price and then in the order detail where it is the selling price.  This allows you to override the selling price if necessary as well as keep accurate historical information.

If you are processing thousands of orders per week, it makes sense to isolate the address into its own table so that you don't have the duplication when billing and shipping addresses are the same.  This didn't sound like that kind of application which is why no one recommended it.  Separating the address is not wrong and it is technically more correct.  However, its a little more complicated since you would also need to have non-customers in the customer table so that you would have a customer to link the address to.  An address isn't useful in isolation.  It has to belong to some entity.  Designing a normalized schema is part science, part experience.  Experience tells Scott and me that a separate address table is taking normalization further than you need to.  Nothing bad will happen if you duplicate the billing/shipping address.  And in fact, using the same logic I applied to the unit price, storing the shipping address gives you a historical perspective if you need it since addresses also change over time and just because a billing address changed doesn't mean that you would have to seek out all old orders to change the shipping address.  The order shipped to the address in the order header and it doesn't matter that the customer's current address is different.  So, storing the billing address in the customer table and the shipping address in the order header is not a violation of normal forms.

The simplest way to fill the unit price (and shipping address) is to include the columns in the appropriate combo.    So the rowSource for the product would be:
Select ProductID, ProductName, Price From Products Order By ProductName;

You can show or hide Price depending on your preference.  I generally show it because if the order is being taken interactively, the order taker can mention it before selecting it.  So the column widths would be 0;2;1

In the AfterUpdate event of the ProductID combo, copy the price to the UnitPrice:
Me.txtUnitProce = Me.cboProductID.Column(2)

Notice that the column reference is to column 2.  That is because the columns are a 0 based array.  So, column 0 is ProductID, column 1 is ProductName and column 2 is Price.

I agree with Pat regarding storing the Address information directly in the OrderHeader. In almost every case, this is static/historical data and should be stored with it's parent item (the OrderHeader, in this case).

I provide custom support for a very large ERP product, and the designers of that database have a Customer >> Address structure much like you have, and you end up with a LOT of irrelevant information since they stored the AddressID in the OrderHeader, instead of the actual Address data. If their Customer changes a shipping address, they must add a NEW Address record, and show/hide the old one as needed. Even for mistypes (like entering 123 Main instead of 132 Main), if that address has been related to ANY record, the ERP solution does not allow you to modify the existing record, and instead prompts you to add a NEW address record. It's a properly normalized structure, as Pat said, but it also cause a LOT of troubles in production. Also, the ERP solution provides for drop ships, and every drop ship address is stored in that address table, which creates a big mess when you must provide the user with custom Address lists.

I realize that's not an issue for you, but rather an example of why it's not a bad idea (or a "denormalized" idea) for storing the actual Address info in the Shipping table.

We are anticipating around 50 orders a week, so it won't be a big database. I was initially asked to do this in Excel and that's why I incorrectly called Access forms 'userforms'.

There is one thing which I don't understand. For the order form, do you make use of subforms or do you put everything on one form, each control with data from different sources? I assumed that the order_detail should be a subform of orders, but it looks like that's not the way to do it.  How would I add more than one product to the current order?

DB_EE3.accdb

Use a subform for the details.  Access comes with a built in order entry example app.  It isn't very good by professional standards  but it should give you an idea of what you need and how the forms will work.

If you are using A2016,
Open Access
Choose Database at the top near Suggested searches for templates.  You should see Northwind 2007 sample
Choose it and Access will create a database for you from the template.
You can do anything you want to it and if you break it, just start again from the template.

If you are using A2013, the steps might be the same.  The others are simply too far back for me to remember how to get it.

Right, so what I now need are the previous order's products from the order_detail table.
I used the following query to get the previous products and I saved it as 'qyrGetLastProducts'

PARAMETERS CustomerID Short;
SELECT Order_Detail.Product_ID, Order_Detail.Quantity, Order_Detail.Price
FROM Orders INNER JOIN Order_Detail ON Orders.OrderID = Order_Detail.Order_ID
GROUP BY Order_Detail.Product_ID, Order_Detail.Quantity, Order_Detail.Price, Orders.Customer_ID
HAVING Orders.Customer_ID=[CustomerID];

Select allOpen in new window

Here comes the point where I really need help - and the reason I put my question hereon Experts-Exchange:.
How do I create an insert query and put the previous products into the subform (order_detail) when the user clicks on the button 'get previous products'?
 DB_EE3.accdb

I got a bit closer to my goal with the following query:

PARAMETERS [@customer_id] LONG, [@order_id] LONG; 

INSERT INTO order_detail 
            (order_id, 
             product_id, 
             quantity, 
             price) 
SELECT [@order_id] AS Expr1, 
       od.product_id, 
       od.quantity, 
       p.price 
FROM   ((SELECT TOP 1 orderid 
        FROM   orders 
        WHERE  customer_id = [@customer_id] 
        ORDER  BY orderdate DESC) AS q 
        INNER JOIN order_detail AS od 
                ON q.orderid = od.order_id) 
       INNER JOIN products AS p 
               ON od.product_id = p.productid; 

Select allOpen in new window

The problem I have with this query is that too many orders will be added to the table:
Customer 1 has 12 orders - the query will insert 24.

I hope I'm on the right track.

You need both the old order id (previous order) and the new order ID (the one you are trying to add) as arguments.

SELECT [@order_id] AS Expr1,

This should be [@order_IDNEW] as Expr1,

INSERT INTO order_detail 
            (order_id, 
             product_id, 
             quantity, 
             price) 
SELECT [@order_id]      AS NewOrderID, 
       od.product_id, 
       Sum(od.quantity) AS quantity, 
       p.price 
FROM   ((SELECT TOP 1 orderid 
        FROM   orders 
        WHERE  customer_id = [@customer_id] 
               AND orderid <> [@order_id] 
        ORDER  BY orderdate DESC, 
                  orderid DESC) AS q 
        INNER JOIN order_detail AS od 
                ON q.orderid = od.order_id) 
       INNER JOIN products AS p 
               ON od.product_id = p.productid 
GROUP  BY od.product_id, 
          p.price; 

Select allOpen in new window

Hello Pat

You are right. After a lot of time I finally got it working.
As you mentioned, I needed the new order ID for this to work.

Thanks for your help

I eventually got it working thanks to your help.

You're welcome