[RESOLVED] MS Access query with unknown field

[LinXG]

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

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

[si_the_geek]

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?

[TysonLPrice]

Do you mean using a variable like:

strSomeField = "MyColumn"

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

[jggtz]

It's possible using VBA

[LinXG]

Hi Si;

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

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

[TysonLPrice]

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

[si_the_geek]

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.

[LinXG]

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

[si_the_geek]

The query you want would be something like this:

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

[LinXG]

Thanks Si... Your suggestions worked perfectly.