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?

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.)
M MAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

YOu need a WHERE clause that filters based on date

[SDate] >= [?StartDate]

mlmcc
0
M MAuthor 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?
0
M MAuthor 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?
0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

M MAuthor 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;
0
mlmccCommented:
In the original example, the filter is on the qryAggSalesWkly query.  Try putting it on the qryAggSales query

mlmcc
0
M MAuthor 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 ...?
0
M MAuthor 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 )
0
mlmccCommented:
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
0
M MAuthor 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?!
0
mlmccCommented:
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
0
M MAuthor Commented:
Well, when I try your sql on qrySales, I get:
mlmccSolution.jpgwhat am I missing?!  thank you for your patience.
0
mlmccCommented:
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
0
M MAuthor Commented:
nope =O (
0
mlmccCommented:
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
0
M MAuthor 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.
0
mlmccCommented:
Your week numbers are characters right?
Single digit for 1 thru 9?

Try using

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

mlmcc
0
M MAuthor Commented:
Where am I to try that? (sorry)
0
M MAuthor 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.
0
M MAuthor 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?!
0
mlmccCommented:
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
0
M MAuthor 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?)
0
mlmccCommented:
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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
M MAuthor 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!
0
M MAuthor 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!
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.