Access 2007 Sql Query

225 pts.
Tags:
Access SQL Averaging
Counts
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
ASKED: September 13, 2012  7:14 AM

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 11  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • TomLiotta
    And what is the problem you are having with getting it accomplished? -- Tom
    125,585 pointsBadges:
    report
  • witsendestate
    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:
    report
  • TomLiotta
    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:
    report
  • TomLiotta
    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:
    report
  • TomLiotta
    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:
    report
  • witsendestate
    Hi Tom... Thanks for the valuable input.. I'll go through your comments now. I appreciate your assistance.RegardsMark
    225 pointsBadges:
    report
  • witsendestate
    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:
    report
  • witsendestate
    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:
    report
  • witsendestate
    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:
    report
  • witsendestate
    Sorry to muck up this thread.. Here is the output..
    225 pointsBadges:
    report
  • TomLiotta
    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:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following