Query with Dynamic # of Columns 2

[accessjack]

Does anyone know how to create a select query to display a dynamic # of columns from a underlying query.

I have a table called <tblMatrix> with 13 fields/columns:
Account 01 02 03 04 05 06 07 08 09 10 11 12
where columns 01 thru 12 represent months with financial data or null values. I know this data isn't normalized, and normalization is not an option.

Using a form called <frmSelection> the user picks a beginning month <cboStart> and ending month <cboEnd> and a button on the form will run vba code that returns all data from the underlying query <qryData> but should only display fields columns between the user selections. For example, if the user chooses 02 & 05, I'd expect the query to return the following:
Account 02 03 04 05 --> 5 columns that exclude the rest. The columns are choosen on the fly so I can't create a fixed query.

Any ideas,
Thanks,
Access Jack

 

[RivetHed]

Something like this?

Dim db As Database
Dim QDef As QueryDef
Dim strSQl As String
Dim i As Integer

Set db = CurrentDb
Set QDef = db.CreateQueryDef("MyQuery")

strSQl = "SELECT Account"

For i = cbostart To cboend
strSQl = strSQl & ", 0" & i
Next i

strSQl = strSQl & " FROM tblmatrix;"

QDef.SQL = strSQl

DoCmd.OpenQuery QDef.Name

set qdef = nothing
set db = nothing

 

[GingerR]

You could make a UNION query to "normalize" it on the fly:

Select [Account], "01" as AccountMonth, [01] as Amount from tblMatrix UNION
Select [Account], "02", [02] from tblMatrix UNION
Select [Account], "03", [03] from tblMatrix UNION
Select [Account', "04", [04] from tblMatrix UNION
...etc...

Then use this query as your basic query that's "normalized" for other calculations.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[accessjack]

Thanks RivetHead,

This is close. I have 2 issues: For month for 2 digits 10, 11, & 12, the sql code makes them 010, 011, 012.

Also, for some reason I keep getting an error on the line
QDef.sql = strSQl or perhaps the next line.
It says error in from clause. I used a breakpoint and the sql string looked good to me.

Any ideas on these issues?
Jack

 

[GingerR]

For i = cbostart To cboend
if i < 9 then '1 digit
strSQl = strSQl & ", 0" & i
else '2 digits
strSQL = strSQL & "," & i
end if
Next i


go debug.print strSQL and send us the sql statement

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[accessjack]

Thanks Ginger, it worked. I modified slightly, I had to add brackets to the fields names as it seems access doesn't like it.

For i = cbostart To cboend
If i < 10 Then
strSQl = strSQl & ", [0" & i & "]"
Else
strSQl = strSQl & ", [" & i & "]"
End If
Next i

One other thing I noticed is the code can only be run once unless I manually delete the <MyQuery> which is created from the code line below.

Set QDef = db.CreateQueryDef("MyQuery")

When I try to reexecute via the form it says object myquery already exists. I am assuming the set qdef to nothing never get executed because as soon as the docmd line runs, the query results are displayed and that ends the code....but I'm not sure.

Thanks,
accessjack

 

[randy700]

How about...

db.QueryDefs.Delete "MyQuery"

Randy

 

[JerryKlmns]

Don't give your query a name

Set QDef = db.CreateQueryDef("", strSQl )

The temporary created query is not saved thus no need to delete it!

 

[buon]

My friend I dont know how to go to forum., but I,d to ask some question. I;m developing some database but I need to automaticaly fields the month of the payment for all workers in a form without some one do it one by one