Access Query IIf #error

No I don't think you need to store the customer in your product table. The relationship between a customer and a product is through the order, not directly. Unless your orders only include one product at a time, then you also have a transactions table whihc is where the Product is recorded.

So a Customer creates an Order, the CustomerID becomes a FK in the Order table. The order contains multiple items so the OrderID is a FK in the OrderDetails (transaction) table. To get a list of products a customer has ordered, you would have to use 4 tables; Customer, Order, OrderDetails, Products. The Relationships would be:

Orders.CustomerID<->Customer.CustomerID

Orders.OrderID<->OrderDetails.OrderID

OrderDetails.ProductID<->Products.ProductID

So your Product table would not have a link directly to the Customer. In the Orders table the only Customer field you need is the CustomerID as the FK. Similarly, the ProductID would be a FK in the Orderdetails table.

Wow, thanks, looks like I might need to start from scratch. That's ok with me if I am going to get it right.

So if I have a Customer table and I have a Product table. I still need to store the Customer in my Product table (I think I do) So my Product table would only be storing the ID number for the Customer? Not the Customer's name?

I'm still a bit confused If I create a form from my Product table. The Customer field wouldn't be a Combobox lookup because it isn't in my table. When I create the form do I replace the field with a Combobox as it isn't linked to the customer in the table?

Thanks again.

As George and I have been saying, lookup fields are not recommended. They are a misguided (in the opinion of most professional developers) attempt by microsoft to make Access easier. But what has happened is they confuse people who don't understand how they work. Unfortunately many Access templates use lookup fields.

For example, you believed that airbag was being stored in the ShipTypeTo field, when actually the PK value of airbag was what was being stored. As I said, lookups should be done on forms, not in tables.You do need to understand how comboboxes work, but the combobox wizard will help you do that.

In a relational database, data should not be repeated. One of the exceptions to that rule is using primary keys as foreign keys. So, when you have something like a ShipType, you create a lookup table of those values and you store the PK of the values as FKs. You would have a table like this:

So what is stored is 1 and 2 not the descriptons.

Thanks, Scottgem

I will make the changes and see how I go.

I have been using the Northwind database to learn from. Checking, I am similar to the Orders table which has the combo boxes and Select From, etc in the Row Source. Is this a bad method for learning using Northwind?

Anyway, might take me a while but with your explanation, I think I grasp the logic of what you have said. I will spend the time making some changes and let you know how I go.

I am very grateful to for you spending your time helping me. Cheers

There is no provision for creating a lookup within a query. If a query column shows as a combobox on a form, then the field is setup as a Lookup field on the table level. That it worked when you added the ShipType table reinforces what I believe the problem is. The ShipTypeTo field is setup as a lookup field on the table level. Open the table in design mode and select that field. In the General properties at the bottom will be 2 tabs, One labeled Lookup. I strongly suspect, that its set to combobox and that the Rowsource as the ID field first and the Bound column as 1. This masks what is actually stored in the table, which is the PK of the table, not the description field (as it should be). That would explain what your problem is and why you got it to work.

Lookups should be done on the form level, not on the table level.

I would not use a query for your subform. I would bind the subform to the QuoteShip table. I would then add comboboxes for the ShipType and ShipZone. For the calculated ShipCost I would use a Used Defined Function. Something like this:

Public Function fShipCost(strShipType As String) as Currency

SELECT CASE strShipType

 Case Airbag

       fShipCost = Me.QuoteCost

 Case "Semi SQM"

      fShipCost = [QuoteCost]\*[SumOfTotalSQM]

 Case Else

      fShipCost = 0

End Select

End Function

Then set the Contrource of the Shipcost control to =fShipCost(Me.ShipTypeTo)