using input from combobox to decide which column to select in a query

[ankita]

Hi,

I have setup an Access database to run a report each month. However, the calculations are a little complex and will change with each month.

What I have done is created a form which asks the user to put in the month number.

So, now I have for example, two tables T1 and T2. Both of them have 13 columns each (one main field and 12 month columns with values). The 12 month columns have names like T1_Jan, T1_Feb.... & ..... T2_Jan, T2_Feb...etc.

Now I want to use VBA to run the query that interacts between these two tables, but the column name should be variable depending on the user input in the combobox.

Can anyone suggest the best way of doing this??

 

[CJ_London]

best way would be to normalise your data. Instead of your table looking like this

MainField, T1_Jan, T1_Feb....
xxx..........12.00....150.44....

it should look like

MainField...TType....TMonth...TValue
xxx...........T1.........Jan........12.00
xxx...........T1.........Feb.......12.00

 

[ankita]

That was my first thought. But the data cannot be managed like that (or it will include a lot more complications).

If only I can figure out to run a select statement through VBA, this will be done. Can you help me with that code?

 

[CJ_London]

create a union query to bring your data into the normalised structure

alternative in VBA you would need to have something like


sqlstr="SELECT mainfield, T1_" & choose(me.mthnumber,"Jan", "Feb"....) & " FROM tblT1"

assumes mthnumber 1=Jan, 2=Feb etc

 

[Uncle Gizmo]

best way would be to normalise your data. Instead of your table looking like this

MainField, T1_Jan, T1_Feb....
xxx..........12.00....150.44....

it should look like

MainField...TType....TMonth...TValue
xxx...........T1.........Jan........12.00
xxx...........T1.........Feb.......12.00

If you want to normalise as suggested by Chris then there is free software available on my blog that can do this, along with written and video instructions. See this page here:- "Excel in Access"

 

[ankita]

Thank you for your help.
But I want the code in VBA that will help me execute the sql.
The dynamic sql i can write and call it, say, s (as string).

Now i am unable to run it through vba.
I am only able to find codes on docmd (update, delete or make table queries).

 

[CJ_London]

am only able to find codes on docmd (update, delete or make table queries).

google 'access vba openrecordset'

But the data cannot be managed like that (or it will include a lot more complications).

Ok - so you have different complications trying to manipulate the data.