Very complex crosstab query question

  • Thread starter Thread starter Julia Boswell
  • Start date Start date
J

Julia Boswell

OK, here goes.... I've got a cross tab query that has row headings of Status
& Business, with column headings of Months and total of a value in the data
section. It's read via a report and it works fine and it looks something
like this:

Status Business Jan Feb Mar Grand Total
Anticipated Bus1 20 40 60 120
Anticipated Bus2 40 20 60 120
Anticipated Total 60 60 120 240
Backlog Bus1 20 40 60 120
Backlog Bus2 40 20 60 120
Backlog Total 60 60 120 240
AOP Bus1 etc
AOP Bus2 etc
AOP Total etc

I've then got a sub report which runs another cross tab showing the total
figures for Backlog and Anticipated.

The status and business unit names will never change. There will always be
three status' with those names and 2 businesses. The question is how on
earth do I do this?

Calculate the figures for (AOP totals) minus (the total of Backlog and
Anticipated) (which is shown in the sub report).

I guess I'll need to create another sub report, but I can't get my head
around how?

Any ideas appreciated!

Julia
 
It will be better running this through a Pivot Table Report in Excel,If you don't know how to do this,send me a reply and a Lay-out of the Table,rather than the Query and I'll help with this.You can also create a Link table to your Excel Spreadsheet within Access

----- Julia Boswell wrote: ----

OK, here goes.... I've got a cross tab query that has row headings of Statu
& Business, with column headings of Months and total of a value in the dat
section. It's read via a report and it works fine and it looks somethin
like this

Status Business Jan Feb Mar Grand Tota
Anticipated Bus1 20 40 60 12
Anticipated Bus2 40 20 60 12
Anticipated Total 60 60 120 24
Backlog Bus1 20 40 60 12
Backlog Bus2 40 20 60 12
Backlog Total 60 60 120 24
AOP Bus1 et
AOP Bus2 et
AOP Total et

I've then got a sub report which runs another cross tab showing the tota
figures for Backlog and Anticipated

The status and business unit names will never change. There will always b
three status' with those names and 2 businesses. The question is how o
earth do I do this

Calculate the figures for (AOP totals) minus (the total of Backlog an
Anticipated) (which is shown in the sub report)

I guess I'll need to create another sub report, but I can't get my hea
around how

Any ideas appreciated

Juli
 
Thanks Theo, I'm afraid I've briefly tried pivot tables and don't really get
them, the table layout is like this:

FieldName DataType
Business Unit Text
Value Number
Month Due Date/Time
Status Text

Is that what you need?

Julia

Theo said:
It will be better running this through a Pivot Table Report in Excel,If
Click to expand...
you don't know how to do this,send me a reply and a Lay-out of the
Table,rather than the Query and I'll help with this.You can also create a
Link table to your Excel Spreadsheet within Access.
 
I'm not sure what you want. Can you type more what you want to display like
you did for your main report?
 
Back
Top