Don't want to recreate the wheel: auto populate intersection table

[Guest]

Hello:

This is a basic question and I'm sure someone already has the code:

I have two tables (X and Y) with a many to many relationship, hence an
intersection table between them (XYIntersection). Tables X and Y both have a
"Keywords" field (Xkeywords, Ykeywords). I want to populate the
XYIntersection table with X to Y mapping when the two keyword fields share
the same keyword:

If xkeywords contains "thiskeyword" and ykeywords contains "thiskeyword",
add record to XYIntersection.

My brain is stuck on this so this may not be the best approach.

Thanks for your expertise, B

 

[Joan Wild]

You should just create a form to enter data and let Access do this for you.

Either a main form based on X and a subform based on a query involving
XYIntersection and Y
or
a main form based on Y and a subform based on a query involving
XYInstersection and X

(or perhaps both depending on your needs)

 

[Guest]

Hi Joan:

I *know* i can use a form to *enter* the data, but then Access is *not*
doing the work for me--*I* am entering the data. I'm sure this can be done in
code and am hoping for a generous soul to provide something that just *has*
to be already out there. Alas, a new learning for me perhaps.

Thanks, B

 

[Douglas J Steele]

Are Xkeywords and Ykeywords properly normalized, or do they contain a number
of keywords, separated by commas?

If they're properly normalized, you should be able to write a query that
joins the two tables.

If they're not, give more details. It will be possible to write a module to
do it.

 

[Guest]

Hello:

Rethinking this: perhaps it would be better to create a Keywords table and
then create intersection tables between it and both X and Y. In this case, I
could easily update the one side of these relationships with update queries
(I already know the "ykeywords" values). Then when i get the "xkeyword"
values, i would similarly use update queries to populate the x side of this
relationship.

Then using select queries to pull the X - Keyword - Y mapping would be
relatively simple. Am I making this too hard?

Perhaps I am missing the simlicity of Joan's solution below?

Thanks, G