Multiple Calculations based upon a SQL in Report

[Brad]

I want to create a report and calculate values based upon multiple SQL
criteria. I know how do this by creating multiple queries doing a
union query and then making the report but, to make it cleaner and
because of the number of queries that would take what I would like to
do calculate the values based upon SQL statement in VBA.

Here is specifically what I want to do.

I am calculating number of accounts and total dollar value for each
month. Then from the same data I want the number that fall into
several categories, then from those categories I want to further break
that down based upon another set of criteria and so on for several
levels. Each value will be reported in columns across the page with
each row being the month.

Can I just create code that performs the sql and sets that as the data
source for each field and is there any special VBA code that needs to
be included so this works correctly.

Anybody done this and have some sample code to share?

 

[Allen Browne]

There are several approaches you could consider, Brad.

Firstly, are you familiar with subqueries? It may be possible to create a
query that has a number of subqueries that grab the values you want from
related tables. The related tables don't even need to be in the main query,
and no VBA is needed. If that's new, here's an introduction:
http://allenbrowne.com/subquery-01.html

If that's not suitable, you might be looking at creating a temporary table
where you can assign values to the various fields, and then use it as the
source for the report. Typically this would be interfaced from a form with a
command button to open the report. The button's Click event executes a
series of action queries to empty the temp table, populate it with the
desired records, update the various fields so they are all populated, and
then finally OpenReport.

 

[Brad]

There are several approaches you could consider, Brad.

Firstly, are you familiar with subqueries? It may be possible to create a
query that has a number of subqueries that grab the values you want from
related tables. The related tables don't even need to be in the main query,
and no VBA is needed. If that's new, here's an introduction:
   http://allenbrowne.com/subquery-01.html

If that's not suitable, you might be looking at creating a temporary table
where you can assign values to the various fields, and then use it as the
source for the report. Typically this would be interfaced from a form with a
command button to open the report. The button's Click event executes a
series of action queries to empty the temp table, populate it with the
desired records, update the various fields so they are all populated, and
then finally OpenReport.

--
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.










- Show quoted text -

Thanks, Allen I was not familiar with subqueries but that will
probably do the trick. I have played with it a little and here is a
follow up question. How do you do a self join with the subquery. I
want to group the results of the subquery with the main query by month
and year. I can do an inner join with different tables but how do you
join the results of the subquery of the same table.

 

[Brad]

Thanks, Allen I was not familiar with subqueries but that will
probably do the trick.  I have played with it a little and here is a
follow up question.  How do you do a self join with the subquery.  I
want to group the results of the subquery with the main query by month
and year.  I can do an inner join with different tables but how do you
join the results of the subquery of the same table.- Hide quoted text -

- Show quoted text -

Nevermind, I found the answer to my question on your website. Great
website by the way.
Many Thanks.