How to create crosstab report with dynamic fields,Help

[elena]

Hi, All
I have crosstab query (all credits on that to Duane Hookom), i need to
create report
with dynamically created fields, the output of the query has unknown amount
of fields MTime1, MTime2... MTime5 ,
How can i set data source to unbound textbox, if sometimes i have only one
or two fields of MTime?
Please, help
query:
TRANSFORM First(t.MarkTime) AS FirstOfMarkTime
SELECT t.MarkDate, t.Location, t.Plate
FROM t
GROUP BY t.MarkDate, t.Location, t.Plate
PIVOT "MTime" & DCount("*","t","Location='" & [Location] & "' AND
MarkDate=#" & [MarkDate] & "# AND MarkTime <=#" & [MarkTIme] & "#");

 

[Duane Hookom]

If you have a maximum number of times, you can use
TRANSFORM First(t.MarkTime) AS FirstOfMarkTime
SELECT t.MarkDate, t.Location, t.Plate
FROM t
GROUP BY t.MarkDate, t.Location, t.Plate
PIVOT "MTime" & DCount("*","t","Location='" & [Location] & "' AND
MarkDate=#" & [MarkDate] & "# AND MarkTime <=#" & [MarkTIme] & "#") IN
("MTime1","MTime2","MTime3",...."MTimeX");

 

[elena]

Thank you once again, it works the way i needed!

If you have a maximum number of times, you can use
TRANSFORM First(t.MarkTime) AS FirstOfMarkTime
SELECT t.MarkDate, t.Location, t.Plate
FROM t
GROUP BY t.MarkDate, t.Location, t.Plate
PIVOT "MTime" & DCount("*","t","Location='" & [Location] & "' AND
MarkDate=#" & [MarkDate] & "# AND MarkTime <=#" & [MarkTIme] & "#") IN
("MTime1","MTime2","MTime3",...."MTimeX");

--
Duane Hookom
Microsoft Access MVP

Hi, All
I have crosstab query (all credits on that to Duane Hookom), i need to
create report
with dynamically created fields, the output of the query has unknown amount
of fields MTime1, MTime2... MTime5 ,
How can i set data source to unbound textbox, if sometimes i have only one
or two fields of MTime?
Please, help
query:
TRANSFORM First(t.MarkTime) AS FirstOfMarkTime
SELECT t.MarkDate, t.Location, t.Plate
FROM t
GROUP BY t.MarkDate, t.Location, t.Plate
PIVOT "MTime" & DCount("*","t","Location='" & [Location] & "' AND
MarkDate=#" & [MarkDate] & "# AND MarkTime <=#" & [MarkTIme] & "#");