Crosstab Query to produce a report with row field with multiple s.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

D_BGuy - 03 Feb 2008 02:46 GMT

I'm trying to mimic an excell spreadsheet type summary report with a crosstab
query that has a project number in the first row, SumBudgeted Rev,
SumProjected Rev, SumActual Rev in the second row that belong to that project
number and the Columns are Dates in "mm-yyyy" format. The value field is
Actual Rev minus Budgeted Rev divided by Budgeted Rev. The object is to show
monthly totals of revenues per project number with a calculated column at the
very end in percent. Sorry about the display name all of the good ones are
taken.

Reply to this Message

Duane Hookom - 03 Feb 2008 06:16 GMT

There is a solution for a monthly crosstab report at
http://www.tek-tips.com/faqs.cfm?fid=5466.

If you can't get this to work, please come back with some sample records and
desired output.

Signature

Duane Hookom
Microsoft Access MVP

> I'm trying to mimic an excell spreadsheet type summary report with a crosstab
> query that has a project number in the first row, SumBudgeted Rev,
[quoted text clipped - 4 lines]
> very end in percent. Sorry about the display name all of the good ones are
> taken.

Reply to this Message

D_BGuy - 03 Feb 2008 13:48 GMT

Here is what the report needs to look like. I have the Project # and the
Date part figured out but not the 3 totals and title fields that go to each
project # here is some sample data

        Jul    Aug    Sep    Oct     Nov    Dec    Totals
1111.11    Budget $19,080.00 $10,800.00     $19,440.00 $45,360.00 $30,240.00
    Projected    $-     $-     $-     $41,670.00 $44,280.00
    Forecast    $-     $-     $-     $45,360.00 $30,240.00
2222.22    Budget    $6,480.00 $7,560.00 $6,480.00 $7,560.00 $5,040.00
    Projected    $6,840.00    $8,280.00 $6,120.00 $7,740.00 $7,560.00
    Forecast    $6,840.00 $8,280.00 $6,120.00 $7,560.00 $5,040.00
3333.33    Budget    $19,440.00$22,680.00$19,440.00 $-     $-     $-
    Projected    $21,240.00 $20,160.00 $17,640.00     $-     $-
    Forecast    $21,240.00 $20,160.00 $17,640.00     $-     $-

> There is a solution for a monthly crosstab report at
> http://www.tek-tips.com/faqs.cfm?fid=5466.
[quoted text clipped - 10 lines]
> > very end in percent. Sorry about the display name all of the good ones are
> > taken.

Reply to this Message

Duane Hookom - 03 Feb 2008 20:21 GMT

I have no idea what your original data looks like. Is your table structure
normalized so that Budget, Projected, and Forecast are all values stored in a
single field?

Signature

Duane Hookom
Microsoft Access MVP

> Here is what the report needs to look like. I have the Project # and the
> Date part figured out but not the 3 totals and title fields that go to each
[quoted text clipped - 25 lines]
> > > very end in percent. Sorry about the display name all of the good ones are
> > > taken.

Reply to this Message