Access 2003 SQL Query for grouping

225 pts.
Tags:
Microsoft Access 2003
SQL Query
Team... I need your help... SELECT Pickerid, Sum(Currentweight) AS TotalWeight, IIf(pickerhours=0,0,TotalWeight/pickerhours) FROM mergeddatabase WHERE ((Currentdate) between [Enter First Date Required:] and [Enter Last Date Required:]) GROUP BY pickerid, pickerhours; I have a access database (2003) single Table (mergeddatabase) with thousands of entries.. This is a query to work out picking rates. The criteria is a picker picks a box and information like currentdate, currentweight, pickerid, pickerhours etc is appended to each record. There are hundreds of records per pickerid each day. What Im trying to do is total all entries for that pickerid within a certain date range.. Ie pickerid = 1 date = 18/03/2012, currentweight = 4.06, hrs = 2 pickerid = 1 date = 19/03/2012, currentweight = 5.08, hrs = 2 pickerid = 4 date = 18/03/2012, currentweight = 4.09, hrs = 3 pickerid = 4 date = 19/03/2012, currentweight = 4.11, hrs = 5 So the result should show Pickerid 1, currentweight 9.14, avgweight = (currentweight / hrs) Pickerid 4, currentweight 8.2, avgweight = (currentweight / hrs) I hope this makes sense.

Answer Wiki

Thanks. We'll let you know when a new response is added.

Your not not assigning the iif statement to any field. it should read

IIf(pickerhours=0,0,TotalWeight/pickerhours) AS avgwight

hope this helps

Discuss This Question: 17  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
  • carlosdl
    It makes sense (almost). Can you clarify how you need to calculate the avgweight ? (totalweight/ pickerhours) doesn't make much sense, as in your example your resultset includes only 1 record for Pickerid 1, and one for Pickerid 4, so the avgweight must be based on a sum, or an average (or some other type of operation) of the pickerhours from the records that are being grouped. Maybe something like this is what you are looking for:
    SELECT Pickerid, SUM(currentweight) AS totalweight, totalweight/SUM(hrs) AS avgweight
    FROM mergeddatabase
    GROUP BY Pickerid;
    69,510 pointsBadges:
    report
  • witsendestate
    Sorry Team... We have ie.. 40 Pickers which each pick say 400 boxes.. That means 16,000 records.. 1 record per box. so Im after Over a given date range (1 week), the Total volume for each picker and also the avg picking rate.. Fields Pickerid, Totalweight, Avg Pickrate The average pick rate is basically the total the picker picked divided by the number of hours they worked. Thanks Team
    225 pointsBadges:
    report
  • carlosdl
    So, what about the suggested query ? (of course, you would have to add the date range condition and any other additional conditions that may be needed)
    69,510 pointsBadges:
    report
  • witsendestate
    Carlosdl.. thankyou for your help... I have implemented what you suggested as follow:- but the figures do not add up SELECT Pickerid, Sum(Currentweight) AS TotalWeight, totalweight/SUM(pickerhours) AS avgweight, Pickerhours FROM mergeddatabase WHERE ((Currentdate) between [Enter First Date Required:] and [Enter Last Date Required:]) GROUP BY pickerid, pickerhours; Pickerid TotalWeight Avgweight pickerhours 2 37.14 0.4220 11 3 73.38 0.3903 11.75 5 73.54 0.4377 10.5 6 82.96 0.4007 11.5 If you take 37.14 / 11 it should equal 3.3763 etc Can you think why the calculation is incorrect.. Regards Mark
    225 pointsBadges:
    report
  • witsendestate
    The info I gave was for a single date.. when I use a string of dates the Pickerid is not grouped anymore.. ie If I have 2 days to scan across I get 2 entries per picker for each date
    225 pointsBadges:
    report
  • witsendestate
    Sorry Im misleading you on the multiple date usgae.. It is because I added the pickerhours in the select statement.. thats why it gave 2 entries.. But the calculation error is still there as above
    225 pointsBadges:
    report
  • witsendestate
    More information now... We had to add a column in the table which is Pickerhours. The problem is that for every box we capture we also append the picker hours for that day... so if there are 400 boxes for each picker we only need to get the 1 picker hours for each date (Not sum each picker hours).. Does this make sense??
    225 pointsBadges:
    report
  • carlosdl
    I'm not sure to be following you. Can you post some example data, and the results you should get from it ?
    69,510 pointsBadges:
    report
  • witsendestate
    Carlosdl.. thankyou for your help so far on this.. Here is some test data and results I would expect.. Pickerid Currentdate CurrentWeight Pickerhours 1 20/02/2012 4.00 7 1 20/02/2012 4.12 7 1 20/02/2012 5.00 7 2 20/02/2012 4.00 7 2 20/02/2012 4.12 7 2 20/02/2012 5.00 7 1 21/02/2012 4.32 6.1 1 21/02/2012 4.12 6.1 2 21/02/2012 4.32 4.5 2 21/02/2012 4.12 2.5 Picker 1's totalweight adds up to 21.56, They worked 7 hours on the 20th + 6.1 hours on the 21st . So thats 13.1 hours they worked in 2 days(dont add up the hours for the same day.. only use one of them for each day) Picker 2's totalweight adds up to 21.28, They worked 7 hours on the 20th + 4.5 hours on the 21st . So thats 11.5 hours they worked in 2 days(dont add up the hours for the same day.. only use one of them for each day) The result I would expect to see is Pickerid Totalweight Avgweight 1 21.56 1.6458 2 21.28 1.850 Remember Totalweight / hours worked = avgweight per hour Regards Mark
    225 pointsBadges:
    report
  • witsendestate
    Carlosdl.. thankyou for your help so far on this.. Typo in previous post.. getting way tired.. Here is some test data and results I would expect.. Pickerid Currentdate CurrentWeight Pickerhours 1 20/02/2012 4.00 7 1 20/02/2012 4.12 7 1 20/02/2012 5.00 7 2 20/02/2012 4.00 7 2 20/02/2012 4.12 7 2 20/02/2012 5.00 7 1 21/02/2012 4.32 6.1 1 21/02/2012 4.12 6.1 2 21/02/2012 4.16 4.5 2 21/02/2012 4.12 4.5 Picker 1’s totalweight adds up to 21.56, They worked 7 hours on the 20th + 6.1 hours on the 21st . So thats 13.1 hours they worked in 2 days(dont add up the hours for the same day.. only use one of them for each day) Picker 2’s totalweight adds up to 21.40, They worked 7 hours on the 20th + 4.5 hours on the 21st . So thats 11.5 hours they worked in 2 days(dont add up the hours for the same day.. only use one of them for each day) The result I would expect to see is Pickerid Totalweight Avgweight 1 21.56 1.6458 2 21.28 1.8608 Remember Totalweight / hours worked = avgweight per hour Regards Mark
    225 pointsBadges:
    report
  • carlosdl
    I'm sure you had a reason to structure your table that way, but I must say that it is not a very good design, specifically because of the Pickerhours column. Anyway, I'll try to figure out a solution tomorrow afternoon, when I have a database at hand to test.
    69,510 pointsBadges:
    report
  • tlsanders1
    Yes, this would be much cleaner if you separated the pickerhours into a separate table that would have one record for each picker for each day. Failing that, how about if you only listed each picker's hours against his first box of the day, then listed 0 hours for the rest of his boxes that day?
    1,340 pointsBadges:
    report
  • carlosdl
    How about something like this ?
    SELECT Pickerid,SUM(totalweight),SUM(totalweight)/SUM(pickerhours)
    FROM
    (SELECT pickerid,currentdate,pickerhours,SUM(currentweight) AS totalweight
    FROM mergeddatabase
    WHERE currentdate between [Enter first date:] AND [Enter last date:]
    GROUP BY pickerid;
    69,510 pointsBadges:
    report
  • Leonmanuel
    Hey carlosdl i was thinking along those same lines, you just missed a group by SELECT Pickerid,SUM(totalweight) as totalweight, SUM(totalweight)/SUM(pickerhours) as avgweight FROM (SELECT pickerid,currentdate,pickerhours,SUM(currentweight) AS totalweight FROM mergeddatabase GROUP BY pickerid, currentdate, pickerhours) WHERE currentdate between [Enter first date:] AND [Enter last date:] GROUP BY pickerid;
    25 pointsBadges:
    report
  • carlosdl
    Yes, it seems that part of the sub-select got cut (the group id and the closing parenthesis) when posting the command. This should have been the complete command:
    SELECT Pickerid,SUM(totalweight),SUM(totalweight)/SUM(pickerhours)
    FROM
    (SELECT pickerid,currentdate,pickerhours,SUM(currentweight) AS totalweight
    FROM mergeddatabase
    GROUP BY pickerid,currentdate,pickerhours)
    WHERE currentdate between [Enter first date:] AND [Enter last date:]
    GROUP BY pickerid;
    69,510 pointsBadges:
    report
  • witsendestate
    Hi Team. You guys are worth more money.. tell your bosses.. I know my structure for this is not correct and I will be changing this today... It was cobbled together to get some stats out.. but clearly the wrong way to do it. Thank you so much for your effort with my stupidty.... yur help and speed of response was great. Regards Mark
    225 pointsBadges:
    report
  • witsendestate
    [...] 8. Carlosdl, TIsanders, and Leonmanuel share their answers about grouping a query in Microsoft Access 2003. [...]
    0 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