Query to tables joined only by date

[Baz]

Hi

I am trying to query two table, the only common column is the date, the one
table is information taken from a telephone management system

tblCallDetail
Date, Dials, CallDuration
Date SumOfDuration SumOfDials
2003/10/21 18 415



The other table is information from sales staff

tblSaleDetail
DateOfSale, Premium, PolicyUnits
DateOfSale SumOfTotalPremium SumOfPolicyUnits
2003/10/21 R 1,372.00 28


I want a report that give me how many sales were made on a particular day as
well as the number of dials the agent made and the total call duration for
the day.

When I query the tables separately i get the results i want, when i query
them together I get totally incorrect figures.

SumOfPolicyUnits SumOfTotalPremium Date SumOfDuration SumOfDials
224 R 10,976.00 2003/10/21 252 5810



Here is the query:

SELECT Sum(tblSaleDetail.PolicyUnits) AS SumOfPolicyUnits,
Sum(tblSaleDetail.TotalPremium) AS SumOfTotalPremium, tblCallDetail.Date,
Sum(tblCallDetail.Duration) AS SumOfDuration, Sum(tblCallDetail.Dials) AS
SumOfDials
FROM tblCallDetail INNER JOIN tblSaleDetail ON tblCallDetail.Date =
tblSaleDetail.DateOfSale
GROUP BY tblCallDetail.Date
HAVING (((tblCallDetail.Date) Between [StartDate] And [EndDate]));


Thanks

Barry

 

[tina]

yeah, i think you're gonna get a cartesian product out of
that setup. suggest you create a separate query for each
table, to get the totals you need from each.
then create a third query, based on the first two queries,
and linking the date fields in that third query. then
select all the "summed" fields to show in the third query.
base your report on the third query.

hth