Report from cross-tab

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

TD11 - 14 Oct 2004 17:23 GMT

I create a table that contains sales data from our different stores. The
table will contain data from different stores depending on the limits applied
when the table is built.

I want to use a cross-tab query to display the data in a comparative layout
with each store being a unique column. Because the data in the table can
change, so can the column headings in my cross-tab.

How can I write a single report that will display the results of the
cross-tab?

Reply to this Message

Allen Browne - 14 Oct 2004 18:01 GMT

Provide enough columns on the report the the maximum number of stores you
envisage ever needing.

Save the report with no RecordSource, and nothing in the Control Source of
your text boxes.

In the Open event of the report, open a recordset into your lookup table of
stores. Use that to build a SQL statement string, and assign to your
report's RecordSource. Then assign the ControlSource of the text boxes as
well, keeping the unused ones hidden, and adjusting the Width and Left
properties so they are spaced appropriately across the page.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

>I create a table that contains sales data from our different stores. The
> table will contain data from different stores depending on the limits
[quoted text clipped - 8 lines]
> How can I write a single report that will display the results of the
> cross-tab?

Reply to this Message