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

  • Thread starter Thread starter Guest
  • Start date Start date
G

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!
 
hmm... everyone seems to be getting stumped on this one... I posed at experts exchange and haven't gotten an answer there either.
 
Back
Top