How can I create an Excel-Style Grid report? 1

[nonturbo]

I am using Access 2002 (XP) and am not a complete newbie, yet I feel like I'm always learning new things when it comes to Access..

I've got a simple table with these 4 fields:
- ID (Autonumber)
- Year (ie: 1999, 2000, etc)
- Quarter (can ONLY be 1,2,3 or 4)
- Return (a decimal, ie: 0.0131, which gets converted to a percentage)

I'd like to setup my report so that it will look similar to this:<code>

1 2 3 4
------------------------------------------------------
1998 | 3.12% 5.91% 6.42% 1.27%
1999 | 4.32% 1.35% 1.24% 1.45%
2000 | 6.25% 5.54% 6.45% 4.45%
2001 | 1.23% 1.53% 4.67% 7.23%

</code>

...Understand? Please help! Thanks!
-NT

 

[d1004]

This is how I do to look exactly like an Excel spreadsheet. If you don't have Access toolbox already, then go to View/Toolbox (I'm assuming Access 2002 has the same toolbox as 2000). Draw a box and a couple of horizontal & vertical lines to make it look like an Excel spreadsheet. Then for the every cell, just plug in the field. That take care of the data part, For the year label, just use the field label that you drag into the report. For header, use the label toolbox icon to make new ones. HOpe it helps.

D1004

 

[sko]

Base your report on a crosstab query where...

Crosstab Row Heading is Year
Crosstab Column Heading is Quarter
Crosstab Value is Return, Group by is Sum, Format is %

Create AutoFormat Tabular report using the wizard.
To add a grid, change the control border style property to solid and size accordingly.

HTH

 

[nonturbo]

sko: Worked like a charm! That's EXACTLY what I was looking for. Thanks. You deserve a star!

Setting Format as % is done where, in the Query or on the report's Return control? If in the Query, HOW?

 

[sko]

To set the format in the query, right-click on return field, select properties and format percent.

Field formatting in the query is inherited from the table field. If it's modified in the query, forms and reports based on the query should inherit the query formatting. I've had instances where this doesn't happen. Not sure why.