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

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?

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.
ScreenShot141.jpg

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?
Crosstab.bmp

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 table.......one 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.  

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.

that's what I don't know how to do...........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.

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)......in 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).

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.

answer_dude.....you'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 for...........an example.  THANK YOU !

Great to hear you've got what you need!