MS Access - how to use one field's data as the field name in a query

I have a set of data that includes one table with transactions, and another that will be a sort-of summary table.  The summary table has fields in it for various types of transactions.  i would like to run an 'update' query that goes through all of the transaction records, and updates the appropriate field in the summary records......determining which field to update based upon the value of a field in the transaction table (the transaction type).  I have MS Access 2010, but also have 2003 & 2007 if necessary.

Is this possible......JUST as an Access "Query" ?

If not, can it be written in VB code or SQL code ?
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.

Helen FeddemaCommented:
It sounds doable, as VBA code, but we need more details, in particular how the value in the field in the summary table is to be updated, based on the transaction type.  The general approach would be to iterate through the transaction table, and for each record, depending on the transaction type, update a field in the Summary table.  But which field?  Which record?  And how is it to be updated?
adk001Author Commented:
those are the questions.  i'm attaching what I'd really like to do here (as a screen-shot / image).  Unfortunately, it obviously won't work as the "Expr1" is not accepted as a field name.  the "XrefFieldName" field (in the PayrollItemXref table) contains the name of the field (in the ReformattedData table) that needs to be updated by this query.
Couldn't this be a crosstab query off of your Transaction detail?

The columns would be your TxnType and the value would just be a count of the IDNum (assuming that each record in the transaction table represents one transaction).

What are the "rows" in your summary?
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

adk001Author Commented:
No.  That only gives the total number of records for each "Department".  If it means anything, this data is originally coming from QuickBooks.  Unfortunately, Intuit doesn't really allow access to the payroll data, so this is using QODBC to get to the data.  Also unfortunately, QODBC doesn't even have access to the actual tables that contain the necessary data........they can only get to it via what they call SP_REPORT, which is effectively via SQL, constrained as "Read-Only" (meaning, it can't even be used in an update query - that updates a different table).  In any case, for each transaction (what I would call a single transaction), there are multiple records in the transaction record for each type of data in that transaction.  What I'm trying to do is to "build" a transaction table (what I'm calling ReformattedData) that contains only 1 record for each complete transaction, with all appropriate data contained in that record.  That way, it can be displayed / reported horizontally........such as 1 line per transaction.  Otherwise, being that a single transaction's data is actually contained in many records (such as 15 or 20 records), it's difficult to format reports in an intuitive way.  
Helen FeddemaCommented:
I think it could be done with a crosstab query, and then make an update query based  on the crosstab query, linking on the fieldname you got from the crosstab query.
adk001Author Commented:
that's what I don't know how to make an update query based upon the results of another query (linking on the fieldname obtained from the other query).  I know how to get the fieldname (as shown in my original query - see the attached picture in the 2nd posting for this Question).
So... you want to denormalize: transaction type in the transaction table corresponds to a field in your reformatted data table.  

First you need a way to identify what a transaction is... how do you know which 15-20 records pertain to "this" transaction.  Is that the RefNumber?

Assuming it is "refnumber" then I would build a query that first inserts "unique" refnumbers into the target reformatted data.  Then loop through the target reformatted data table, query the transaction table for all transaction records pertaining to that master transaction, then loop through those transactions and populate the reformatted fields.

I don't know about quickbooks ... but assuming you can link to the tables you should be able to manipulate them as "linked" and populate a local Access table where you keep your reformatted data.

If that sounds like it solves your problem I could give you some code that should do it... but I'll be in the dark without some test data... and a sample of what you expect the test data to look like in the target.
adk001Author Commented:
You're correct about RefNumber being the way to identify which records pertain to a single transaction.  The problem is that there are actually about 80 potential fields (80 potential record types for a single transaction).  What I'm trying to do here is avoid having to run 80 separate queries.......and come up with a way of running a single query that does exactly what you're suggesting.  What I don't know how to do is to utilize "data" (the result of a query.....whether that be a Crosstab Query or any other type of query) IN ANOTHER QUERY (in an Update query) order to know which field to update.  If you look at my screenshot (picture attached to the 2nd post in this Question), I believe it's pretty self-explanatory......although it obviously doesn't work the way I wish it would have worked.  

What I had hoped to do is to loop through the transaction records, populating things exactly as you have suggested.  What I don't know how to do is to tell the query (that does the populating) which field is being populated.  Basically, ALL of the dollar amounts (which is what I'm concerned with) are in the "Amount" field in the transactions.  It would be EASY if they were in separate fields for each type of data, but they're not.  Amount can be regular hourly wages, overtime wages, federal tax withheld, state tax withheld, etc. (about 80 different types).  The goal here is to learn how to populate the ReformattedData table with the data from the transaction table..........determining which field in the ReformattedData table by the result (data) from another query (or table).
Here's some code that should get you started... I've used some of the fields from your image as an example.  This code will loop through all of the unique "master" transactions (refnumbers)... then for each unique refnumber, it will loop through all of the fields in the reformatted table, query the transaction table for that refnumber and txntype -- I'm assuming that that will pull back either 1 record if the txntype is part of the transaction or no records if not (in other words that the txntype/refnumber combination define a unique record on your transaction table.

Also, I assume that the txntype is a text field ... if not then you'll need to remove the two "& Chr(34)" statements.

This will loop through however many fields you have on your reformatted table.

But this is very intensive.  If you have 80 fields in your reformatted table then this will run 80 queries for each refnumber... so... if you have 1000 unique refnumbers, you'll run 80,000 queries!!  So it could take a while...

Option Compare Database
Option Explicit

Sub PopulateReformattedTable()

    Dim db As Database
    Dim rs As Recordset
    Dim rsTxn As Recordset
    Dim sSQL As String
    Dim fld As Field
    Dim sTranTable As String
    Dim sReformatted As String
    sTranTable = "ImportedPayrollTransaction"
    sReformatted = "tblReformatted"
    'reset/empty tblReformatted
    sSQL = "delete * from " & sReformatted
    DoCmd.RunSQL sSQL
    'populate reformatted with unique RefNumbers
    sSQL = "INSERT INTO " & sReformatted & " ( RefNumber ) " & _
           "SELECT DISTINCT " & sTranTable & ".RefNumber " & _
           "FROM " & sTranTable
    DoCmd.RunSQL sSQL
    'open the Reformatted table to update with txn data
    Set db = CurrentDb
    Set rs = db.OpenRecordset(sReformatted, dbOpenTable)
    'loop through the reformatted table
    Do While Not rs.EOF
        'put the reformatted record in edit mode
        'loop through all of the fields for the current refnumber
        For Each fld In rs.Fields
            sSQL = "SELECT " & sTranTable & ".* " & _
                   "FROM " & sTranTable & " " & _
                   "WHERE (((" & sTranTable & ".RefNumber)=" & rs.Fields("RefNumber").Value & _
                   ") AND (" & "(" & sTranTable & ".TxnType)=" & Chr(34) & fld.Name & Chr(34) & "));"
            Set rsTxn = db.OpenRecordset(sSQL, dbOpenSnapshot)
            'if we find a txntype equal to the current field, update the reformatted field with txn values
            If Not rsTxn.EOF Then
                'for example, set the current txntype field to price times quantity
                fld = rsTxn.Fields("UnitPrice") * rsTxn.Fields("Quantity")
            End If
            If rsTxn.RecordCount > 1 Then
                'somehow we found a refnumber that has more than one of the same txntype -- should be
                MsgBox "RefNumber " & rs.Fields("RefNumber") & " has more than one txntype " & fld.Name
            End If
        Next fld
        'save the changes we've made to the reformatted record
        'go to the next record to reformat
    MsgBox "Complete"
End Sub

Open in new window

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
I forgot to mention a few things:

1.  you'll want to change the variable declarations:

sTranTable = "ImportedPayrollTransaction"
sReformatted = "tblReformatted"

to be the names of your transaction table and your 'reformatted' table -- I couldn't tell the full name from your picture.

2.  I only included the query to the transaction table and inserting into the reformatted table -- you had another table in your diagram that I didn't include.  If this basic model works, then enhancing the transaction based query to pull in data from another table is relatively straightforward.

3.  I didn't put any error handling and I didn't properly clean up the recordset declarations in my code snippet above.  If you think this is on the right track to solving your problem, I would give you an updated snippet to add the proper house-keeping.

Good luck.
adk001Author Commented:'re awesome!  I am still working on all of this, however, your THOROUGH answer has now given me the tools to be able to accomplish my task!  I'm actually going to loop through the transactions only once, then updating the reformatted record (one field at a time) during that pass.  In any case, you have provided exactly what I was looking example.  THANK YOU !
Great to hear you've got what you need!
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.