distinct row

  • Thread starter Thread starter lane55
  • Start date Start date
L

lane55

I have a combo box and want the results displayed to be
distinct. The row source (product) contains same values
many times. This gives me duplicate values in combo box
list. What am I missing? Setupsheetinfo is table name.
Product is name of field in table that I want to use as row
source. (I am working on correcting my poor naming conventions)
This is my row source query.

SELECT DISTINCTROW SetupSheetInfo.Product FROM SetupSheetInfo
 
You want DISTINCT, not DISTINCTROW:

SELECT DISTINCT SetupSheetInfo.Product FROM SetupSheetInfo
 
Hi,


Here is how you can "simulate" the way DISTINCTROW works:


1- Add the primary key ( or a "bookmark" ) in the SELECT list of
fields for each and every tables already implied in the SELECT list (NOT
necessary from each tables implied in the FROM clause).

2- Solve the query replacing the DISTINCTROW by a standard DISTINCT

3- Remove the fields you have added in step one.



As example:


tb1
f1 f2
-----------
a 1
b 2
c 3
a 1



tb2
g1 g2
--------------
x 1
y 1
z 2



Then:


SELECT DISTINCT tb1.f1 FROM tb1 INNER JOIN tb2 ON tb1.f2=tb2.g2
----------
a
b


SELECT DISTINCTROW tb1.f1 FROM tb1 INNER JOIN tb2 ON tb1.f2=tb2.g2
----------
a
b
a


SELECT tb1.f1 FROM tb1 INNER JOIN tb2 ON tb1.f2=tb2.g2
---------
a
a
a
a
b



So, in some way, DISTINCTROW can be seen as "removing" a potential
duplication, amplification, effect that an inner join can produces because
of repeated values present in the joinned table, here, tb2. DISTINCTROW has
a side effect to allow JET to track back the ***unique*** record involved
in the supporting table.


Hoping it may help,
Vanderghast, Access MVP
 
Back
Top