Make Subform Display Only Unique Value

[calvinle]

Hi,

I am trouble finding way to make my subform to display only unique value, even the Index is set to Duplicate Ok.

I have a list of client that can have more several product. The product for each client can be the same. For each of the product, I need to specify the quantity, the price and total ordered.

I have 2 tables:
tblClient
tblProduct

The Relationship is from tblClient [client_id] (one) to tblProduct [client_id](many).

On my main form, I want to show only the available product in 1 subform, then on another subform, to show the product, quantity, price, total.

frmMain
sfProd
sfOrder

Here is the data sample:
tblClient:
Microsoft

tblProd:
Microsoft - Hardrive - 1 - 3.00$ - 3.00$
Microsoft - Adapter - 2 - 2.00$ - 4.00$
Microsoft - Hardrive - 3 - 3.00$ - 9.00$

In the first subform, I set to continuous, then only display the product.
In the second subform, I set to continuous, then display product, quantity, price, and total.

If I add more order in the second subform, it will generate also the product in the first subform (sfProd). How can I make the subform (sfProd) to only show unique record regardless the number of same product entered in the subform 2 (sfOrder)?

Thanks.

 

[JHB]

Set the recordsource for the first subform to a query which only return distinct values.
The link shows how.
https://www.fmsinc.com/microsoftaccess/query/distinct_vs_distinctrow/unique_values_records.asp