combo box select distinct doesn't work duplicates occur

[newdatabaser via AccessMonster.com]

I am using the command below in the row source to list entries in a combo box,
but I still receive duplicates in the drop down box. The form is bound to a
table, but the combo box unbound. The data base has one table.

row source type = Table/Query
column count = 2
bound column = 1

SELECT DISTINCT [GRI Projects].ID, [GRI Projects].Location FROM [GRI Projects]
ORDER BY Location;

what am i doing wrong?

 

[Douglas J. Steele]

What's your definition of duplicate? For the SQL below, you might see the
same ID number multiple times if it's associated with different locations,
or the same location multiple times if it's associated with different IDs.
You shouldn't be getting the same ID and Location repeated.

 

[newdatabaser via AccessMonster.com]

Different IDs. Below are 3 sample records:

ID Location
--- ------------
1 East
2 East
3 East

I see all three in my combo box.

What's your definition of duplicate? For the SQL below, you might see the
same ID number multiple times if it's associated with different locations,
or the same location multiple times if it's associated with different IDs.
You shouldn't be getting the same ID and Location repeated.

I am using the command below in the row source to list entries in a combo
box,

[quoted text clipped - 11 lines]

what am i doing wrong?

 

[John Vinson]

Different IDs. Below are 3 sample records:

ID Location
--- ------------
1 East
2 East
3 East

I see all three in my combo box.

That's exactly what you're asking for: all DISTINCT combinations of ID
and Location. The combination {1,East} is distinct from the
combination {2,East}.

What do you WANT to see? just East? If so, what ID do you want
selected? Is ID the bound column, or Location?

John W. Vinson[MVP]

 

[newdatabaser via AccessMonster.com]

Thanks for your patience. I hope this is the info you wanted. I'm not
positive which column goes with which field. I just counted the columns from
left to right.

column count = 2
bound column = 1

column 1 column2
------------ -------------
ID Location
------------ -------------
1 East
2 East
3 East
4 West
5 West

I would like the combo box to show as follows:
East
West

Different IDs. Below are 3 sample records:

[quoted text clipped - 5 lines]

I see all three in my combo box.

That's exactly what you're asking for: all DISTINCT combinations of ID
and Location. The combination {1,East} is distinct from the
combination {2,East}.

What do you WANT to see? just East? If so, what ID do you want
selected? Is ID the bound column, or Location?

John W. Vinson[MVP]

 

[John Vinson]

Thanks for your patience. I hope this is the info you wanted. I'm not
positive which column goes with which field. I just counted the columns from
left to right.

column count = 2
bound column = 1

column 1 column2
------------ -------------
ID Location
------------ -------------
1 East
2 East
3 East
4 West
5 West

I would like the combo box to show as follows:
East
West

But you say the bound column is 1.

That means that the Combo Box will reflect - or store - a numeric
value (one that you don't see, the ColumnWidths property must have the
width of the first column set to 0).

There are five numeric values in this table.

What numeric value do you want to store if you select East?

If the table field contains the number value 1, you want to see East.
If it contains the number value 2, what do you want to see? If the
combo box only has two records, one for East and one for West, then it
can only accept two numbers - one for East, a different one for West.


John W. Vinson[MVP]

 

[newdatabaser via AccessMonster.com]

I'm not quite understanding. Please stay with me. I may have been vague on
my goal.
I have a table with a field called "Location". Mutiple Records within this
table may have the
same "Location", ie more than one record has East in "Location.

In the form I created I have a combo box called "Location". When the user
selects
the drop down arrow, I want the combo box to show all of the "Locations" that
have been
Previously stored, one time only, ie only show East, West one time. When
they select
the "Location", for example East, and then hit the OK button. All records
that have East
as the location displayed.

Also, from your response, I thought that maybe I should have bound the box to
column 2. It still
showed multiple location

 

[John Vinson]

I'm not quite understanding. Please stay with me. I may have been vague on
my goal.
I have a table with a field called "Location". Mutiple Records within this
table may have the
same "Location", ie more than one record has East in "Location.

In the form I created I have a combo box called "Location". When the user
selects
the drop down arrow, I want the combo box to show all of the "Locations" that
have been
Previously stored, one time only, ie only show East, West one time. When
they select
the "Location", for example East, and then hit the OK button. All records
that have East
as the location displayed.

Also, from your response, I thought that maybe I should have bound the box to
column 2. It still
showed multiple location

The important question is: What is stored in your Table for the
location? What is the datatype of this field? Is it a Lookup Field?

I suspect that the table does NOT contain "east" or "west" or any
other text string, but instead contains a number. I would also guess
that (however this might have come to pass) your table has multiple
different values for East - some records have 1 (meaning East), some
records have 3 (also meaning East), some have 4 (yet again meaning
East).

Do you have a Locations table? What is the RowSource property of the
combo box? If it's a query could you post the SQL of the query? What
is its Control Source, and what is the datatype of that field?

John W. Vinson[MVP]

 

[newdatabaser via AccessMonster.com]

Thanks for the response. The data type for the field "location" is text .
Yes text like "East" or "West" is stored in the field "location".

Examples of records in "GRI Projects" table (this is the only table in the
database):
ID Location Project
--- ------------ ---------------------------------------
1 East Admissions
2 East ER Procedure Improvement
3 West Blood Test wait time
4 West IV tube inventory

Combo box properties you asked for are as follows:
Row source type= Table/Query
Row source= SELECT DISTINCT [GRI Projects].ID, [GRI Projects].Location_
FROM [GRI Projects] ORDER BY Location;
Control source: none (it's unbound)
Name: Location

I'd like the combo box to show:
East
West

If I select East and hit OK I display in another form all records that
have East as the Location. In that form users can edit records.
The search works ok. Above I only show 4 records there are many
more. The drop down might show 20 Easts and 30 Wests. Users
have to scroll a lot to find the Location they want. thanks again

 

[John Vinson]

Combo box properties you asked for are as follows:
Row source type= Table/Query
Row source= SELECT DISTINCT [GRI Projects].ID, [GRI Projects].Location_
FROM [GRI Projects] ORDER BY Location;
Control source: none (it's unbound)
Name: Location

I'd like the combo box to show:
East
West

If I select East and hit OK I display in another form all records that
have East as the Location. In that form users can edit records.
The search works ok. Above I only show 4 records there are many
more. The drop down might show 20 Easts and 30 Wests. Users
have to scroll a lot to find the Location they want. thanks again

Well... if you don't want your combo box to contain the ID, don't
include the ID in the query.

Chante the Row Source to

SELECT DISTINCT [GRI PRojects].[Location] FROM [GRI Projects] ORDER BY
Location];

Change the Column Count to 1, and the Bound Column should also be 1.

Obviously if the combo box is unbound, the EAST or WEST that you
select will not be stored anywhere and (unless you have VBA code
somewhere to do so) nothing will be done with it. I presume that you
have such code in the OK button's click event.

John W. Vinson[MVP]

 

[newdatabaser via AccessMonster.com]

Right. I missed the ID being called out in the row source. I used the
wizard to great it. I changed the Row Source code, column count
and bound column as you suggested and the . Now there is
nothing in the drop down list of the combo box. There are plenty of
records that have text in the Location field. Any other suggestions?

Yes I use the OK button to search the GRI table for records.

Well... if you don't want your combo box to contain the ID, don't
include the ID in the query.

Chante the Row Source to

SELECT DISTINCT [GRI PRojects].[Location] FROM [GRI Projects] ORDER BY
Location];

Change the Column Count to 1, and the Bound Column should also be 1.

Obviously if the combo box is unbound, the EAST or WEST that you
select will not be stored anywhere and (unless you have VBA code
somewhere to do so) nothing will be done with it. I presume that you
have such code in the OK button's click event.

John W. Vinson[MVP]