On the Fly Report based on a variable crosstab query

[gwoman]

I have a crosstab query that displays a given years worth of production averages by month based on a date entered by the user in a form textbox.
The Report would have to be created on the fly as I have failed at attempting to create a report with the crosstab query with a variable as a source. I tried to display it as a subform in datasheet view and make it look like a report but Access won't let me use a crosstab query with a variable as a record source for a form either.
Does anyone know of a way to make this happen?

Thanks
gwoman

 

[randysmid]

Hi,
Yes, this is entirely possible, but you will need one or more queries to make it all happen. The first query will use your variable to select the core set of records. Your second query (presumably your cross tab) will use the result set of the first query to generate your on-the-fly report.
Make sense?
HTH, Randy

 

[GingerR]

If your report will always report on a whole year, by month, then you could set the Column Headings of the query to something like "JAN", "FEB", "MAR", etc. then put those right into the report. Then it will work to use a crosstab query in a report.

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

 

[MichaelRed]

There are a couple of threads in these fora (Tek-Tips) which discuss and illustrate how to generate a report bases on an arbitrary recordset. Use search with the term(s) "CreateReport" and / or "CreateReportControl". The threads I am famioliar with are, perhaps, not for the faint of heart and certainly would need some elaboration for all but the most simplistic report / recordset requirements. The redeaming feature is that (once understood) it is easily applied to a diversity of applications, and at leaset would easily build a prototype report for you to further customize.



MichaelRed

 

[dhookom]

gwoman,
If you are using months as column headings, check the FAQs for this forum to find faq703-5466.

When you ask a crosstab question, it is generally a good idea to either post the sql view or at least tell us about your column and row headings.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[gwoman]

Thanks to all of you ... I appreciate your responses.
My crosstab query was based on another query that previously selected the record set.
The column headings are not the typical Jan-Dec layout ... the beginning month would be whatever month the user indicated in the form text box (i.e. if the user's input was Aug. 1990 ... then the column headings would begin with Aug. 1990 and go through July 1991).
I did a search for threads prior to posting this ... but
I will do a more intense search in the FAQ's and the previous posts.
Thanks again everyone ...

gwoman

 

[dhookom]

It seems to me the FAQ that I suggested meets your needs.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]