How to select & display distinct values

[Guest]

Hello,

I need help with a form please.

My form has two controls, the first being a combo box. This box should
select the company name from a lookup table. The lookup table has only two
companies in it, however, they each are repeated several time. Company 1 has
about 10 rows, company 2 has 3 rows. I used the query builder and came up
with the statement:

"SELECT DISTINCT Lktbl_Role_Master.CompanyName
FROM Lktbl_Role_Master;"

When I run the statement directly from query view, it executes perfectly and
returns exactly two rows (one for each company, in a datasheet view) however,
when I put the Select statement into the rowsource property for the control,
the combobox does not populate and nothing is displayed on the form.

Table Definition:
ID Autonumber primary key
Company Name text
Role Name text

Can anyone help me understand what I am doing wrong?

TIA,
Rich

 

[Bob Quintal]

Hello,

I need help with a form please.

My form has two controls, the first being a combo box. This
box should select the company name from a lookup table. The
lookup table has only two companies in it, however, they each
are repeated several time. Company 1 has about 10 rows,
company 2 has 3 rows. I used the query builder and came up
with the statement:

"SELECT DISTINCT Lktbl_Role_Master.CompanyName
FROM Lktbl_Role_Master;"

When I run the statement directly from query view, it executes
perfectly and returns exactly two rows (one for each company,
in a datasheet view) however, when I put the Select statement
into the rowsource property for the control, the combobox does
not populate and nothing is displayed on the form.

Table Definition:
ID Autonumber primary key
Company Name text
Role Name text

Can anyone help me understand what I am doing wrong?

TIA,
Rich

Check the column count and column widths property of the
combobox, it may be set to hide the first column,

 

[Guest]

Your tables need redesigning. You should not have repeating company name in
this table.

Try saving this as a query and putting the query name in the rowsource

Dorian.

 

[OldPro]

Hello,

I need help with a form please.

My form has two controls, the first being a combo box. This box should
select the company name from a lookup table. The lookup table has only two
companies in it, however, they each are repeated several time. Company 1 has
about 10 rows, company 2 has 3 rows. I used the query builder and came up
with the statement:

"SELECT DISTINCT Lktbl_Role_Master.CompanyName
FROM Lktbl_Role_Master;"

When I run the statement directly from query view, it executes perfectly and
returns exactly two rows (one for each company, in a datasheet view) however,
when I put the Select statement into the rowsource property for the control,
the combobox does not populate and nothing is displayed on the form.

Table Definition:
ID Autonumber primary key
Company Name text
Role Name text

Can anyone help me understand what I am doing wrong?

TIA,
Rich

I don't see a problem. Does it work (sort of) without the "Distinct"?

 

[Guest]

Hello,

Not sure what I did to resolve this, but now it's working.

I kept re-creating the form using the form wizard and setting the
properties. Right now, the cbo properties are:
Row Source Type: Table/Query
Row Source: SELECT DISTINCT Lktbl_Role_Master.CompanyName FROM
Lktbl_Role_Master;
Bound Column: 1 (Table has total of 3 columns, with 1st being ID, which is
autonumber for the PK)
Column count: 1

Dorian,
I believe I understand why you say the table needs a re-design. I think I'm
violating 1st normal form.

1NF: Eliminate duplicative columns from the same table.
Create separate tables for each group of related data and identify each row
with a unique column or set of columns (the primary key).
http://http://databases.about.com/od/specificproducts/a/normalization.htm

This, I think would lead to a new table (lktbl_Company) (which I do have).
This table would look something like:
CompanyID Autonumber (this becomes the PK)
CompanyName Text

This table would then have 1 row for each unique company.
CompanyName would be a FK pointing to Lktbl_Role_Master.CompanyName. Am I
on the right trail with my thinking? I had it like this previously but
deleted the relationships because I have another form with the same problem
and I thought I may have some kind of vicious cirle of relationships because
it would not work.

Anyway, this problem looks resolved but can either of you recommend a good
online article that gives medium to advanced level discussion of using a
combo box? I still have got to get the issue resolved with the other (more
controls on it) form.

Thanks very much!!

Rich

 

[Guest]

You are on the right track with your revised table design.
For details about combo boxes, Access Help screens should have most
everything you need to know.
Its difficult to recommend books because it depends on your expertise level
and what Access version you have. Go to Amazon.com book section, type in
Access and read the reviews.

Dorian

 

[John W. Vinson]

This, I think would lead to a new table (lktbl_Company) (which I do have).
This table would look something like:
CompanyID Autonumber (this becomes the PK)
CompanyName Text

This table would then have 1 row for each unique company.
CompanyName would be a FK pointing to Lktbl_Role_Master.CompanyName. Am I
on the right trail with my thinking?

Not quite.

CompanyID would be the foreign key, pointing to Lktbl_Role_Master.CompanyID.
The Role Master table would not contain a company name field *at all*, just
the ID.

I had it like this previously but
deleted the relationships because I have another form with the same problem
and I thought I may have some kind of vicious cirle of relationships because
it would not work.

Anyway, this problem looks resolved but can either of you recommend a good
online article that gives medium to advanced level discussion of using a
combo box? I still have got to get the issue resolved with the other (more
controls on it) form.

Check out the references in:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

John W. Vinson [MVP]