for_each_loop_vba

[gis_help2002]

Hello everybody!

please somebody help me about this problem:

in a access_DB I've one MAIN_TABLE whith multiple primary key COD1
COD2 COD3 containing the field EVENTS

then I've about 200 tables having foreign key COD1 COD2 COD3
containing the field LOCALITY_NAME

I NEED TO JOIN FOR EACH TABLES THE FIELD "EVENT" IN THE MAIN_TBL WHITH
THE FIELD "LOCALITY_NAME" PRESENT IN ALL THE TABLES

I MEAN ONE IDENTICAL JOIN FOR EACH TABLES (WHERE COD MEET OF COURSE)

I'VE ALREADY CREATED A LIST_TABLE (GET FROM A SELECTED QUERY IN
MSYSOBJECTS)CONTAINING THE NAMES OF THE 200 TABLES THAT I WHANT TO
JOIN.

Coul somebody help me writing down the loop for a vba module that can
perform what I need.

I am very grateful to anyone who will help me.

 

[tina]

i have to wonder about your tables design. if i needed to link a parent
table to 200 child tables, i probably wouldn't use a combination primary key
consisting of 3 fields - that's quite an excess of data duplication. i would
probably use an auto number as primary key in the parent table, and set a
unique index for the three fields, instead.
also, if you have 200 tables that all have the same field called
LOCALITY_NAME, then my guess is that your table design is *not* normalized.
sounds like you created a separate table for each locality, to track events
in that locality. or perhaps you created a separate table for each event?
that might be reasonable, if the events have enough differences that it is
impractical to store them all in one table.
a general suggestion would be that you read up on table
design/normalization, to be sure you are optimizing your table design. here
are a few links, and you can google for more.

ACC2002: Database Normalization Basics
http://support.microsoft.com/?id=283878

Database Normalization Tips by Luke Chung
http://www.fmsinc.com/tpapers/genaccess/databasenorm.html

Data Normalization Fundamentals by Luke Chung
http://www.fmsinc.com/tpapers/datanorm/index.html

324613 - Support WebCast: Database Normalization Basics
http://support.microsoft.com/?id=324613

proper table/relationship design now will repay you a thousandfold later, in
building the rest of the db and maintaining the data.
for more specific suggestions, from me or others, suggest you post details
of the fields in your parent table and more than one of the child tables, as
format

ParentTableName
FieldName (combo primary key)
FieldName (combo primary key)
FieldName (combo primary key)
FieldName
FieldName
etc, etc, etc.

ChildTableName
FieldName (primary key)
FieldName (combo foreign key)
FieldName (combo foreign key)
FieldName (combo foreign key)
FieldName
FieldName
etc, etc, etc.

hth

 

[Duane Hookom]

I agree with tina. However, with all due respects to Luke Chung, the second
link "Data Normalization Fundamentals" doesn't go nearly far enough toward
normalization. His "Normalized Data" contains field names like "Human
Resources", "Defense", and "Other". These should be data values rather than
field names.

 

[tina]

okay, i have to confess that i've only skimmed those links, some time back.
i saved them from a reputable contributor's post, so i'd have an quick
reference to offer people. my bad, for not studying them myself. i guess i'd
better do some research before offering normalization links again! <g>