Table for combobox fields

[Harold via AccessMonster.com]

Hi,
I would like to have a combo box on a form to list the street names. To
assure that the name gets entered with the correct spelling. Should I have a
separate table with street names for the combo box to be bound to? This table
would have to hold several hundred street names. How could I link this table
to my Address Table? My Address Table looks like this:

AddressID
[StreetNumber]
[StreetName]
[Direction]

AddressID is the primary key. Would the relation with the new combobox street
table by a one to one with the Address table? Could I link them by street
name? Should it all be in one table? And most importantly, does anyone want
to type in 300 street names for me?

Thanks,

Harold

 

[Marc]

Hi,
I would like to have a combo box on a form to list the street names. To
assure that the name gets entered with the correct spelling. Should I have
a
separate table with street names for the combo box to be bound to? This
table
would have to hold several hundred street names. How could I link this
table
to my Address Table? My Address Table looks like this:

AddressID
[StreetNumber] this changes to StreetId
[StreetName]
[Direction]

AddressID is the primary key. Would the relation with the new combobox
street
table by a one to one with the Address table? Could I link them by street

one StreetTable record for many Address table records

StreetTable
[StreetId]
[StreetName]

name? Should it all be in one table? And most importantly, does anyone
want
to type in 300 street names for me?

lol, you're funny

Thanks,

Harold

Marc

 

[Guest]

A separate table for street names is a good approach, but it would be used
for the combo box's row source only. The Control Source (bound field) is
StreetName in the form's Record Source. When you select a street name from
the combo box list, the street name itself would be written to the main
table. I don't see any need to link the Street Name table to the StreetName
in the main table. Since you are storing a single field it may as well be
the actual value (the street name) rather than a separate key field from the
Street Name table.

 

[Harold via AccessMonster.com]

A separate table for street names is a good approach, but it would be used
for the combo box's row source only. The Control Source (bound field) is
StreetName in the form's Record Source. When you select a street name from
the combo box list, the street name itself would be written to the main
table. I don't see any need to link the Street Name table to the StreetName
in the main table. Since you are storing a single field it may as well be
the actual value (the street name) rather than a separate key field from the
Street Name table.

Hi,
I would like to have a combo box on a form to list the street names. To

[quoted text clipped - 16 lines]

Thanks for replying,

I like the idea of using a street name table for the combo box's row source
only. I wasn't sure if I could do this without setting up some kind of
relationship between the two tables. The street number I refered to was the
actual address number as in : 455 N Pine Street. The number would not be
unique.

 

[Guest]

May I suggest that if you are selecting the street name from a combo box that
you store the street number in a separate field? If you need to combine them
you can do so in a query or in a text box. This would be the best approach
if you want to limit street names to what is on the list. There is no need
to link the tables if you are going to store a single field. The only caveat
would be that if a street name changes you need to decide whether an old
record should contain the name that was in use at the time, or if it should
contain the new name. If the latter, you would need to store the PK field,
not the street name. You can decide whether or not a field needs to be
unique. There is no requirement that a table needs to have a PK field,
especially if the table is used as a list for a combo or list box selection.
It's usually a good idea to have a PK field in case you need to use it later,
but you don't need to use it.

A separate table for street names is a good approach, but it would be used
for the combo box's row source only. The Control Source (bound field) is
StreetName in the form's Record Source. When you select a street name from
the combo box list, the street name itself would be written to the main
table. I don't see any need to link the Street Name table to the StreetName
in the main table. Since you are storing a single field it may as well be
the actual value (the street name) rather than a separate key field from the
Street Name table.

Hi,
I would like to have a combo box on a form to list the street names. To

[quoted text clipped - 16 lines]

Thanks for replying,

I like the idea of using a street name table for the combo box's row source
only. I wasn't sure if I could do this without setting up some kind of
relationship between the two tables. The street number I refered to was the
actual address number as in : 455 N Pine Street. The number would not be
unique.