newbie report question

[KellyK]

hi all. i'm sure to you experienced folks this is a real newbie question but for me it's giving me a huge headache. i need to create a report showing various revenue components for our field offices. the problem is that i need every office on the same report. for instance I want something like this:

Atlanta Chicago Cincinnati ....
Rail Pay $3,000 $4,000 $5,000
Broker Pay 5,000 6,000 7,000
...

if i add groupings for City, it ends up printing each city on a separate page. but i don't exactly want a crosstab query because Rail Pay, Broker Pay, etc. are all calculated from the underlying select query and there are simply too many calculated fields to make sense enough to create a query. I want to base the report on my raw data (the select query) but when I try to enter SQL into the text boxes, they show as #name? on the report. My SQL looks something like "SELECT [BrokerRev] where [City] = 'Atlanta'. I have Msowcf.dll installed so this is not the problem. Also, when I tried to create a dummy crosstab query, I got the error "the microsoft jet database engine does not recognize '[forms]![fmnuMain]![cboMonth]' as a valid field name or expression" because my select query uses embedded queries which pull the month from my main form. Is there a way to get the SQL to work or am I really going to have to create a complex query with 100+ calculations to get it to show on a report? Thanks in advance for help.

 

[mrf1xa]

I'm not 100% sure I;ve understood what you want and am far from being an expert myself, but a couple of comments that may help:
'if i add groupings for City, it ends up printing each city on a separate page.'- this should not happen unless Access has been instructed to do it. Look at the sorting/grouping for your report- if you add a geoup header for city there is an option below this for forcing a new page or not...

As for the date problem, I often have this problem with crosstabs etc and have got around it in the past by using a maketable query, with your date criteria in it, to create a temporary table on which you can then base your other queries and calculations.

Hope this helps a bit

Nigel

 

[AnalystDBA]

Hi Kelly,

This sounds complex.
Can you email me your .mdb file and I will see what I can do.
I create reports like this all the time.

Cheers,
AnalystDBA
alc_consulting@optusnet.com.au

 

[KellyK]

Thank you very much Nigel and AnalystDBA! I've decided to populate each of the fields on the report through VBA code. I can't change the values of text boxes and such for a report at runtime (or can you and I'm missing something?) so I decided to create a form and populate the fields using recordsets based on my original query. It doesn't look quite as slick as a nice report, but since I'm getting a working product I'm going with it until I develop my Access skills a little more. Thanks for the help!