Report - Detail and Summary Information using two diff qry's

[Debbie]

Hello all!

I'm am trying to create a report that requires the top
portion to show the detail for the various product groups
of sales by Customer and Salesman. The totals of the
sales are calculated in the customer name footer section
of the report. In the customer name footer section I also
need to calculate the customers eligibility for an
incentive trip based on certain criteria.

Example:
In the Customer Name Header

Customer: ABC
Account #: 123
Sales Rep: Joe

Month Res Sales Cml Sales Pts Sales Total Sales

In the Detail Section
Jan 1000 5000 200 6200
Feb 0 2000 300 2300
Mar 1500 0 0 1500
Apr 5000 1000 1000 7000

In the Customer Name Footer Secion
Total 7500 8000 1500 17000

Trip Calculations

Trip A
Trip B

The Trip Calculations is where I am struggling, I have a
table setup that spells out what number to divide by
depending on what type of customer it is. This has to be
calculated for each group of sales (Res, Cml, Pts, Total).

IE: iif(customer is RNC, then total res sales / 330000/
else total res sales divided by 440000)

There will be multiple reports for the various regions
that basically will be the same, but I don't want to have
to go into each of these reports and change the divided by
numbers.

So, is there a way that I can calculate the trip
sales/eligibility in a query and bring it into the Footer
section of the report while having the detail portion of
the report pull from another Query.

Hope someone can help, it seems logicial, just don't know
how to do this.

Thanks in advance.

Debbie

 

[Marshall Barton]

I'm am trying to create a report that requires the top
portion to show the detail for the various product groups
of sales by Customer and Salesman. The totals of the
sales are calculated in the customer name footer section
of the report. In the customer name footer section I also
need to calculate the customers eligibility for an
incentive trip based on certain criteria.

Example:
In the Customer Name Header

Customer: ABC
Account #: 123
Sales Rep: Joe

Month Res Sales Cml Sales Pts Sales Total Sales

In the Detail Section
Jan 1000 5000 200 6200
Feb 0 2000 300 2300
Mar 1500 0 0 1500
Apr 5000 1000 1000 7000

In the Customer Name Footer Secion
Total 7500 8000 1500 17000

Trip Calculations

Trip A
Trip B

The Trip Calculations is where I am struggling, I have a
table setup that spells out what number to divide by
depending on what type of customer it is. This has to be
calculated for each group of sales (Res, Cml, Pts, Total).

IE: iif(customer is RNC, then total res sales / 330000/
else total res sales divided by 440000)

There will be multiple reports for the various regions
that basically will be the same, but I don't want to have
to go into each of these reports and change the divided by
numbers.

So, is there a way that I can calculate the trip
sales/eligibility in a query and bring it into the Footer
section of the report while having the detail portion of
the report pull from another Query.

I think the easiest way to approach this is to use a
subreport for the trip stuff. This easily allows you to use
a query specifically designed for this purpose.