extracting a field to a new table when another field's total gets to a certain value?

[Guest]

I would like to do a make-table query to copy one field when the running total of another field gets to a certyain number..

for example - I have a tblOfficeData table with many fields... two of which are ContractNumber and CabsADJ

I want to copy the contract numbers into a new table (tblWeek1) when the total of CabsADJ gets to 50... I will then relate this new table back to tblOfficeData so that I can view all the fields.

I need this for a weekly production schedule.... The ideal sitaution (I guess) would be to make 52 queries (for 52 weeks in the year), and run one each week to generate the new table

I want to generate tables instead of just making a select query, because I want to move around the jobs after the query assigns them... say I need a certain job in a certain week, even if it will go over 50 in CabsADJ

Here's a diagram to help you understand

tblOfficeData

ContractNumber CabsAD
J24741-1
J24770-0
J24829-0 2
J24788-0 1
J24832-0

I want the top 4 contract numbers to be copied into a tblWeek1 because the running total of CabsADJ equals 44 and adding the fifth one would make it higher than 50... I would then run a week 2 query to pick up the last record and any others under it until that week gets below 50... etc...

I would also run an update querry in between the make-table queries to mark the ones already assigned so that they aren't picked up by the next query.

Do you understand my question?

Thank you!

 

[Guest]

hmm... everyone seems to be getting stumped on this one... I posed at experts exchange and haven't gotten an answer there either.