ComboBox Returns Duplicates of Products -- I only want to see one of each

[PinkDuster]

I have a sales and inventory database (have discussed DB Design in the appropriate forum here) for which I now have two related tables: tblProducts and tblPricing. There are wholesale and retail prices for each product that are not related by a formula, so I split the original table. The order form has a subform for the order details, which includes a combobox for the sales person to select a product. However, now there are duplicate products, I think for each of the two types of pricing.

I wanted to select a product, then use the next box for PriceType to be able to select the correct price. Is there something I am missing?

[ArviLaanemets]

There are more tables, are there? As you have in your post added a picture of Orders for, there is an table tblOrders or something like this?. And as Orders form looks like it has a subform for order rows, there must be a table like tblOrderRows too. Is it? What is the structure of this table?

[isladogs]

Difficult to do more than guess without seeing your data and you've removed the screenshot so I'm double blind.

First I would suggest changing the combo row source to SELECT DISTINCT....

BTW I don't understand why you have 2 separate tables for the 2 prices.
Why not just 2 fields in the same table?

[orange]

Pinkduster,

Perhaps you could lead us through a couple of examples to show more of the context involved in your process(es).
Also you could show us a jpg of your current tables and relationships. Without such info, readers are just guessing at what you have and what you need.

[PinkDuster]

There are: tblOrders, tblOrderDetails, and tblCustomers also. The OrderDetails table includes fields for OrderID, ProductID, ProductDescription, SoldAsPrice, Qty, PriceType.

[PinkDuster]

I started with the two different prices in the same table, but couldn't make that work. It was recommended that I normalize the data by separating the different pricing into a separate table.

[PinkDuster]

Attached shows the tables and relationships.

[isladogs]

Ok you've now fixed both images.
What is the row source sql for the combo box which has duplicates?
Have you tried using SELECT DISTINCT to get unique records yet?

[ArviLaanemets]

The RowSource of combo cbbProductID must be something like:

"SELECT prod.ProductID, prod.Description FROM tblProducts AS prod 
WHERE prod.ProductUpdatedOn = (SELECT MAX(prod0.ProductUpdatedOn) FROM tblProducts AS prod0 WHERE prod0.ProductID = prod.ProductID AND prod0.LastUpdated <= " & Me.txtfkOrderID & ")"

When this query returns double set of rows, then you must have double rows in table tblProducts too - which is impossible with ProductID being a single primary key.
NB! You have to update the combos RowSource by some form event, p.e. by Orders form OnCurrent or AfterUpdate event, as you can't use a formula for SQL string as control's property.

And you don't need a field SoldAsPrice in table tblOrderDetails. With your current tables setup you can always calculate it from tblProductPrices as a latest price of selected type for given product with LastUpdated <= OrderDate

[PinkDuster]

Yes, I did, and it worked! Thank you!