Query to tables joined only by date

  • Thread starter Thread starter Baz
  • Start date Start date
B

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
 
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
 
Back
Top