transpose field values to field names

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.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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);-)

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!

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bhynesAuthor Commented:
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.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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!
bhynesAuthor Commented:
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!
I'm glad to hear that. Good luck with your project!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.