troubleshooting Question

qry in a qry worked by week ending date! how do I get it to work by weeks (y-t-d) instead of one week at a time?

Avatar of M M
M M asked on
Microsoft AccessSQL
25 Comments1 Solution193 ViewsLast Modified:
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.)
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 25 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 25 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros