Single Report using Multiple Queries

[Patrick Trotter]

I am trying to setup a single report to track our sales dept. The rub
is this...I need to create a report that will show # of sales, total $
sum of sales, # appts run, # proposals generated. This information is
drawn from 3 different tables, and I only want to use one beg. and end.
date parameter for all three (e.g 4-1-04 - 4-30-04). The problem that I
am having is that the information is not necessarily inter-related, so
that the date parameters are selecting only records that match all date
parameters. For example, a salesman may run an appt on Apr 26, generate
a proposal on Apr 29, and not sell the acct until May 3. The example
record is being completely excluded from the report, but I still need to
account for the appt, and proposal from the month of Apr.

I realise this is confusing, but any help would be appreciated.

Patrick

 

[Gerald Stanley]

Have you considered using a UNION query e.g. something
along the lines of

SELECT Sum(1) as TotalSales, Sum(salesCost) as
TotalSalesCost,0 As TotalAppts ,0 As TotalProposals FROM
SalesTable WHERE salesDate >= #4/30/2004# AND salesDate <=
#4/30/2004#
UNION
SELECT 0,0, Sum(1),0 FROM ApptsTable WHERE apptDate >=
#4/30/2004# AND salesDate <= #4/30/2004#
UNION
SELECT 0,0,0,Sum(1) FROM ProposalsTable WHERE propsalDate

= #4/30/2004# AND salesDate <= #4/30/2004#

The dates do not need to be hardcoded. As long as you use
the same construct for the parameter request, you should be
asked to enter the start and end dates once. The above
will give you three rows of output. If you need the output
n one row, try

SELECT Sum(TotalSales) As TotalSales, Sum(TotalSalesCost)
As TotalSalesCost, Sum(TotalAppts) As TotalAppts,
Sum(TotalProposals) As TotalProposals
FROM
(The UNION query above)

All the above is untested aircode but hopefully will give
you some ideas.

Hope This Helps
Gerald Stanley MCSD