Looking for a query to do my job for me!

I have developed an inventory database (access 2000), the product is rope. The main reason we have put everything into the database is to preserve the longest length possible on each spool. The trick is to assign each sale, to the shortest possible spool. When an order is placed, it is manually assigned to the best spool. The problem is, in the time between the order being placed and the order being paid for (and cut from the spool) many other orders have been made and perhaps paid for, and that can change which spool the order should be taken from.

Example:

Three spools measure:

15 feet
7 feet
4 feet

A customer orders 5 feet. The order is assigned to the 7 ft. spool, because it is the shortest spool that is at least as large as the amount ordered. The customer mails a payment. Meanwhile another customer orders 10 feet, and pays instantly, via credit card. The order is cut from the 15 ft. spool. Now it would be much more efficient to reassign the original order to the now 5 ft. spool.

Right now I am doing this manually, three or four times a day I run a query which displays all the spools, for the varieties, where any spool has changed since I last checked, then I evaluate each order, and manually reassign where needed.

Each order is identified by the numeric saleID, each spool is identified by the numeric spoolID, there are two tables, sales and spools.

The sales table is comprised of the unique saleID, the quantity field (number of feet ordered) and the SpoolAssignment field which is the spoolID, of the spool, the sale is to be cut from. Also there is a field named status which is numeric. That is 1 awaiting payment, 2 past due, or 3 shipped.

The spools table is comprised of the unique spoolID, the itemID (an unique number given to each type of rope) the quantity field (number of feet on the spool) and the outstanding field which is total length, of the orders assigned to that spool.

I am looking for an update query, that would do my job for me, it could be run after each order, reassigning orders where needed.

I would also like to know how to automate the outstanding field, as I am compiling it manually.

I do not know what I am doing, any help would be greatly appreciated.

This is a most interesting challenge. Hoewever, there are questions, the answers to which are not in your post. One that comes to mind is: Is there a date limit by which an order is canceled? (1 awaiting payment, 2 past due, or 3 shipped, (this can be simplified with one date entry)). What makes what past due? The non-receipt of payment? There must be a time/date related mechanism which cancels the order and would reset the available spool length. A series of queries are required depending on the scenarios but one needs a more detailed analysis for the decision making process regarding order processing. If you wish some help, upload a zipped sample db (Access 2000) with some data and will gladly look at it.

Okay, I over simplified I suppose... I figured to much detail would only muddy the water... so I omitted everything I did not think would be involved in this particular aspect of the database. Our database is very complex. It was originally the back end to an eBay auction management program named auction assistant, which eBay purchased from blackthorn, a couple years ago and renamed it seller's assistant pro, plugged it into their API back end, to become the world's most popular auction management program. We still use it as a cost effective way to access eBay's API. But the database has been customized several times. The tricky part is eBay likes to issue updates without warning, you never know whether they're going to rename a category or convert the whole thing from 97 to 2000. Luckily they only make changes to the tables they know exist. So when we want to do something, we make up a new table and reference their primary keys, and use update queries to keep everything synchronized. From time to time they'll change the formatting of a field or something though and we will have to work around it.

To answer your question when the program sends out a "final notice email" it changes a four digit, statusID, in eBay's table, which changes the numeric value in my table when the query is run. People run late from time to time but, rarely do we come across a true deadbeat. The only reason I even have a status recorded in the table at all is so if it comes down to a stale order, that we've been waiting on, and a fresh new customer, wanting to purchase the same thing will, then the person who was dragging his feet, loses out to the new guy, otherwise I just let the program send late notices, tell the cows come home, after all the inventory is just collecting dust. Until someone else comes along who wants it, why not. The 123 thing is just my way of drawing a line in the sand with 20 statusIDs in eBays table, I like to keep it more cut and dry.

In the interest of simplicity I'm going to cut the database down to the three tables that matter, and I'm going to delete all but the relevant fields so we won't have to explain fields which do not have anything to do with the questions at hand. Proprietary customer information aside, the full version of the database includes database records of nearly every email in the last few years and is a little too much to upload with my Dial-up.

For simplicity I think it's best we all pretend there are only a couple straightforward tables, and we are unaware of the preceding paragraphs. The only purpose the database serves is keeping up with the inventory. I don't know about you but I find it easier to see the forest through the trees once I clear-cut, all but the trees I need.

I wish everyone who posts in forums would explain as clearly as you do, for both your posts are crystal clear.
I looked at the db and I found some anomalies. For example, you show Sales with no matching data in the Cutlist. Can this be possible? To automate, I must add a date field (business transactions are always date related), and will add some fictitious data for all possible scenarios. Your additional comments would be appreciated and then the fun can begin.

First off, I had just selected a random cross-section, so I went back and selected only records that matched up in all three tables. Now everything should lineup. Not all sales require a matching record in the cut list. The reason is some of our items (a small percentage) are regular items, not from a spool.

I'm glad I spent this morning doing this, I found two mistakes. Someone (probably me) had put two sales in the cut list twice each. To keep myself from ever doing that again I indexed (No Duplicates) the saleID field on the cut list table.

I have quite a few dates recorded in the database already. Tell me what date you wanted, and I'll throw it in and upload the database again. I'm having a hard time coming up with a reason to include a date as this is something that would be run over and over, like I said, I plan to run this after each sale (or change to a sale).