Switch horizontal and vertical

[Todd K.]

I'm working on an audit report and I need to do one of two things:
1) switch the horizontal and vertical axes so that what would normally list
the funds in the detail section will now list the funds across the top

OR

2) figure another way to find the next fund number in the header section
(ie....

header: Fund 1 Fund 2 Fund(next) Fund(next)
detail: Amt 1 Amt 2 Amt (next) Amt (next)

 

[Duane Hookom]

Share your table(s) structure, some sample records, and how you want them to
appear in your report.

 

[Todd K.]

Table "TblRecords" includes fields [Fund] (1,2,13,18, etc), [ObjectCode]
(4300, 4301, 4450, etc) and [Amount] (dollar amount). The audit report is
required to be in this format:

Fund1 Fund 2 Fund13...
Code
4300 $25 $150 $300
4301 $10 $200 $150
4450 $5 $201 $700

The problem I'm having is finding the next fund (ie "13" instead of "3")

 

[Duane Hookom]

The crosstab report download at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane creates
reports like you are requesting.

--
Duane Hookom
Microsoft Access MVP

Table "TblRecords" includes fields [Fund] (1,2,13,18, etc), [ObjectCode]
(4300, 4301, 4450, etc) and [Amount] (dollar amount). The audit report is
required to be in this format:

Fund1 Fund 2 Fund13...
Code
4300 $25 $150 $300
4301 $10 $200 $150
4450 $5 $201 $700

The problem I'm having is finding the next fund (ie "13" instead of "3")

Share your table(s) structure, some sample records, and how you want them to
appear in your report.

 

[Todd K.]

Very interesting, I had never used a CrossTab query before but it's exactly
what I need. I couldn't find your loop that assigned the letters A-H to the
customers though. How did you do that part?

The crosstab report download at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane creates
reports like you are requesting.

--
Duane Hookom
Microsoft Access MVP

Table "TblRecords" includes fields [Fund] (1,2,13,18, etc), [ObjectCode]
(4300, 4301, 4450, etc) and [Amount] (dollar amount). The audit report is
required to be in this format:

Fund1 Fund 2 Fund13...
Code
4300 $25 $150 $300
4301 $10 $200 $150
4450 $5 $201 $700

The problem I'm having is finding the next fund (ie "13" instead of "3")

Share your table(s) structure, some sample records, and how you want them to
appear in your report.

--
Duane Hookom
Microsoft Access MVP


:

I'm working on an audit report and I need to do one of two things:
1) switch the horizontal and vertical axes so that what would normally list
the funds in the detail section will now list the funds across the top

OR

2) figure another way to find the next fund number in the header section
(ie....

header: Fund 1 Fund 2 Fund(next) Fund(next)
detail: Amt 1 Amt 2 Amt (next) Amt (next)

 

[Todd K.]

Duane - I think I just discovered a better solution (borrowing heavily from
your example, but without the crosstab query).

1) I used a grouping query to create a table (TblTEMP) of just the funds in
numeric order
2) I used SQL to add a counter (autonumber) to the table, so the first fund
is autonum 1, the second is autonum 2, etc.
3) For the report, the query joins the original table and the TblTEMP table
by fund
4) For the headers, I just do a lookup in the tblTemp table for as many
columns as I may need (=DLookUp("[Fund]","TblTEMP","[Autonum]=1"),
(=DLookUp("[Fund]","TblTEMP","[Autonum]=2"), etc.

It works great and it runs lightning quick.

- Todd K.

The crosstab report download at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane creates
reports like you are requesting.

--
Duane Hookom
Microsoft Access MVP

Table "TblRecords" includes fields [Fund] (1,2,13,18, etc), [ObjectCode]
(4300, 4301, 4450, etc) and [Amount] (dollar amount). The audit report is
required to be in this format:

Fund1 Fund 2 Fund13...
Code
4300 $25 $150 $300
4301 $10 $200 $150
4450 $5 $201 $700

The problem I'm having is finding the next fund (ie "13" instead of "3")

Share your table(s) structure, some sample records, and how you want them to
appear in your report.

--
Duane Hookom
Microsoft Access MVP


:

I'm working on an audit report and I need to do one of two things:
1) switch the horizontal and vertical axes so that what would normally list
the funds in the detail section will now list the funds across the top

OR

2) figure another way to find the next fund number in the header section
(ie....

header: Fund 1 Fund 2 Fund(next) Fund(next)
detail: Amt 1 Amt 2 Amt (next) Amt (next)