How to create crosstab report with dynamic fields,Help

G

Guest

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] & "#");
 
G

Guest

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");
 
G

Guest

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

Duane Hookom said:
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


elena said:
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] & "#");
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Format Time in the query result 1
Crosstab query Help 4
Dynamic column in report using crosstab query 20
Example of a crosstab report with dynamic column headings 1
Crosstab Query with query parameter used to create a report 2
Need to create a report using a crosstab query 2
Help to create a specific format for report 1
Crosstab Report with Dynamic Fields 2

Top