Base a report on SQL

[desperatemeasures]

I need to create a report that is based on sql due to the fact that there are many different criteria for each line on the report. How would you base the individual fields on the report on sql, would you put the sql in the control source of each field?

 

[JeremyNYC]

Nope, just make the controls like you would if it were based on a query or table. They'll pick up their values correctly.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.

 

[desperatemeasures]

Thanks for that, but that wont work as I cant base the report on one query, each line on the report (Its a summary report) is based on a different query. I wanted to move away from basing the fields on a query, by using SQL in the control source, as there are too many queries to work with now (about 10).

 

[JeremyNYC]

I have a report like this. It's one record and a couple dozen fields (maybe more). Because each of the individual queries only produces a single record, I just make another query that includes each of the other queries, with no relationships between the queries. It shows all the fields from each of the queries.

Not sure if this will work for you, but if you can make your queries all produce only a single record, it should.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.

 

[desperatemeasures]

This is the way I had it, but it has to change as there are different criteria for each customer, so I have a summary report (like a cross tab report/query) where each line and some fields within each line are based on different criteria. I just think it would be easier to code it in SQL, than have 10-15 queries used.

 

[BSman]

I have found this to work well:

I'm assuming that all (or most) of the data is contained in your query, but how you handle the data will vary for each customer.

Base your report on a query that provides all of the data.

Have a grouping level (by customer?) that will be the first place that anything will actually be displayed on the report.

In the detail section (which will have the visible property set to false), determine which data will be used and where it will be used in the customer grouping level. Remember that you must define a (non-visible) control to hold each column of data that will be used. I'm assuming that there is something in the customer identification that will define how that customer's data should be handled.

Add each item of data to the appropriate control in the grouping level (if multiple records are required to create one customer level record) or just set the appropriate controls on the customer level to the values of the controls in the detail section.

This avoids setting up queries to run for each customer record on the report (and making the report take hours to print).