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]

I have a feeling my problem has to do with where I'm requesting the sub to run... with both tables and VB open (with VB active), I'm just hitting F5... thoughts?

 

[Norie]

Don't have the tables open, they don't need to be.

I don't know if it's the problem, but it's worth a try.

 

[giacomo]

close both tables and run it. Norie's code runs super fast and you won't get any sort of confirmation. just check out the flat table after you run it.

EDIT: BTW Very nice code Norie!

 

[jkeyes]

Hmmm... I set up a test table with the following fields: Zip, Population, Households. And, I have about 10 different records of zips in it. I ran it just like you said, and just like I posted. And all it seemed to do was change my demographic data from numbers to text. It is still arranged the same way, there aren't multiple records for each Zip...

Granted I'm not familiar with Access VBA, but it looks to me like the code is just telling it to create a new record in Flat, then add in the value from fields 0, 1, and 2 (since there aren't more than that in my test table), next record. Which is what it seems to be doing...

... thoughts on what I'm doing wrong?

 

[jkeyes]

OK, I thought that maybe only having 2 demographic fields might be an issue, so I added one (Distribution) to my test table, and ran the code. It created multiple records, but rather than place the header in field 2 and it's value in field 3, it repeated the field 2 value for each like zip record, and returned the households value in field 3 for the first record, and the distribution value in field 3 of the next record.

So if I have these fields in my Wide table:

Zip, Pop., Households, Dist.

I'd like it to appear like this in the Flat table:

Zip, Demographic, Value

Where the value under the Demographic field is the header value from the Wide table, and the value is the, well, value under that particular demographic for that zip.

 

[Norie]

Are you sure you have the flat/wide tables the correct way round?

The 'wide' table should be the one with the multiple fields. ie your existing demographics table.

The 'flat' table should be a table with no records.

The code should run through every field in the 'wide' table, apart from the first two.

It should then create a record in the 'flat' table for every field that contains 4 fields.

Field1 = Field1 from 'wide'

Field2 = Field2 from 'wide'

Field3 = value from FieldI from 'wide' where I is the field's position in the 'wide' table

Field4 = the name of FieldI from 'wide' where I is the field's position in the 'wide' table

Note the Field4 is really needed so you can actually identify what demographic the record is for.

Note also it takes no account of field types.

 

[jkeyes]

Thanks, yes, I double-checked all that...

But, I tweaked your code and it seemed to work. Please see below and let me know if you see any issues... thanks!

Sub AddToFlat()
Dim db As DAO.Database
Dim fldWide As DAO.Field
Dim fldFlat As DAO.Field
Dim rstWide As DAO.Recordset
Dim rstFlat As DAO.Recordset
Dim I As Long

    Set db = CurrentDb

    Set rstWide = db.OpenRecordset("Wide")
    Set rstFlat = db.OpenRecordset("Flat")

    rstWide.MoveFirst
   
    While Not (rstWide.EOF)
        With rstFlat
            For I = 1 To rstWide.Fields.Count - 1
                .AddNew
                .Fields(0) = rstWide.Fields(0)
                .Fields(1) = rstWide.Fields(I).Name
                .Fields(2) = rstWide.Fields(I)
                .Update
            Next I
        End With
        rstWide.MoveNext
    Wend
End Sub

 

[jkeyes]

OK, I assumed my tweaking was OK and moved onto a larger sample of my data to test... got an error message... looks like some of the headers exceed the character limit of a cell... is that possible?

Any ways around that?

 

[giacomo]

OK, I assumed my tweaking was OK and moved onto a larger sample of my data to test... got an error message... looks like some of the headers exceed the character limit of a cell... is that possible?

Any ways around that?

what did you set the field size to when you created the table?

 

[Norie]

That looks fine, and if it worked even better.

It also ties in with your previous post regarding the Zip being the only common value for each record.

If you look at the link I posted you'll see the OP had a 'wide' table where it was the first 2 fields that would be repeated.

By the way there's actually another step towards normalization.

That would be to create a table just for the Zips.

It would only hold the unique zips and would have a primary key say an AutoNumber field called ZipID.

You would then add a field ZipID (Long Integer) to the new 'flat' table.

You would then populate this field via a simple update query that links the 'flat' table to the 'zip' table.

You could then dispense with the zip field in the 'flat' table.

Probably confused you there, sorry.