Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old August 27th, 2008, 08:03 AM
Authorized User
 
Join Date: Oct 2007
Location: , , .
Posts: 31
Thanks: 0
Thanked 0 Times in 0 Posts
Default Selecting records in a query

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 With Quote
  #2 (permalink)  
Old August 27th, 2008, 10:07 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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 With Quote
  #3 (permalink)  
Old August 27th, 2008, 03:59 PM
Friend of Wrox
Points: 4,007, Level: 26
Points: 4,007, Level: 26 Points: 4,007, Level: 26 Points: 4,007, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

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 With Quote
  #4 (permalink)  
Old August 28th, 2008, 07:04 AM
Authorized User
 
Join Date: Oct 2007
Location: , , .
Posts: 31
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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 With Quote
  #5 (permalink)  
Old August 28th, 2008, 07:40 AM
Friend of Wrox
Points: 4,007, Level: 26
Points: 4,007, Level: 26 Points: 4,007, Level: 26 Points: 4,007, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

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 With Quote
  #6 (permalink)  
Old August 28th, 2008, 08:14 AM
Authorized User
 
Join Date: Oct 2007
Location: , , .
Posts: 31
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Selecting one of possibly many related records rodmcleay SQL Server 2000 2 March 24th, 2007 12:49 AM
selecting records from excel androoo Pro VB 6 1 January 12th, 2005 12:18 PM
Selecting records from a dynamic tablename flyin SQL Server 2000 3 September 30th, 2004 08:05 AM
Selecting the Latest Records gordbro Access 3 September 16th, 2004 07:21 PM
ASP - Selecting Multiple Records Ei0nN Classic ASP Databases 2 May 28th, 2004 03:38 PM



All times are GMT -4. The time now is 04:05 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.