transpose field values to field names

Posted on 2006-04-12
Medium Priority
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!field.name then
myrec1!field.name.value = 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
  • 3
  • 2
LVL 44

Expert Comment

ID: 16441506
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

harfang earned 2000 total points
ID: 16464324

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

ID: 16478180
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.
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

LVL 58

Expert Comment

ID: 16479806
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

ID: 16535225
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

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

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

621 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