|
Subject:
|
Selecting records in a query
|
|
Posted By:
|
firefighter2045
|
Post Date:
|
8/27/2008 8:03:07 AM
|
I have a query with 5 fields: FabUnit, Priority, GskHours, Balance, and TimeToCMP
What I would like to do is select all the records for each FabUnit sorted ascending by Priority where the sum of TimeToCMP would be less than or equal to 40, the next record would make the sum greater than 40.
I don’t want to actually sum the records I just want to select the records with the query, I was wondering if there was a Select statement that I could put in the criteria field of the query to do this.
Thanks
Tony
|
|
Reply By:
|
mmcdonal
|
Reply Date:
|
8/27/2008 10:07:26 AM
|
Can you post some raw data, and then what the data would look like if the query were successful?
mmcdonal
Look it up at: http://wrox.books24x7.com
|
|
Reply By:
|
SerranoG
|
Reply Date:
|
8/27/2008 3:59:16 PM
|
I think I get it. Let's say a plain select query lists them sorted by priority and you have 1000 records. It just so happens that if you sum TimeToCMP for record nos. 1 to 33, their sum is 38.8. Record 34 would put the sum over 40 so you don't want to include it. Therefore, the query would spit out only records 1 to 33. That is for Priority No. 1.
Now for Priority No. 2, their records range from record nos. 345 to 683. For those, records 345 to 369 add up to 39.5. Therefore, the query would spit out not only records 1 to 33, but also 345 to 369.
And so on for the other priorities. Is that right?
Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
|
|
Reply By:
|
firefighter2045
|
Reply Date:
|
8/28/2008 7:04:40 AM
|
SerranoG you are close it is not based off of the Priority No. I need the records based on the FabUnit which will run from 1 to 14. The Priority No. is used to sort the fields to determine which ones need to be selected first.
I dont know if this will work but I will try it. Below is a copy of part of the records This is for 3 of the fab units. These records are first sorted by the priority and that is how I would need them to be selected. The highlighted records are the ones I need returned.
FabUnit Priority GpPph Balance TimeToCMP
1 132 17 60 3
1 133 17 40 2
1 134 17 40 2
1 135 17 20 10
1 136 12 40 3
1 137 12 20 10
1 139 12 100 8
1 140 13 15 1
1 141 14 60 4
1 142 14 66 4
1 143 14 66 4
2 144 17 66 3
2 145 12 66 5
2 146 12 44 3
2 147 12 10 50
2 363.1 12 34 2
2 422 11 50 4
3 527 14 40 19
3 528 9 200 20
3 529 10 50 13
|
|
Reply By:
|
SerranoG
|
Reply Date:
|
8/28/2008 7:40:27 AM
|
You definitely need a query to sort them in the correct order so you pick off the correction ones to add up to 40.
However, picking them off and listing them will probably require some VBA module to cycle through the recordset created by this query. You could:
1) Write the data to a temporary table based on the sum; OR
2) Create a NEW boolean (yes/no) field into the table that is checked TRUE if the sum criteria is met as you cycle. Then in the future you need only use the records where this field is = TRUE.
I prefer the latter because you're not bloating the database with repeated data.
Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
|
|
Reply By:
|
firefighter2045
|
Reply Date:
|
8/28/2008 8:14:12 AM
|
SerranoG this data already comes from a query it is located in several different tables, and some of it comes from other queries which have to preform some very elaborate math to come up with the values.
I don't mind writing the data to a table, but I still have no clue how to select only the records I want. I would appreciate any help i could get with trying to select the desired records.
|
