code for creating a relationship between all tables in db

[Boulder_girl]

Hi, I've got ~100 tables, each of them has 2 fields: 'Value' and 'Count'...

I want to link them all (or create relationships, I'm not sure what the
difference is)
to another table (this "Master" table also has the same fields).

I want the relationship to be such that all records from the "Master" table
will be shown, but only those records from each of the other 100 tables where
the joined fields are equal will be shown.

I frequently need to do this type of operation, so I'd like a generic code
that links all the tables I've imported into access based on their 'Value'
field

If anyone has any suggestions, I'd appreciate them! Thanks,
-Tiffany

 

[John W. Vinson]

Hi, I've got ~100 tables, each of them has 2 fields: 'Value' and 'Count'...

Then you almost certainly have an INCORRECTLY DESIGNED DATABASE.

I want to link them all (or create relationships, I'm not sure what the
difference is)
to another table (this "Master" table also has the same fields).

I want the relationship to be such that all records from the "Master" table
will be shown, but only those records from each of the other 100 tables where
the joined fields are equal will be shown.

Any 101 table query will be too big for Access to process.

I frequently need to do this type of operation, so I'd like a generic code
that links all the tables I've imported into access based on their 'Value'
field

If anyone has any suggestions, I'd appreciate them! Thanks,
-Tiffany

What are these tables? What data do they contain other than the value and the
count? What real-life Entities to they represent?

I strongly suspect that they could be consolidated into *one* table, but
that's not certain based on your description.

 

[Boulder_girl]

Hi John,

These ~100 tables are actually representations of 20 different regions
within the US. The 'Value' filed represents a particular ecological class
within any given region and the count represents the number of 10 km grid
cells that each ecological class occupies within each of the 20 regions. The
reason there are 100 tables is that 20 of them represent current geographic
distributions of these 'eco-zones', while the other 80 represent modeled
future potential distributions of each class within each region based on
model output from each of 4 different global climate models.

I actually would use only about 5 tables in any one query, as I look at
model output scenarios on a region-by-region basis, so no 1 query would ever
involve calling out all 100 tables. Nonetheless, it is a meticulous and
time-consuming task to manually link each table. I regularly create these
sorts of dbs, and so I'd really like to automate portions of the process.

 

[John W. Vinson]

These ~100 tables are actually representations of 20 different regions
within the US. The 'Value' filed represents a particular ecological class
within any given region and the count represents the number of 10 km grid
cells that each ecological class occupies within each of the 20 regions. The
reason there are 100 tables is that 20 of them represent current geographic
distributions of these 'eco-zones', while the other 80 represent modeled
future potential distributions of each class within each region based on
model output from each of 4 different global climate models.

So you're commiting a very common mistake: storing data in tablenames.

Rather than 100 tables you need ONE table with two additional fields -
Distribution and ClimateModel.

You can then just join this table for your calculations, using criteria to
select which region and which model. You can run a bunch of Append queries to
migrate your existing data in this table.

 

[Boulder_girl]

Well, it's been a couple of weeks since your post, John, but if you're still
out there, I'm still trying to solve this problem.

I really don't think I can just import all of my excel tables into a single
Access table. Why? Because every Excel table has the same 2 field names:
'VALUE' and 'COUNT'... although maybe it would be possible to get some code
that adds a unique name tag on to each new column that is imported (and
pasted) into a single access table. Unfortunately, I'm exceedingly new to
VBA (and programming in general), so I'm not sure how to go about doing it
this way.

If anyone has any suggestions, they'd be very welcome.

Thanks,
-Tiffany