VBA to query with variable field names?

[jkeyes]

First, I'm a bit of an Access newb, bear with me, please...

I have a simple query that I need to run based on two tables:

DirZip
Fields are: Directory Number, Zip Code, Inclusion
(Each directory number has several zip codes in it, with the Inclusion being the percentage of the zip that is included in the directory)

ZipDemographic
Fields are: Zip Code, and over 100 different demographics, e.g. population, number of Households, etc.


What I am trying to do is roll the demographic data up to the directory level. So, each demographic field from the ZipDemographic table needs to be multiplied by the inclusion rate of that Zip Code in the directory.

But, rather than having to type out the expression in each field in the query (which would take me hours), I'm thinking that I can probably run some code to run the query for me since I'm making the exact same calculation to each field in the query.

I'm not sure if you need more info or not... Like I said before I'm a bit of a newb, so try to be explicit, if possible. Thanks in advance for any and all help!

 

[jkeyes]

Thanks to both of you for help, I really appreciate it!

I just amended the few trouble headers to fit (btw, I didn't create the table, I just have to deal with it )

As for Norie's suggestion for using a ZipID to complete the normalization process, I get what you're saying, but I'm not sure why... still new to this normalization concept

Could you please explain what's different or useful about using an auto-number vs. a zip? The way my newb mind sees it, each of them is a unique number, so it wouldn't matter which one you used...

 

[jkeyes]

Sorry, another quick question...

If I wanted to make sure the "Value" in the Flat table was set as a Number (not Text), how can I adjust my code for that?

 

[Norie]

You could use the Zip as a primary key but a lot of people believe that in a primary key should be something arbritary and not connected directly to the data.

An Autonumber is regarded by some as a good way to generate a unique, arbritrary key.

Also, I'm not sure in your case, but something like a zip could be text and performing joins on text fields is slower than on number fields.

As to the Number issue, I suppose it depends on how you've set the 'flat' table up and also what the demographics actually are.

Are they percentages?

Are they currency?

etc

It might be 'safer' to keep the field as text and deal with any conversion to values at a laster stage.

 

[jkeyes]

You could use the Zip as a primary key but a lot of people believe that in a primary key should be something arbritary and not connected directly to the data.

An Autonumber is regarded by some as a good way to generate a unique, arbritrary key.

Also, I'm not sure in your case, but something like a zip could be text and performing joins on text fields is slower than on number fields.

This makes sense. The zip IS set as text, so I can see where a number might be more effective. Thanks!

As to the Number issue, I suppose it depends on how you've set the 'flat' table up and also what the demographics actually are.

Are they percentages?

Are they currency?

etc

It might be 'safer' to keep the field as text and deal with any conversion to values at a laster stage.

The values are just a count but will need to be calculated, so, I think, they should be numbers. I didn't think to change that field to number before running the code... no, that would make too much sense I tried changing after I ran it, and my PC ran out of memory trying to change it (yeah, it's a big file).

Anyway, got it straightened out... thanks again!