Queries and Multi-Column Reports

[Devlin]

This question was previously posted in the reports section. However, we
thought we might get better response here in queries.

We are seeking a best way to develop a query or queries and corresponding
report that involves multiple columns across and includes sub columns. The
report must look something like this:

Year: 2000 | Year: 2001
Accts Crd Dbt PrvBal Bal | Crd Dbt PrvBal Bal
---------------------------------------------------------------
Acct01 12 10 8 10 | 20 10 10 20
Acct02 ....
....

In this example only two years are displayed. However, in the report it is
required that three years be displayed on one page. Furthermore, there may,
and most likely will be more than three years of records to display.
Therefore, the report must again, display three years per page, and span
multiple pages.

The best way we have come up with to do this is to use a temp table.
However, we foresee that this could be troublesome in a multi-user
environment.

Another hurdle that we face in this task is in some cases, some accts may
need to be grouped. Grouped accts are defined in a separate table from the
actual accts table. To overcome this hurdle we are using a union query to
gather the individual and group values combined. This query looks up the
credits and debits. We then programmatically look up the prev and current
balance and update the table records.

An example of our table is defined below:
Year1, Year2, Year3, Cred1, Cred2, Cred3, Dbt1, Dbt2, Dbt3, PrvBal1,
PrvBal2, PrvBal3, Bal1, Bal2, Bal3

If anyone knows of a better way to tackle this challenge, any help would be
greatly appreciated!

 

[Michel Walsh]

Do it with a temp table, but have the table LOCAL, in the FRONT END, NOT in
the back end, so the multi-users environment won't be a problem.

You can also do a crosstab, either 4 of them you finally join, either using
a cross join with a table having 4 records, Iotas4, one field, iota, with
value from 1 to 4:


TRANSFORM SUM(CHOOSE(iota4.iota, cdr, dbt, prvBal, bal))
SELECT account
FROM myTable, Iota4
GROUP BY account
PIVOT Year(dateTimeStamp) & "-" &
CHOOSE( iota4.iota, "Cdr", "Dbt", "PrvBal", "Bal")



Hoping it may help,
Vanderghast, Access MVP