[RESOLVED] MS Access query with unknown field

Hi;

Is it possible in MS Access to create an update query where the field being updated is determined at the time the query is run? For example...

Code:

---

update myTable set [someField] = 'ABC';

---

In this example I would like to use a lookup table to set the field name (someField) used in the query.

Is this possible?

Thanks
Lin

I don't think so... and I can't think of a reason why you would want to, can you give a bit more detail about that?

Do you mean using a variable like:

strSomeField = "MyColumn"

update myTable set & " strSomeField" & " = 'ABC'";

It's possible using VBA

Hi Si;

I have a table that looks something like this ...

Code:

---

ID      Category    Period_1    Period_2    Period_3  Period_4  Period_5  Period_n
1        A              22          18       
1        B              30          16
1        C              12          34
2        A              12          44
2        B              88          20
2        C              17          15

---

As an example I would like to create a stock query that updates Period_? for ID-1, Category-A. At the moment I edit the query each time I run it which is a bit tedious.

Hopefully my example is clear enough to show you what I'm attempting and any advice or suggestions are greatly appreciated.

Thanks
Lin

Maybe you could create a stored procedure that updates based on what you pass it...

I had suspected it was something like that, and I'm afraid it is not a good table design - hence why this kind of thing is not easy.

Based on the data you showed, the way I would have the table is 4 fields (ID, Category, PeriodNumber, and PeriodValue), with one row of data for each period.... that would then allow your query to specify a value (for PeriodNumber) rather than a field. If the period values are often blank (as implied by your example), it would also reduce the file size.

Hi Si;

Yes, I suspected that you'd suspect the table wasn't a great design. I'm still experimenting with this. The table is being used as the basis for a report that displays what activities took place in a given time period. As long as I'm willing to change the field name each time I do an update it will work but still not a great table design

I will create a new table that matches what you've suggested and then try to figure out a query that displays the information as I've shown it in table.

Thanks
Lin

The query you want would be something like this:

Code:

---

SELECT ID,
      Category,
      Sum(If(PeriodNumber=1,PeriodValue,0)) as Period_1,
      Sum(If(PeriodNumber=2,PeriodValue,0)) as Period_2,
      ...
FROM myTable
GROUP BY ID, Category

---

Thanks Si... Your suggestions worked perfectly.