Formatting Rows / Columns of a Report

[ghetto_banjo]

Ok i am having trouble wrapping my brain around this one.

I have a table, tblTotals, with Fields: Station, WeekEnd, Revenue.
(text, date, number respectively).

The WeekEnd date is always a Friday. Here is some example data:

ATL 1/9/2009 2000
BWI 1/9/2009 5000
LAX 1/9/2009 8000
ATL 1/16/2009 3500
....


I need to create a report that is similar to a CrossTab Query in
format. Not sure if that is possible.
So the Row Headers would be the Stations, and the Column Headers would
be the WeekEnd Dates.

i.e.

1/9/2009 1/16/2009
ATL 2000 3500
BWI 5000 7000


Is this possible to format in a report? I know I could, in theory,
use a DLookup function to grab each Revenue amount for the respective
Station and Date, but I don't know how to dynamically build the column
headers for the WeekEnd dates.

Any help would be appreciated. The CFO here really wants the data to
be in that format...

 

[Duane Hookom]

I have a solution for a dynamic monthly crosstab at
http://www.tek-tips.com/faqs.cfm?fid=5466. You should be able to easilty
adapt this to weekly.

 

[ghetto_banjo]

Thanks Duane. I actually found an old access 97 database example that
I believed you designed that shows how to do a dynamic report based on
a crosstab query. I am still figuring our a couple of things but this
is really what i need! thanks again!

 

[Marshall Barton]

I have a table, tblTotals, with Fields: Station, WeekEnd, Revenue.
(text, date, number respectively).

The WeekEnd date is always a Friday. Here is some example data:

ATL 1/9/2009 2000
BWI 1/9/2009 5000
LAX 1/9/2009 8000
ATL 1/16/2009 3500

I need to create a report that is similar to a CrossTab Query in
format. Not sure if that is possible.
So the Row Headers would be the Stations, and the Column Headers would
be the WeekEnd Dates.
i.e.
1/9/2009 1/16/2009
ATL 2000 3500
BWI 5000 7000

Is this possible to format in a report? I know I could, in theory,
use a DLookup function to grab each Revenue amount for the respective
Station and Date, but I don't know how to dynamically build the column
headers for the WeekEnd dates.

You only need to use a crosstab query. Instead of pivoting
on the week end date, calculate a relative week number using
an expression like:
"W" & DateDiff("w", WeekEnd, FirstWeekEndDate))

Then the column header labels can be changed to text boxes
with expressions like:
=FirstWeekEndDate + 0
=FirstWeekEndDate + 7
=FirstWeekEndDate + 14
. . .
And the detail section can refer to the 53 fields using the
column names W0,W1, ...,W53

Where you specify the starting week end date is what you
would use to replace my place holder name FirstWeekEndDate.
E.g. if you use a text box on a form to enter the starting
week end date, replace FirstWeekEndDate with something like:
=Forms!theform.thetextbox

 

[Duane Hookom]

But I'm older and wiser now ;-)