TomLiotta Sep 13, 2012   7:21 AM GMT

And what is the problem you are having with getting it accomplished? -- Tom

125,585 pointsBadges:

witsendestate Sep 14, 2012   2:42 AM GMT

If I try and add a SUM & AVG to the query the sum always returns the value of "1" as all the boxes have already been added up to give 1 record.. I want the sum to actually count the boxes for that picker. The Avg is also only averaging the single record not the total of kg's of the boxes divided by the box count.

225 pointsBadges:

TomLiotta Sep 14, 2012   3:52 AM GMT

First, I'm going to try to post your original query in a formatted way. Let's see if it shows up in a readable form:  SELECT Pickerid, Pickername, ROUND(SUM(totalweight), 2) AS PickersTotalWeight, SUM(totalweight)/ROUND(SUM(pickerhours)) AS HourlyRate FROM ( SELECT pickerid, currentdate, pickerhours, pickername, SUM(currentweight) AS totalweight FROM mergeddatabase GROUP BY pickerid, currentdate, pickerhours, pickername ) AS [%$##@_Alias] WHERE ((currentdate between [Enter first date:] AND [Enter last date:]) and (pickerhours > 0.1)) GROUP BY pickerid, pickername;  If that posts okay, I'll add some comments. If not, I'll try it a different way.   Tom

125,585 pointsBadges:

TomLiotta Sep 14, 2012   5:20 AM GMT

Sheesh. Well, anyway...   First, I'm no Access expert, so there might be a couple items that work differently than with real SQL. For this part:  

FROM ( SELECT pickerid, currentdate, pickerhours, pickername, SUM(currentweight) AS totalweight FROM mergeddatabase GROUP BY pickerid, currentdate, pickerhours, pickername ) AS [%$##@_Alias]

  That's your table expression for the FROM clause. You have "AS" in order to apply an alias name. I'm just going to call the name MyAlias. So, the basic structure is simply FROM (MyAlias).   You have parentheses to mark the beginning and end of that part of your query.   The first thing that seems odd to me is that you whole query has a WHERE clause, but the conditions only seem to apply to your table expression. The conditions don't seem to be totally relevant to your final outer query. To me, it would all seem better if you had things looking like this:

FROM ( SELECT pickerid, currentdate, pickerhours, pickername, SUM(currentweight) AS totalweight FROM mergeddatabase GROUP BY pickerid, currentdate, pickerhours, pickername WHERE ((currentdate between [Enter first date:] AND [Enter last date:]) and (pickerhours > 0.1)) ) AS [%$##@_Alias]

 Hmmm... it's going to be a mess no matter how I do it.   But the WHERE clause is moved up to be part of your table expression instead of being applied to the final query. That is, I moved it inside those parentheses.   It's possible that Access will do that as part of some optimization. It's also possible that it can't be done like that because of the parameters for the dates. But it's the way that it looks like you want things to run.   The only place that currentdate is used is inside the table expression. If you only want dates within a range, then they probably should be dropped before putting rows through any summarization and grouping.   The (pickerhours > 0.1) part is questionable, though. Do you want to select original rows greater than (0.1) or sums greater than (0.1)? That part might move back to a WHERE clause for the final query.   Now, this piece -- ROUND(SUM(pickerhours)) .   You have that in your final query. You're also using pickerhours in the GROUP BY in your table expression, which doesn't make full sense to me. I can't see why you want to "group" by that column. I would more expect that to be a SUM() value instead of a GROUP BY. You might have a reason, so I can't say that it's wrong. It just seems odd.   I looked at your past history. You seem to be coding valid business queries rather than just being a student. I can help with SQL, though Access can be a little different. There are others who can tune whatever we come up with.   Look over my comments and see if they are readable and make sense. If we get a solid basic query first, changes will be easier to fix next. Ask questions here if explanations are needed.   Tom

125,585 pointsBadges:

TomLiotta Sep 14, 2012   5:23 AM GMT

Sorry, I put the WHERE clause after the GROUP BY. It really should be befor the GROUP BY. But that's easy to fix, and Access might not care.   Tom

125,585 pointsBadges:

witsendestate Sep 18, 2012   5:44 AM GMT

Hi Tom... Thanks for the valuable input.. I'll go through your comments now. I appreciate your assistance.RegardsMark

225 pointsBadges:

witsendestate Sep 20, 2012   10:00 AM GMT

Hi Tom,Once again huge thanks for your help.... I tried your suggestion with different variations but had errors.. (Sorry).. Maybe its a Access Sql quirk.Here is the final Query I came up with which works perfectly.. May not be the ideal coding practices.. I was a Cobol Programmer.. SQL very rusty.Also how do you format the text in these posting so its not all wrap around text.Once again thanks Heaps MarkSELECT Pickerid, Pickername, ROUND(SUM(totalweight), 2) AS PickersTotalWeight, SUM(totalweight)/ROUND(SUM(pickerhours)) AS HourlyRate, Round(sum(boxcount), 2) AS Boxes, Round(sum(AvgBox), 3) AS AvgBoxWeightFROM (SELECT pickerid,currentdate,pickerhours,pickername,SUM(currentweight) AS totalweight, count(*) AS BoxCount, avg(currentweight) as AvgBoxFROM mergeddatabaseGROUP BY pickerid,currentdate,pickerhours,pickername)  AS [%$##@_Alias]WHERE ((currentdate between [Enter first date:] AND [Enter last date:]) and (pickerhours > 0.1))GROUP BY pickerid, pickernameORDER BY 4 DESC;

225 pointsBadges:

witsendestate Sep 20, 2012   10:04 AM GMT

Hi Tom, Once again huge thanks for your help…. I tried your suggestion with different variations but had errors.. (Sorry).. Maybe its a Access Sql quirk. Here is the final Query I came up with which works perfectly..It may not be the ideal coding practices.. I was a Cobol Programmer.. SQL very rusty. Regards Mark

225 pointsBadges:

witsendestate Sep 20, 2012   10:05 AM GMT

SELECT Pickerid, Pickername, ROUND(SUM(totalweight), 2) AS PickersTotalWeight, SUM(totalweight)/ROUND(SUM(pickerhours)) AS HourlyRate, Round(sum(boxcount), 2) AS Boxes, Round(sum(AvgBox), 3) AS AvgBoxWeight FROM (SELECT pickerid,currentdate,pickerhours,pickername,SUM(currentweight) AS totalweight, count(*) AS BoxCount, avg(currentweight) as AvgBox FROM mergeddatabase GROUP BY pickerid,currentdate,pickerhours,pickername) AS [%$##@_Alias] WHERE ((currentdate between [Enter first date:] AND [Enter last date:]) and (pickerhours > 0.1)) GROUP BY pickerid, pickername ORDER BY 4 DESC;

225 pointsBadges:

witsendestate Sep 20, 2012   10:10 AM GMT

Sorry to muck up this thread.. Here is the output..

225 pointsBadges:

TomLiotta Sep 20, 2012   10:24 AM GMT

If that query gives the result you expect and need, I'll look it over to be sure I understand what it does. Just from impressions, it looks like it can be simplified. Once I'm comfortable that I know its full meaning, simplification should be fairly easy. One question... does mergeddatabase contain one row for each box? -- Tom

125,585 pointsBadges: