crosstab generation

[Guest]

newbie.

So I've gotten so far as to programatically generate a
querydef - xresources - based on some form inputs, and
then a crosstab query based on that - xresources_crosstab.

code below.

wondering now how I'd go about generating a non permanent
crosstab query based on a non permanent query and then
popping up the reults from the crosstab query so that the
user makes their selection, hits go, and the results from
the crosstab query are displayed.

Alternatively i could write permanent queryies, open the
crosstab query programatically, then delete the queries
on close.

Prefer the non permanent approach though. Any ideas?
tia, jb

Private Sub Go_Click()
Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim rs As DAO.Recordset
Dim strSQL As String
Dim NextDate As Date

Set db = CurrentDb()

'set the string for the query on which the crosstab
query is based

strSQL = "SELECT [jobs].[job_name], [jobs].
[flexability], [activities].[priority], [workdays].
[date], [workdays].[days_required], [workers].[worker]
FROM workers INNER JOIN ((jobs INNER JOIN activities ON
[jobs].[job_id]=[activities].[job_id]) INNER JOIN
workdays ON [activities].[activity_id]=[workdays].
[activity_id]) ON ([workers].[worker_id]=[activities].
[helper_3]) Or ([workers].[worker_id]=[activities].
[helper_1]) Or ([workers].[worker_id]=[activities].
[helper_2]) Or ([workers].[worker_id]=[activities].
[in_charge])WHERE [workers].[worker_id] = " & Me!worker
& " AND workdays.date Between #" & Me!startrange & "# AND
#" & Me!EndRange & "# ORDER BY [workdays].[date]"

Set qd = db.CreateQueryDef("xresources", strSQL)
'create the foundation query def

'set the sql string for the crosstab query with
references to xresources

strSQL = "TRANSFORM Sum(xresources.days_required) AS
SumOfdays_required SELECT xresources.worker,
xresources.job_name, Max(xresources.priority) AS
MaxOfpriority, Min(xresources.flexability) AS
MinOfflexability FROM xresources GROUP BY
xresources.worker, xresources.job_name PIVOT Format
([date],'Short Date') In (" & Me!startrange

'still writing the sql string here, adding date range
columns based on user input

NextDate = Me!startrange
Do While (NextDate < Me!EndRange)
NextDate = DateAdd("d", 1, NextDate)
strSQL = strSQL & ", " & NextDate
Loop
strSQL = strSQL & ");"

'create the permanent crosstab query
Set qd = db.CreateQueryDef("xresources_crosstab",
strSQL)
End Sub

 

[this ? poster]

macros - pretty sweet.