whats wrong with this Distinct

I am reworking a DB to simplify maintenance for salesman if/when I leave this company. The intent is to consolidate three tables into one flat file (which will work just fine).

However, I still need to be able use three cascading combo boxes to filter the search.

My first combo box (cboBrand), has this code as RowSource SELECT DISTINCT qryPurchaseInfoNew.Make FROM qryPurchaseInfoNew;

The second combo box will filter the results of the first, but it's not showing the distinct values and I cannot figure out why. Here is the code:
SELECT DISTINCT qryPurchaseInfoNew.ID, qryPurchaseInfoNew.TracModel, qryPurchaseInfoNew.Make FROM qryPurchaseInfoNew WHERE ((qryPurchaseInfoNew.Make)=[Forms]![frmTractorPriceToolNew]![cboBrand]) ORDER BY qryPurchaseInfoNew.TracModel;

dmyoungsal said:

...but it's not showing the distinct values...

Click to expand...

What does that mean exactly, "not showing the distinct values," does that mean it is showing duplicates, or that no values are showing, or something else?
Mark

You are including the ID field which by definition will be different for each record... though it's probably hidden in your combo.

MarkK said:

What does that mean exactly, "not showing the distinct values," does that mean it is showing duplicates, or that no values are showing, or something else?
Mark

Click to expand...

Some of the models say 6230, will show three times. I want the combo boxt show 6230 only one time.

ridders said:

You are including the ID field which by definition will be different for each record... though it's probably hidden in your combo.

Click to expand...

From another forum, it was suggested that I only use
SELECT DISTINCT qryPurchaseInfoNew.TracModel. When I do that, nothing appears in the combo box.

If I remove ID from the Select Statement, then all i see is the Brand (John Deere) repeated upto 30 times. Which is from "qryPurchaseInfoNew.Make"

Yes, the ID field will be different for each record. I may have a model (say, 6230) that are all different. once I can select the correct model using the second combo, a third combo box will allow the user to select to actual and correct model my people need to look at.

dmyoungsal said:

From another forum, it was suggested that I only use
SELECT DISTINCT qryPurchaseInfoNew.TracModel. When I do that, nothing appears in the combo box.

If I remove ID from the Select Statement, then all i see is the Brand (John Deere) repeated upto 30 times. Which is from "qryPurchaseInfoNew.Make"

Yes, the ID field will be different for each record. I may have a model (say, 6230) that are all different. once I can select the correct model using the second combo, a third combo box will allow the user to select to actual and correct model my people need to look at.

Click to expand...

First of all, if you cross post, please state you have done so and provide the link.

In order to advise further, please show screenshots of the query design and results.


EDIT When you tried the single field, TracModel, as suggested by ranman in the other forum, did you ensure that the column width was greater than zero. If not, the field will be hidden

I would build these queries and look at them in the query viewer. As you describe the problem it sounds like a formatting issue on the combo for why you are not seeing any tracmodels when you do a distinct on the just tracmodel.
Take a look at
Column Count:
Column widths:
bound column:

I am going to guess in column width there is something like :0";1" where the first column is 0 width. So you are returning the distinct values, but they are not visible.