Hi Team (Again).. further to an original post I am trying to get 2 further fields added to my Query / Report. BoxCount & AverageWeight
Here is the existing query.
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;
This gives me a summary by Pickerid, Pickername, Pickerstotals, Hourly rate... All is good with this..
I now what to add a count of boxes (summed) for each picker & average box weight..
The Table is structured 1 entry for every box weighed. A pickerid, Weight of the box, date, time etc is also added..
My Output would be as follows:-
Pickerid Pickername Pickerstotal hourlyrate Boxcount AverageWeight
1 Mark 200.00 12 50 4.5
2 Steve 150.00 13 37 3.75
I have tried adding a count(*) in both select statements but it will always return "1" as all the records for that picker are tallied into 1 output record. I need the count of records for that picker before it is tallied..
The averagweight may be a hard one... it needs to do a avg(currentweight) for each picker.
Make sense.. I hope so and I do appreciate your help... Really
Regards
Mark
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.
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
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
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;
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
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;
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
Free Guide: Managing storage for virtual environments
Complete a brief survey to get a complimentary 70-page whitepaper featuring the best methods and solutions for your virtual environment, as well as hypervisor-specific management advice from TechTarget experts. Don’t miss out on this exclusive content!
Discuss This Question: 11  Replies