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?

M M
M M used Ask the Experts™
on
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.)
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Somewhere you have to limit it to 9 weeks.

YOu need a WHERE clause that filters based on date

[SDate] >= [?StartDate]

mlmcc
M M

Author

Commented:
OK, hmmm ... I'll try and figure that one out.  I thought it was an error in joins that produced the 'gazillion' records in an output?
M M

Author

Commented:
OK ... this:
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
WHERE (([qryAggSales]![WeekNum]<"10"));
narrowed results down from 2000+ to 231, did I put it in the wrong spot?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

M M

Author

Commented:
I tried this as well, same results of 231.
SELECT 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
WHERE (([qryAggSales]![WeekNum]<"10"))
GROUP BY qryAggSales.RepID, [tblRep]![FNameRep] & " " & [tblRep]![LNameRep], qryAggSales.CountOfCustomer, qryAggSales.SumOfSAmt, qryAggQuotes.CountOfCustomer, qryAggQuotes.SumOfQAmt;
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
In the original example, the filter is on the qryAggSalesWkly query.  Try putting it on the qryAggSales query

mlmcc
M M

Author

Commented:
mlmcc ... I am just not following this.  can you explain further? the 'filter' is the HAVING part, (bc I don't see any WHEREs) y/n?  if yes, then it is also on the orig qryAggQuotesWkly as well, y/n? and there is no 'filter' on the orig qryCombinedActivity, y/n?

which am I supposed to be trying to add, HAVINGs or WHEREs? to just the 2 subqrys, y/n?

Sorry, I'm confusing myself and not sure how to explain it.  Hopefully someone can see ...?
M M

Author

Commented:
disclaimer as a newbie.

I get myself confused trying different things, and if per chance I get something that works 1) don't remember the steps (streamlined) it took to get there, 2) how to recognize similar scenarios where the solution is applicable and how to replicate whatever the solution was, 3) the logic -- how's, why's, when's -- hasn't clicked ... yet ;O )
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
I believe you can use either.  The having should be just the same

HAVING  (([qryAggSales]![WeekNum]<"10"))

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
HAVING  (([qryAggSales]![WeekNum]<"10"));

Open in new window


mlmcc
M M

Author

Commented:
Sorry I got interrupted, and am back now.
When I try that SQL on qryAggSales, I get a parameter prompt: qryAggSales!WeekNum

I am just not understanding?!
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
it should be qrysales

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
HAVING  (([qrySales]![WeekNum]<"10"));

Open in new window


mlmcc
M M

Author

Commented:
Well, when I try your sql on qrySales, I get:
mlmccSolution.jpgwhat am I missing?!  thank you for your patience.
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Try it as

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
HAVING  ((qrySales.WeekNum < "10"));

Open in new window


mlmcc
M M

Author

Commented:
nope =O (
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Try using a where clause

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
WHERE qrySales.WeekNum < "10" 
GROUP BY qrySales.RepID, [SDate]+(7-Weekday([SDate],7)), [tblRep]![FNameRep] & " " & [tblRep]![LNameRep], qrySales.WeekNum;

Open in new window


mlmcc
M M

Author

Commented:
we're getting closer, but it only outputs WeekNum 1

AND we're applying this on qrySales => qryAggSales => qryAggCombinedActivity, right?  If so then I replicate on qryQuotes =>qryAggQuotes => qryAggCombined Activity.
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Your week numbers are characters right?
Single digit for 1 thru 9?

Try using

Where Length(trim(qrySales.WeekNum )) = 1

mlmcc
M M

Author

Commented:
Where am I to try that? (sorry)
M M

Author

Commented:
I tried that after the WHERE part and got: Undefined function 'Length' in expression

Can/would you explain why/how it works (when it does)?  I paste suggestions, and then try and study them to see if I understand them.

Thank you so much.
M M

Author

Commented:
here's the sql that produced the undefined length msg:
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
WHERE Length(trim(qrySales.WeekNum )) = 1
GROUP BY qrySales.RepID, [SDate]+(7-Weekday([SDate],7)), [tblRep]![FNameRep] & " " & [tblRep]![LNameRep], qrySales.WeekNum;

?!  I applied it on Copy of qrySales?!
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Can you change the values in the database?
The problem you are having is that the WeekNum is a character string which sort alphabetically. Thus "1" "10", "2", "3', ..., "8", "9"

Length was to get the length of the string and check if it is 1 thus 9 weeks and under.

Try using LEN rather than LENGTH

mlmcc
M M

Author

Commented:
I am so sorry, ya thought this was gonna be easy, eh?  ;O )

In my qry sales, the expression for WeekNum is: WeekNum: Format([SDate],"ww")
Is this where I change the value?  And if so, how?  If not, then where do I/where can I change the value and how?

Thank you for the explanation regarding character strings sorting alphabetically ... never would have thought about that ... and it makes sense ... now!

(what's the diff between LEN and LENGTH?)
(can you suggest a link where I might find more info on this issue so I don't have to bother this forum with so many add'l questions?)
Senior Consultant
Most Valuable Expert 2011
Top Expert 2013
Commented:
The difference between LEN and LENGTH is that the length function in Access is LEN.  LENGTH is for VB and some other databases.

Can you change the qrysales query?
You can use DatePart to get the week as a number
DatePart([SDate],"ww")

Open in new window


Since there may be other queries that use QrySales and a change could cause other issues try using this

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
WHERE Val(qrySales.WeekNum) <  10
GROUP BY qrySales.RepID, [SDate]+(7-Weekday([SDate],7)), [tblRep]![FNameRep] & " " & [tblRep]![LNameRep], qrySales.WeekNum;

Open in new window


mlmcc
M M

Author

Commented:
HEY mlmcc ... almost there!!!!!  =O )  I've got 2 reps that didn't sell anything in two different weeks, how do I get them included in output (either a blank or a 0 is OK)!

AND I can replicate this for my qryQuotes as well, y/n?  And then do a combined activity qry with week ending dates as column heards.

Can you explain why/how this works (and none of the other suggestions did), in SIMPLE terms?  How was my original thinking/approach flawed?

I thank you for sticking with me on this, and really want to learn.  I will award you points now and save my qtns for a subsequent crosstab query for another thread when I absorb this!
M M

Author

Commented:
many, many thank you's to mlmcc for sticking with me on this!

PS in earlier comment it was supposed to column headers, not heards?!  it would not allow me to edit the comment, perhaps bc I closed it already!
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and Programming
Top Expert 2015

Commented:
even though this is closed, I want to add a comment:

> "that didn't sell anything in two different weeks, how do I get them included in output (either a blank or a 0 is OK)!"

 tblReps is used to get ALL reps even if they are not in the query ... but then criteria is imposed on qrySales, knocking out the effects of the Join.  Essentially, the Join becomes an INNER JOIN, not a RIGHT (or Left, if that is the case) Join.

How to avoid:

one way is for the SQL for qrySales to be modified first so that the criteria is underlying and won't affect the join. How to do that? only use WeekNum <  10 in the underlying query  (qrySales)-- then the join will work right.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial