I have a problem. I need to associate information in two seperate tables with
each other selectively. Meaning, I need items in Table A to be associated to
with some (but not necessarily all) items in Table B. I also need to be able
to add new items to both tables and make new association as required.

An example would be paint and dyes.

You have the following paints, classified by names: Eggshell, Off-white,
Sunny Day, Cloudy Day, Dirt and Grass. These are in Table A.

You have the following Dyes, classified by an index number: 120, 134, 154,
192, 195. This is Table B.

Now, certain dyes are used in certain paints, but since the process of
getting a color just right might require changes, you need to be able to
change the associations as new dyes and paints are added to the lists.

Cloudy Day might initially be Dye# 120 and 192, but later an improvement
might mean that dye # 195 is added.

Conversely, a new color named Umber is added, and is assocated with Dye #s
134 and 154. Later improvements to dye technology and color matching
indicates that dye # 192 is required.

All of this information and these changes must be tracked in an easily
manageble way. Any ideas?

What you need is a "junction table" -- a third table that allows you to
relate the items from Table A to the items in Table B. This is how you do a
"many-to-many" relationship, which is what you want to establish between the
paint and the dyes.

The table structure would be very simple:

tblPaintDye
PaintDyeID (autonumber; primary key)
PaintID (foreign key to the paints table)
DyeID (foreign key to the dye table)

Then you enter a record for each "match" of a paint and dye.

--

Ken Snell
<MS ACCESS MVP>


"Firstlord" <(E-Mail Removed)> wrote in message
news:61DFFF17-4DC9-4B42-8538-(E-Mail Removed)...
>I have a problem. I need to associate information in two seperate tables
>with
> each other selectively. Meaning, I need items in Table A to be associated
> to
> with some (but not necessarily all) items in Table B. I also need to be
> able
> to add new items to both tables and make new association as required.
>
> An example would be paint and dyes.
>
> You have the following paints, classified by names: Eggshell, Off-white,
> Sunny Day, Cloudy Day, Dirt and Grass. These are in Table A.
>
> You have the following Dyes, classified by an index number: 120, 134, 154,
> 192, 195. This is Table B.
>
> Now, certain dyes are used in certain paints, but since the process of
> getting a color just right might require changes, you need to be able to
> change the associations as new dyes and paints are added to the lists.
>
> Cloudy Day might initially be Dye# 120 and 192, but later an improvement
> might mean that dye # 195 is added.
>
> Conversely, a new color named Umber is added, and is assocated with Dye #s
> 134 and 154. Later improvements to dye technology and color matching
> indicates that dye # 192 is required.
>
> All of this information and these changes must be tracked in an easily
> manageble way. Any ideas?

Yes. You should end up with three tables:

tblColor - essentially the color set that you show as table A
tblDye-your current dable B
tblColorDye-this is the new table that will associate the dyes that
are used to make the named color.

tblColor is your main table and the the one on which your form will be
based

tblDye is a lookup table. It wil list every dye you use.

tblColorDye is where the dyes used to make the color are listed, one
record for each dye that goes into the color. Its records are
presented in a subform on your main form

HTH
--
-Larry-
--

"Firstlord" <(E-Mail Removed)> wrote in message
news:61DFFF17-4DC9-4B42-8538-(E-Mail Removed)...
> I have a problem. I need to associate information in two seperate
tables with
> each other selectively. Meaning, I need items in Table A to be
associated to
> with some (but not necessarily all) items in Table B. I also need
to be able
> to add new items to both tables and make new association as
required.
>
> An example would be paint and dyes.
>
> You have the following paints, classified by names: Eggshell,
Off-white,
> Sunny Day, Cloudy Day, Dirt and Grass. These are in Table A.
>
> You have the following Dyes, classified by an index number: 120,
134, 154,
> 192, 195. This is Table B.
>
> Now, certain dyes are used in certain paints, but since the process
of
> getting a color just right might require changes, you need to be
able to
> change the associations as new dyes and paints are added to the
lists.
>
> Cloudy Day might initially be Dye# 120 and 192, but later an
improvement
> might mean that dye # 195 is added.
>
> Conversely, a new color named Umber is added, and is assocated with
Dye #s
> 134 and 154. Later improvements to dye technology and color
matching
> indicates that dye # 192 is required.
>
> All of this information and these changes must be tracked in an
easily
> manageble way. Any ideas?

Thanks to both of you for your help. It is greatly appreciated.

"Larry Daugherty" wrote:

> Yes. You should end up with three tables:
>
> tblColor - essentially the color set that you show as table A
> tblDye-your current dable B
> tblColorDye-this is the new table that will associate the dyes that
> are used to make the named color.
>
> tblColor is your main table and the the one on which your form will be
> based
>
> tblDye is a lookup table. It wil list every dye you use.
>
> tblColorDye is where the dyes used to make the color are listed, one
> record for each dye that goes into the color. Its records are
> presented in a subform on your main form
>
> HTH
> --
> -Larry-
> --
>
> "Firstlord" <(E-Mail Removed)> wrote in message
> news:61DFFF17-4DC9-4B42-8538-(E-Mail Removed)...
> > I have a problem. I need to associate information in two seperate
> tables with
> > each other selectively. Meaning, I need items in Table A to be
> associated to
> > with some (but not necessarily all) items in Table B. I also need
> to be able
> > to add new items to both tables and make new association as
> required.
> >
> > An example would be paint and dyes.
> >
> > You have the following paints, classified by names: Eggshell,
> Off-white,
> > Sunny Day, Cloudy Day, Dirt and Grass. These are in Table A.
> >
> > You have the following Dyes, classified by an index number: 120,
> 134, 154,
> > 192, 195. This is Table B.
> >
> > Now, certain dyes are used in certain paints, but since the process
> of
> > getting a color just right might require changes, you need to be
> able to
> > change the associations as new dyes and paints are added to the
> lists.
> >
> > Cloudy Day might initially be Dye# 120 and 192, but later an
> improvement
> > might mean that dye # 195 is added.
> >
> > Conversely, a new color named Umber is added, and is assocated with
> Dye #s
> > 134 and 154. Later improvements to dye technology and color
> matching
> > indicates that dye # 192 is required.
> >
> > All of this information and these changes must be tracked in an
> easily
> > manageble way. Any ideas?
>
>
>