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.
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.