Thanks to Crystal for suggesting qry in a qry solution. Now mgmt wants these stats by weeks, not just week at a time. I THOUGHT I could 1) just take week parameter out. And then 2) do a crosstab query?!
I will have crosstab query qtns as soon as I figure out why I can't get past #1.
Here's the weekly sql versions that seem to work (except for reps that haven't sold and/or written a sale and/or quote in a given week. Ryan C helped me with the nz function on another qry, but I haven't been able to apply it successfully here.
Again these 3 qrys work, a week at a time ...
qryAggSalesWkly:
SELECT [tblSales].RepID, [SDate]+(7-Weekday([SDate],7)) AS [Week Ending Date], Count(tblSales.Customer) AS CountOfCustomer, Sum(tblSales.SAmt) AS SumOfQAmt
FROM tblSales
GROUP BY tblSales.RepID, [SDate]+(7-Weekday([SDate],7))
HAVING ((([SDate]+(7-Weekday([SDate],7)))=[Enter Week Ending Date]));
qryAggQuotesWkly:
SELECT [tblQuotes].RepID, [QDate]+(7-Weekday([QDate],7)) AS [Week Ending Date], Count(tblQuotes.Customer) AS CountOfCustomer, Sum(tblQuotes.QAmt) AS SumOfQAmt
FROM tblQuotes
GROUP BY tblQuotes.RepID, [QDate]+(7-Weekday([QDate],7))
HAVING ((([QDate]+(7-Weekday([QDate],7)))=[Enter Week Ending Date]));
and qryAggCombinedActivityWkly: for whatever reason reps that didn't sell/write a sale/quote are at least listed even though they aren't included in above 2 qrys. not sure why this is?!
SELECT qryAggSalesWkly.RepID, [tblRep]![FNameRep] & " " & [tblRep]![LNameRep] AS FullName, qryAggSalesWkly.CountOfCustomer, qryAggSalesWkly.SumOfQAmt, qryAggQuotesWkly.CountOfCustomer, qryAggQuotesWkly.SumOfQAmt
FROM (qryAggQuotesWkly RIGHT JOIN tblRep ON qryAggQuotesWkly.RepID = tblRep.RepID) LEFT JOIN qryAggSalesWkly ON tblRep.RepID = qryAggSalesWkly.RepID;
AND this is what I've tried but can't get to work:
qryAggSales:
SELECT qrySales.RepID, [SDate]+(7-Weekday([SDate],7)) AS [Week Ending Date], [tblRep]![FNameRep] & " " & [tblRep]![LNameRep] AS FullName, qrySales.WeekNum, Count(qrySales.Customer) AS CountOfCustomer, Sum(qrySales.SAmt) AS SumOfSAmt
FROM qrySales RIGHT JOIN tblRep ON qrySales.RepID = tblRep.RepID
GROUP BY qrySales.RepID, [SDate]+(7-Weekday([SDate],7)), [tblRep]![FNameRep] & " " & [tblRep]![LNameRep], qrySales.WeekNum;
qryAggQuotes:
SELECT qryQuotes.RepID, qryQuotes.[Week Ending Date], [tblRep]![FNameRep] & " " & [tblRep]![LNameRep] AS FullName, qryQuotes.WeekNum, Count(qryQuotes.Customer) AS CountOfCustomer, Sum(qryQuotes.QAmt) AS SumOfQAmt
FROM qryQuotes RIGHT JOIN tblRep ON qryQuotes.RepID = tblRep.RepID
GROUP BY qryQuotes.RepID, qryQuotes.[Week Ending Date], [tblRep]![FNameRep] & " " & [tblRep]![LNameRep], qryQuotes.WeekNum;
qryAggCombinedActivity: I get a gazillion records here, when I shd get 11 reps x 9 weeks = 99 records, right? Somewhat desperate, I tried every combination of joins graphically as well, to no avail. And I think I am finally understanding Pat Hartman's earlier suggestion/solution (WeekNum). I hope I am applying it approporiately - to be column headers in a crosstab qry?)
SELECT qryAggSales.WeekNum, qryAggSales.RepID, [tblRep]![FNameRep] & " " & [tblRep]![LNameRep] AS FullName, qryAggSales.CountOfCustomer, qryAggSales.SumOfSAmt, qryAggQuotes.CountOfCustomer, qryAggQuotes.SumOfQAmt
FROM (tblRep LEFT JOIN qryAggQuotes ON tblRep.RepID = qryAggQuotes.RepID) LEFT JOIN qryAggSales ON tblRep.RepID = qryAggSales.RepID;
What am I missing? I tried to replicate the 2 subqueries and qryCombinedActivity exactly graphically in the QBE screen. I don't understand why it's not that simple?
As always, thank you in advance and please keep it simple!
mm
(Will save my crosstab qtns for different thread.)
YOu need a WHERE clause that filters based on date
[SDate] >= [?StartDate]
mlmcc