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!

 

[giacomo]

If you alter your table structure for ZipDemographic following the rules of normalization then you would not have to repeat the expresion. Your table should look like this:

<script type="text/javascript" > function myFunc733133852(myData,myVal) { setTextmyFunc733133852("Info733133852" ,myVal); setTextmyFunc733133852("Data733133852" ,myData); } </script><script type="text/javascript" > function setTextmyFunc733133852(myID,myVal) { if (navigator.appName == 'Microsoft Internet Explorer') { document.all(myID).innerText = myVal; } else{ document.getElementById(myID).textContent = myVal; } }</script><STYLE TYPE="text/css" > TD.AccTBInner { BORDER-RIGHT: none; BORDER-TOP: none; BORDER-LEFT: none; BORDER-BOTTOM: none; BACKGROUND-COLOR: #0000FF; FONT-SIZE: 12pt; FONT-FAMILY: times; FONT-STYLE: bold; COLOR: #ffffff;"} </STYLE><STYLE TYPE="text/css" > TD.AccTBMain { BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-BOTTOM: #B0B0B0 1px solid; BACKGROUND-COLOR: #0000FF; COLOR: #000000; FONT-SIZE: 8pt; FONT-FAMILY: times; "}</STYLE><STYLE TYPE="text/css" > TD.AccHDRMain { BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT: none; BORDER-BOTTOM: none; BACKGROUND-COLOR: #d8d8d8; COLOR: #ffffff; FONT-SIZE: 10pt; FONT-FAMILY: arial; "}</STYLE><STYLE TYPE="text/css" > TD.AccHDRLeftElem { BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-BOTTOM: none; BACKGROUND-COLOR: #d8d8d8; WIDTH: "2%"; COLOR: #000000; FONT-SIZE: 12pt; FONT-FAMILY: webdings; "}</STYLE><STYLE TYPE="text/css" > TD.AccDataElem { BORDER-RIGHT: #d4d0c8 0.25pt solid; BORDER-TOP: #000000 0.25pt solid; BORDER-LEFT: #000000 0.25pt solid; BORDER-BOTTOM: #d4d0c8 0.25pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; FONT-FAMILY: Sans-Serif; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left; "}</STYLE><STYLE TYPE="text/css" > TD.AccIEOnlyMain { BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #808080 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-BOTTOM: #000000 0.5pt solid; BACKGROUND-COLOR: #d8d8d8; COLOR: #000000; FONT-SIZE: 12pt; FONT-FAMILY: arial; TEXT-ALIGN: left; "}</STYLE><STYLE TYPE="text/css" > TD.AccIEOnlyInnerLeft { BACKGROUND-COLOR: #d8d8d8; FONT-SIZE: 9pt; FONT-FAMILY: arial; TEXT-ALIGN: left; "}</STYLE><STYLE TYPE="text/css" > TD.AccIEOnlyInnerRight { BACKGROUND-COLOR: #d8d8d8; WIDTH: "10%"; FONT-SIZE: 9pt; FONT-FAMILY: arial; TEXT-ALIGN: Right; "}</STYLE><STYLE TYPE="text/css" > TD.AccIEOnlyButton { BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #ffffff 0.5pt solid; BORDER-LEFT: #ffffff 0.5pt solid; WIDTH: "2%"; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Webdings; BACKGROUND-COLOR: #d8d8d8; TEXT-ALIGN: center; "}</STYLE><STYLE TYPE="text/css" > TD.AccIEOnlyData { BORDER-RIGHT: #B0B0B0 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-BOTTOM: #B0B0B0 1px solid; WIDTH: "10%"; BACKGROUND-COLOR: #ffffff; FONT-SIZE: 9pt; FONT-FAMILY: caption ; TEXT-ALIGN: Left; "}</STYLE><STYLE TYPE="text/css" > TD.AccInfoBarInnerRight { BACKGROUND-COLOR: #d8d8d8; FONT-SIZE: 9pt; FONT-FAMILY: arial; WIDTH: "25%"; TEXT-ALIGN: right; "}</STYLE><STYLE TYPE="text/css" > TD.AccInfoBarData { BORDER-RIGHT: #B0B0B0 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-BOTTOM: #B0B0B0 1px solid; BACKGROUND-COLOR: #ffffff; FONT-SIZE: 9pt; FONT-FAMILY: caption ; TEXT-ALIGN: left; "}</STYLE><CENTER><TABLE cellSpacing=0 cellPadding=2 align=center width=100% ><TR ><TD CLASS="AccTBMain" border-left=1px solid colSpan= 4 ><TABLE width="100%" align=left VALIGN="TOP" ><TR ><TD CLASS="AccTBInner" align=Left ><FONT color=white>ZipDemographic : Table </FONT></TD><TD CLASS="AccTBInner" align=right >Access 2000</TD></TR></TABLE></TD></TR><TR ><TD CLASS="AccHDRLeftElem" >
</TD><TD CLASS="AccHDRMain" align=middle ><CENTER>zip</CENTER></TD><TD CLASS="AccHDRMain" align=middle ><CENTER>demographic</CENTER></TD><TD CLASS="AccHDRMain" align=middle ><CENTER>value</CENTER></TD></TR><TR ><TD ID="currRec733133852" CLASS="AccHDRLeftElem" >4</TD><TD CLASS="AccDataElem" >12345</TD><TD CLASS="AccDataElem" >Number of Households</TD><TD CLASS="AccDataElem" >17000</TD></TR><TR ><TD ID="currRec733133852" CLASS="AccHDRLeftElem" >
</TD><TD CLASS="AccDataElem" >12345</TD><TD CLASS="AccDataElem" >Population</TD><TD CLASS="AccDataElem" >30000</TD></TR><TR ><TD ID="currRec733133852" CLASS="AccHDRLeftElem" >
</TD><TD CLASS="AccDataElem" >56789</TD><TD CLASS="AccDataElem" >Number of Households</TD><TD CLASS="AccDataElem" >25000</TD></TR><TR ><TD ID="currRec733133852" CLASS="AccHDRLeftElem" >
</TD><TD CLASS="AccDataElem" >56789</TD><TD CLASS="AccDataElem" >Population</TD><TD CLASS="AccDataElem" >45000</TD></TR><TR ><TD ID="IE733133852" CLASS="AccIEOnlyMain" colSpan= 4 ><TABLE width="100%" align=left VALIGN="TOP" ><TR ><TD CLASS="AccIEOnlyInnerRight" > Record: </TD><TD CLASS="AccIEOnlyButton" > 9 </TD><TD CLASS="AccIEOnlyButton" > 3 </TD><TD CLASS="AccIEOnlyData" > 1 </TD><TD CLASS="AccIEOnlyButton" > 4 </TD><TD CLASS="AccIEOnlyButton" > : </TD><TD CLASS="AccIEOnlyInnerLeft" > of 4</TD></TR></TABLE></TD></TR><TR ><TD CLASS="AccIEOnlyMain" colSpan= 4 ><TABLE cellSpacing=0 cellPadding=0 width="100%" align=left VALIGN="TOP" ><TR ><TD ID="Data733133852" CLASS="AccInfoBarInnerRight" >ZipDemographic</TD><TD ID="Info733133852" CLASS="AccInfoBarData" > Record Count: 4</TD></TR></TABLE></TD></TR></TABLE></CENTER><script type="text/javascript">if (navigator.appName != 'Microsoft Internet Explorer') { var a = document.getElementById("currRec733133852"); a.innerHTML = '
'; var b = document.getElementById("IE733133852"); b.textContent = 'Table format has been altered for non-IE browsers '; b.style.fontSize = 10;}</script>


with this structure then your query expression would simply be [Inclusion] * [Value] written once and not repeated over 100 times. This design also alows for the addition of new demographics (# of ipods maybe) without the need to create new fields and altering queries to accomodate these new fields.

hth,
Giacomo

 

[jkeyes]

Thanks for the reply...

The resulting query will need to maintain the standard table format (each record having all the demographic fields). Is that something that can still be accomplished with your suggestion? Assuming it is, could you please walk me through how I'd get to what you're suggesting from what I have? For example, I'm not sure what you mean by "following the rules of normalization"...

If your suggestion will not give me the end result I'm looking for, let's assume I keep the tables in their original format and approach this from a VBA perspective... how do I code Access to take care of this for me?

Thanks again!

 

[giacomo]

The resulting query will need to maintain the standard table format (each record having all the demographic fields). Is that something that can still be accomplished with your suggestion?

Yes, you can still get to the result you're looking for with a crosstab query.

Assuming it is, could you please walk me through how I'd get to what you're suggesting from what I have? For example, I'm not sure what you mean by "following the rules of normalization"...

You would most likely have to rework your existing table into a new one through a series of append queries. This will take a while but once it is complete it will save you a lot of time in the long run.

Regarding normalization, take a look at the links provided in my signature. You'll want to understand this concept first before you do any more development on your database.

If your suggestion will not give me the end result I'm looking for, let's assume I keep the tables in their original format and approach this from a VBA perspective... how do I code Access to take care of this for me?

I cannot stress enough what a bad move it would be to keep your tables set up the way they are. Yes, it might be painfull to convert your tables to normalized ones but once you have you will be able to do the kind of analysis you're talking about with ease.

hth,
Giacomo

 

[jkeyes]

Thanks Giacomo-

I reviewed your links and did some more research on normalization to get my head around it. While I think I get it, I still don't see how my ZipDemographic table isn't normalized (sorry, this is my first time with this...). Don't I have unique data to each primary key (Zip)? Is there redundant data I'm not seeing?

EDIT: OK, I think I'm starting to see... I was thinking of each of field as unique to the zip, when they are actually each sub-components of "Demographics", right? Wow, that's gonna take a little getting used to... thinking of my data in those terms.

Once I "get" this part, then I have to move on to understanding append queries (I told you I was a newb! ).

EDIT: So, I'm looking into Append queries now, too. I am not seeing how I'd get my data into your suggested format using these... any tips?

Thanks again!

 

[Norie]

You could actually create the normalized table using VBA.

I've written code that does that, and I think I've posted it here.

I'll have a look and post back if I find it.

Found it.

 

[giacomo]

First of all, I took another look at that Normalization Rules link I provided and I see now that there is not a good example for you to follow. Sorry about that...

One of the requirements of First Normal Form is that your table does not have multiple columns representing the same type of fact. This link better represents this:

http://en.wikipedia.org/wiki/First_normal_form#Multiple_columns_representing_the_same_type_of_fact

Although all of your demographics are uniquely named it's the same as saying Food Not Eaten (1), Food Not Eaten (2), Food Not Eaten (3) in the example above. This may become clearer to you when you consider that you are taking the product of each demographic by the Inclusion percentage. By having to multiply each value by the same percentage you are telling me that they are all of the same type, a Demographic Type.

I feel that you can simplify your structure from 100+ fields of demographic values to just 2, one for the demographic type and another for the value associtated with that type. Again, this will take some effort, but it's a more elegant table structure in the long run.

Let me know if that makes sense then we can move onto the append queries.

hth,
Giacomo

 

[jkeyes]

You could actually create the normalized table using VBA.

I've written code that does that, and I think I've posted it here.

I'll have a look and post back if I find it.

Found it.

Thanks... I tried plugging your code in and adjusting to fit my data, but it's not seeming to work (nothing happens). I'm sure it's something I'm doing or not doing, as this is my first time using VBA in Access. But here's what I did:

Renamed current ZipDemographic table as "Wide".

Created new table, "Flat", with fields named Zip, Demographic, and Value.

Copied your code and pasted into a new module under the db in VBA.

Commented out the last Fields line in your For/Next loop as I only have 3 fields...

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 = 2 To rstWide.Fields.Count - 1
                .AddNew
                .Fields(0) = rstWide.Fields(0)
                .Fields(1) = rstWide.Fields(1)
                .Fields(2) = rstWide.Fields(I)
                '.Fields(3) = rstWide.Fields(I).Name
                .Update
            Next I
        End With
        rstWide.MoveNext
    Wend
End Sub

Hit F5 to run sub...

Any idea where I'm going wrong?

 

[giacomo]

this worked for me...

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

 

[Norie]

jkeyes

Did the code actually run?

Did you get any errors?