Re: Combo Box Design and/or Coding

Tech-Archive recommends: Fix windows errors by optimizing your registry

you're describing a many-to-many relationship between tblHotels and
tblLocations. that is resolved with a "linking" table, as

tblHotelLocations
HotelID (foreign key from tblHotels)
LocationID (foreign key from tblLocations)
you may use the two fields as a combination primary key, which will ensure
that each combination is unique. if you prefer to work with single-field
primary keys, you can add another field to serve as a surrogate primary key,
such as HLID (data type Autonumber). in that case, you can still create a
unique (though not primary) index of the two foreign key fields, to ensure
that each combination is unique.
note: strongly recommend that you do NOT use Lookup fields in
tblHotelLocations (or in any other table in your database, for that matter).

the relationships are
tblHotels (parent) 1:n tblHotelLocations (child)
tblLocations (parent) 1:n tblHotelLocations (child)

in a form, you can use combo boxes to allow selections of specific hotels
and locations from those parent tables. (suggest you look up the following
combo box control Properties in Access Help, to learn how they work:
RowSourceType, RowSource, ColumnCount, ColumnWidths, BoundColumn, ListWidth,
LimitToList, and NotInList.) there are several ways to add records to the
parent tables while in the form, utilizing the NotInList event. see
http://www.mvps.org/access/forms/frm0015.htm for one solution; you can find
others by doing a search on these newsgroups (this is a common question).

hth


"bw" <iamnu@xxxxxxxxxxxx> wrote in message
news:11oeugjcg4jnl21@xxxxxxxxxxxxxxxxxxxxx
> I don't know if this is a design problem, or coding. In any case, I don't
> know how to do what I want.
>
> I want to have a table of Hotels.
> I want to have a table of Locations.
>
> Hotels may have many Locations.
> Locations may have many Hotels.
> A Hotel/Location combination must be unique.
>
> So I would like to know how to set up the Table(s) to accomplish this, and
> maybe an explanation as to what the relationships are here.
>
> From a practical stand point, I want to be able to allow for selection of
a
> Hotel/Location combination, on a Form, AND to be able to update these
tables
> from the Form, if the Hotel/Location combination does not currently exist.
>
> I'm appreciative of any help received.
>
> Thanks,
> Bernie
>
>


.

Relevant Pages

  • Re: Combo Box Design and/or Coding
    ... > HotelID (foreign key from tblHotels) ... > LocationID (foreign key from tblLocations) ... > you may use the two fields as a combination primary key, ... >> I want to have a table of Hotels. ...
    (microsoft.public.access.formscoding)
  • Re: Combo Box Design and/or Coding
    ... > HTH?? ... >> LocationID (foreign key from tblLocations) ... >> you may use the two fields as a combination primary key, ... >>> I want to have a table of Hotels. ...
    (microsoft.public.access.formscoding)
  • Re: FAQ? factors influencing choice of data type for primary key
    ... >>example, an Autonumber as a primary key, are there ... >>it is a foreign key? ... >don't need to bring the lookup table or any indexes into ...
    (microsoft.public.access.tablesdbdesign)
  • Re: OT: SQL & Dave
    ... A&E broadcast ... My initial thought was to have the "show number" be the primary key, ... so that it'll be the foreign key in other tables (guests, staff, ... could I set up those other tables (guest, ...
    (alt.fan.letterman)
  • Re: Data changes but reverts.
    ... > trust my data to a wizard-built form. ... > 3) What is the primary and foreign key in your relationship ... I did a TABLEUPDATE() and it's now working! ... The symbol field is the primary key in the Symbols table and it is ...
    (microsoft.public.fox.programmer.exchange)