CrossTab report question

[billfindley]

I have a fairly simple crosstab query that I would like to use as the
basis of a report:

TRANSFORM Max(CStr([numberoffiles]) & "-" & [Comments]) AS Details
SELECT [D99-AuditHistory].Camera
FROM [D99-AuditHistory]
WHERE ((([D99-AuditHistory].FileDate)>=#4/30/2007# And ([D99-
AuditHistory].FileDate)<=#5/3/2007#))
GROUP BY [D99-AuditHistory].Camera
PIVOT [D99-AuditHistory].FileDate;

The query and report will always return only 3 dynamic columns (the
last three dates of historical info - hard coded into the query).

The report wizard works fine if I am willing to create a new report
each time the dates change (every run).

All I need to be able to do is set the "control source" property of my
three detail section text boxes to query.fields(1) / query.fields(2) /
query.fields(3) . This would solve the problem of the fieldnames
changing based on the query results.

I hope to set the column headings to query.fields(1) .NAME /
query.fields(2) .NAME / query.fields(3) .NAME.

I have tried many different syntax's for the control source, but have
not stumbled upon the right combination. Any ideas would be greatly
appreciated.

I am using Access 2007.

Thanks in advance.

[paii, Ron]

Work on your source query, replace [Filedate] with a calculated number of
days from the starting date. Add an "IN" clause after PIVOT to list each day
column in your report. In the report calculate the date from the starting
date and relative days.

<bi*********@ya hoo.comwrote in message
news:11******** **************@ l77g2000hsb.goo glegroups.com.. .

I have a fairly simple crosstab query that I would like to use as the
basis of a report:

TRANSFORM Max(CStr([numberoffiles]) & "-" & [Comments]) AS Details
SELECT [D99-AuditHistory].Camera
FROM [D99-AuditHistory]
WHERE ((([D99-AuditHistory].FileDate)>=#4/30/2007# And ([D99-
AuditHistory].FileDate)<=#5/3/2007#))
GROUP BY [D99-AuditHistory].Camera
PIVOT [D99-AuditHistory].FileDate;

The query and report will always return only 3 dynamic columns (the
last three dates of historical info - hard coded into the query).

The report wizard works fine if I am willing to create a new report
each time the dates change (every run).

All I need to be able to do is set the "control source" property of my
three detail section text boxes to query.fields(1) / query.fields(2) /
query.fields(3) . This would solve the problem of the fieldnames
changing based on the query results.

I hope to set the column headings to query.fields(1) .NAME /
query.fields(2) .NAME / query.fields(3) .NAME.

I have tried many different syntax's for the control source, but have
not stumbled upon the right combination. Any ideas would be greatly
appreciated.

I am using Access 2007.

Thanks in advance.

[billfindley]

Thanks for the advice Ron. I will look into your approach.
On May 4, 2:09 pm, "paii, Ron" <p...@packairin c.comwrote:

Work on your source query, replace [Filedate] with a calculated number of
days from the starting date. Add an "IN" clause after PIVOT to list each day
column in your report. In the report calculate the date from the starting
date and relative days.

<billfind...@ya hoo.comwrote in message

news:11******** **************@ l77g2000hsb.goo glegroups.com.. .

I have a fairly simple crosstab query that I would like to use as the
basis of a report:

TRANSFORM Max(CStr([numberoffiles]) & "-" & [Comments]) AS Details
SELECT [D99-AuditHistory].Camera
FROM [D99-AuditHistory]
WHERE ((([D99-AuditHistory].FileDate)>=#4/30/2007# And ([D99-
AuditHistory].FileDate)<=#5/3/2007#))
GROUP BY [D99-AuditHistory].Camera
PIVOT [D99-AuditHistory].FileDate;

The query and report will always return only 3 dynamic columns (the
last three dates of historical info - hard coded into the query).

The report wizard works fine if I am willing to create a new report
each time the dates change (every run).

All I need to be able to do is set the "control source" property of my
three detail section text boxes to query.fields(1) / query.fields(2) /
query.fields(3) . This would solve the problem of the fieldnames
changing based on the query results.

I hope to set the column headings to query.fields(1) .NAME /
query.fields(2) .NAME / query.fields(3) .NAME.

I have tried many different syntax's for the control source, but have
not stumbled upon the right combination. Any ideas would be greatly
appreciated.

I am using Access 2007.

Thanks in advance.- Hide quoted text -

- Show quoted text -