0

I'm in the process of converting an Excel 'database' into an Access database.

I have two tables - DataFromExcel and tbl_AuditResults.
DataFromExcel has these fields: AutoNum (PK),1,1 Comment,2, 2 Comment,.... 19,19 Comment
tbl_AuditResults has these fields: ID (PK), Param (PK), Result (Boolean), Notes

DataFromExcel has 19 fields for each ID (and related Comment field),
tbl_AuditResults has 19 records for each ID and a Notes field.

I'm looking to update the Result & Notes fields to show TRUE and the comment if the relevant DataFromExcel field contains the number 1.

I have this SQL which works on a field by field basis, but I have to update it for each field (the example is for field 4). 

UPDATE  tbl_AuditResults INNER JOIN DataFromExcel ON tbl_AuditResults.ID = DataFromExcel.AutoNum
SET     Result = TRUE, Notes = [4 Comment]
WHERE   DataFromExcel.[4] = 1 AND tbl_AuditResults.Param = 4

Does anyone know how I could do it all in one pass?

Darren Bartrup-Cook
  • 18,362
  • 1
  • 23
  • 45

1 Answers1

0

You will be updating different sets of data for different fields, if I understand it correctly - the WHERE clause will be different.

Therefore the answer is no.

But you can loop over field numbers 1..19, build the respective SQL in VBA, and then run each one with DB.Execute.

Edit: the scenario sounds a bit like a case for UNPIVOT, but since Access doesn't have this natively, the loop will be much easier.

Community
  • 1
  • 1
Andre
  • 26,751
  • 7
  • 36
  • 80
  • I had a feeling it may need a VBA solution, but wanted to check with the rest of the world first. I'd also thought it looked a lot like a reverse pivot table - didn't know it existing in other databases. I'll have a look at the solution provided in your link as well - looks like it may work as well. – Darren Bartrup-Cook Feb 25 '16 at 17:21