transpose field values to field names

Posted on 2006-04-12
Last Modified: 2008-02-01
Hi All,

In Access97 I have a form which has a tab control with several tabs but
my issue is with just two of the tabs I'll call them tabSumy and
tabDetail for easy of explaining my issue.

First - tabDetail is bound to tblDetail, its a transactional looking
table like
MKey     Category      Cost
235486  Food            50.00
235486  Rent           250.00
235486  Travel           75.00
235486  Food            25.00
next mkey etc......

tabSumy is bound to tblEstimatedCost, its field names are the same as
the Category names in tblDetail,
and is summed so it looks like
MKey    FoodEst   Food   RentEst    Rent   TravEst    Travel
235486   100.00    75.00   200.00   250.00   125.00     75.00
next mkey  etc......

This db existed already with the tabSumy/tblEstimatedCost, my task is
to add tabDetail/tblDetail and when a user moves from the tabDetail tab
to sum up the values by category for the MKey and update those totals
to the tabSumy/tblEstimatedCost so, what do I need to do?
I think I need sort of crosstab query into a temp table of the
tabDetail data for the MKey and then a Do Loop with an if statement
something to the effect;

myrec = tblDetail, myrec1 = tblEstimatedCost
if myrec!category = myrec1! then
myrec1! = myrec!cost

Can a field name be evaluated and matched to something?
if so, can the value for that field name be updated?

anybody got any vba code they want to share that does this? or have any
other idea's as I'm not getting very far very fast with trying to
figure this out.
Question by:bhynes
    LVL 44

    Expert Comment

    Estimates should be in one table - Actuals in another.  It then becomes a very simple query to compare the Sum(est.Food) to Sum(Act.Food).  It's what I call a spreadsheet mentality in a database environment);-)
    LVL 58

    Accepted Solution


    Ray is right, of course, whoever designed the table tblEstimatedCost was not a database specialist and s/he made the wrong decision (in the long run, that is). However, let's answer the question nontheless.

    I will assume that the button is on the main form. The code could look like this:

        ' transfer new sums to tblEstimatedCost
        Const cCategList = " 'Food', 'Rent', 'Travel' "

        Dim recSums As DAO.Recordset
        Dim strSQL As String

        strSQL _
            = "TRANSFORM Sum(Cost)" _
            & " SELECT MKey" _
            & " FROM tblDetails" _
            & " GROUP BY MKey" _
            & " PIVOT IIf(Category In (" & cCategList & "), Category, 'Other')" _
            & " IN (" cCategList & ", 'Other');"

        Set recSums = CurrentDb.OpenRecordset( strSQL, dbOpenSnapshot )
        Do Until recSums.EOF
            With Me.subSumy.Form.RecordsetClone
                .FindFirst "MKey = " & recSums!MKey
                ' .FindFirst "MKey = '" & recSums!MKey & "'"   ' if MKey is text

                If .NoMatch Then
                    ' handle that here :)

                    !Food = Nz(recSums!Food, 0)
                    !Rent = Nz(recSums!Rent, 0)
                    !Travel = Nz(recSums!Travel, 0)
                    !Other = Nz(recSums!Other, 0)   ' you sould have an "other" column!
                End If

            End With

        Me.subSumy.Form.Requery   ' might not be needed...

    Note: you don't really need to have the SQL in your VB code, unless you want to insert a WHERE clause to limit which MKeys you want to handle. [The WHERE clause comes just before the GROUP BY.] If not, you might as well save the query "qxtbDetailSums" and open it by name in your code.

    As you see, there is no need to lookup the field names based on the value in another. The cross-tab query does that for you, with a good control over the accepted values and the desired column output. But for the record, you were probably looking for a syntax like this:

        myrec1.Field(myrec.Category) = myrec!cost

    However, this does neither check the validity of the category name nor perform the sum...

    Tell me if you encounter any problems of if this was unclear.    

    Hope this helps!

    Author Comment

    Hi harfang,
    I accepted your solution even though it's going to be another week or two til I get back to that issue but I didn't want to leave this open and since you responded with code that will direct me to the right direction.
    LVL 58

    Expert Comment

    I hope it will work, in that case. If this question is closed when you get back to it, open a follow-up question and send me an email (address on my profile page), with a link to this question and the new one. Or just send an e-mail.

    I would feel bad if this didn't work and if you had no way to reach me.

    Good luck!

    Author Comment

    Hello harfang,
    Just to let you know I got it all working, a few tweaks here and there and it works as needed. Thank You!
    LVL 58

    Expert Comment

    I'm glad to hear that. Good luck with your project!

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    The first two articles in this short series — Using a Criteria Form to Filter Records ( and Building a Custom Filter ( — discuss in some detail how a form can be…
    This isn't a frequent question on EE. I must have seen it three or four times (among several thousand questions). However, I use this trick quite often, most frequently as a delayed Current event. A form does not expose it's calculation dependenc…
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now