Scheduling problem! (1 Viewer)

[arronjuk]

Hi,

I'm having a problem with my database, it is a split database, created in Access 2003.

I am trying to create a database for a hire company. I have created tables for the product categories, and the products. I need the ability to be able to add prospective orders to future dates. But this causes my main problem;

I would like to add the section to the 'Add Order' form, so that the user can enter a start date, end date, and then choose a category. At that point a list of available products appears.

I know there are various posts about scheduling, but I can't find one that fits this model. I also really don't want to create records for every possible day for the next xx years!

I was thinking along the lines of a table that holds the following fields for scheduled orders. We'll call it Orders "Category", "Product", "Start_Date", "End_Date".

And then a query (or possibly code) that takes the start date, end date, and category fields from the Add Order form, and checks them against the "Orders" table, then checks the results against all products in that category, and hides records that match, but returns the remaining list to the form.
(I hope that lot makes sense)

The end result is the user can select a category and a date period and the system returns available products.


If anyone could give me some guidance or point me in the right direction, I would be very grateful.

Many thanks,

Arron

 

[The_Doc_Man]

A couple of "old programmer" rules apply here, and I will offer them as a springboard for discussion.

1. Access will never tell you anything you didn't tell it first.

2. Access won't do anything on a computer that you couldn't do on paper first.

What you are describing, I think, is a future-date inventory using some sort of "Just In Time" shipping model. If this is so, then your database must contain information about inventory availability dates. This is actually almost a traditional inventory problem with the wrinkle of future dates. Let's see if I can describe this.

First, take traditional inventory. You have some fields:

tblInventoryItem
InvID, PK, the part number of the item, assembly, or whatever it is.
<descriptive data for the item>

tblInvXAct
InvID, FK, LONG link to the inventory item
XActDate, date
XActCode, something that tells you what caused inventory to change
XActAmount, LONG, number of items in the transaction.
XActPutTake, INT, +1 or -1

With this wrinkle... A PUT adds inventory, a TAKE removes inventory. And your stock on hand is a summation query of all transactions for that item, where stock "add supply" and "item return" are PUT and stock "issue" and stock "shrinkage" are TAKE entries.

The amount on hand is the sum of all (XActPutTake * XActAmount). You know the inventory level today by adding up all of the individual transactions to see if you are back-ordered or have stock on-shelf. If the total is positive, you have stock on-shelf.

Every so often, you probably should do a sight-inventory, reconcile via a shrinkage or return transaction, and then remove all old transactions past the sight-inventory date, replacing them by a single special PUT record that is the sum of all such records before that given date. Call it a reconciliation record. That way, your database won't clog up forever with old transaction records and your performance won't go to Hell in a handbasket as the transaction table fills up.

OK, what about future dates? Well, start by adding a PUT record for "future shipment expected" and the amount and the future date anticipated for the shipment arrival. You would also need to record "anticipated future sale" as a TAKE record. Then when you are checking for fillling future orders, make the summation query use a date limit for all transactions earlier than the date of anticipated sale, which no longer is assumed to be today. The only wrinkle added by doing this is how you would want to handle two modifiers to those events: If a shipping date slips, the PUT record date has to be modified AND you have to visit all "anticipated future sale" records to see if any of them can no longer be supported due to the shipping delay. If a sale falls through and doesn't occur, you need to either cancel the future sale completely, thus removing the record, or generate a future stock return equal to the sale, with a transaction type of "cancelled sale" as the item code, balancing the future sale record with a future return.

Keeping the detailed records around for a while allows you to track order history for as long as you have room to keep those records. I.e. the transactions give you a very complete picture of business events at least as far back as the last archive event. You can search this forum for the "Archiving" topic to see more about it.