I'm not quite sure how to explain this

[Avick]

I am building a database that will tell the company what items to dispatch to the customer first.

It’s a food company. So getting products out on time is important. I will try explain it.

Say the food company makes a batch of 30 apple tarts. This collection of products has a set batch number with all the relevant information attached.
For example
Batch no 4457 - qty 30 - created 10/10/07 - use by 10/11/07
Batch no 4458 - qty 30 - created 11/10/07 - use by 11/11/07

Now lets say customer (joe Bloggs) orders 35 apple tarts

The food company wants to take all the first batch (4457) and 5 from the second batch (4458)

The will have an order sheet with the following
Apple tart batch 4457 - 30
Apple tart batch 4458 - 5

After the order is gathered it will be given an order number of say order 8897

The company can now go back and look at order 8897 and see what was shipped in that order and what the batch numbers where for all products.

Any help would be greatly appreciated

Alan

 

[Guus2005]

You have stated clearly what you want and by the looks of it, it is not difficult. However you have forgotten to mention what the problem is.

Regards, Guus

 

[Avick]

Hi Guus

That is my problem, I'm not quite sure how to do it.

I know what I want but getting it or building it has just knocked me

For example,
How would I make sure the oldest batch is taken first, when that batch is empty I want it to go to the next batch number and so on.

There could be 10 items (such as 10 apple tart batch numbers with 30 apple tarts per batch)

This would happen for each product in the order.
if for example he orders:

35 Apple tarts
16 mince pies
40 jam tarts

each product could have lots of batch numbers with a number of units in each batch.

He has to be able to track each product unit to a specific batch number which in turn will track back to each ingredient in that product and then back to a supplier.

Its all being build for traceability.

I don't want to mess this up at the start and end up causing major problems down the line.

Alan

PS
Sorry forgot to mention.
The next order that he gets will know that batch number 4458 will be less then 30 units (missing 5 because of the previous order.)

 

[The_Doc_Man]

OK, in a sense this is an inventory system in which you are your own supplier.

SEARCH this forum for INVENTORY and STOCK CONTROL topics to get general ideas about how to manage stock control. Your wrinkle, of course, is that you probably always want to use oldest stock first - unless you have an age limit after which you declare shrinkage so you can remove expired stock.

To approach this, you are going to need a table that lists your product AND its creation date or use-by date. It also implies that you must retain batch-number identification with your product storage.

Offhand, I don't recall many threads on time-sensitive inventory usage. But there might have been a few. On the other hand, your problem is not THAT different from inventory systems where the price depends on the batch from where you got the stock items.

The key will be how you define product availability. Normally, you would just say "we have n on-hand of stock item x" - but you might in this case you might need to say "we have n on-hand of stock item x in batch y". The trick will be the summation query that defines quantity on hand. Also, you might need (as a short-cut for efficiency's sake) a flag in the batch record to show that you have now used up batch x and must step to batch x+1 when fulfilling an order.

In essesnce, you are looking at either a really ugly set of batch-product queries or some VBA code to run behind the scenes to keep batch info updated. Your problem is quite complex because of the batch-age tracking; otherwise it is just an inventory problem.

 

[Banana]

Here's an article to get you started.

 

[Avick]

Banana
Thanks for the link. Very interesting reading and possibly something I could use in this build.

The_Doc_Man
an INVENTORY system was on my mind when I first considered the build.
Most of the data will be dates so I could probably utilize them better.

With relation to the use by dates. my plan was to have a Created date a number field and a use by date. The use by date will be based on [created date]+[number field]=[use by date]

The problem I have with taking an INVENTORY approach is because of the layout structure.
The structure is as follows:

-Supplier
-Ingredients {all linked to supplier}
-Product
-product details {the ingredients that make up a product}
-Customer
-Order {this will be a single order that will list each product and there batch number}

I am also considering adding Order Details. This might make relating data easer

Should I go back to looking at an inventory system???

Once I settle on a build plan I can start working through how I can tie it all together. I just don't want to spend weeks building something that I will regret and have to start all over again!!!!

Alan

 

[The_Doc_Man]

Were it not for the time-dependent nature of your information, you have a classic inventory system in which you are your own supplier for the final product. However, there are all sorts of snags in getting from point A to point B here.

The first and foremost snag is that you want oldest product first, which suggests a queueing system. Oldest first means "youngest create date first" of course.

The second snag is diversion / disqualification of expired product. Here, a "divide and conquer" approach might help. Think about this as a possibility: Before you begin filling ANY orders for the day, run a query that removes from available stock any items now over their age limits. In other words, run "inventory shrinkage" first. What is left is less difficult to assess because you have separately imposed limits BEFORE attempting to fill orders with what is left.

There is a tremendous urge here to slightly denormalize the database by storing the quantity remaining in each batch so that you could quickly tell that a batch is no longer meaningful for order filling. However, in a shared database, that is a no-no. On the other hand, if one and only one person ever prints "product-pull" tickets, i.e. that function is NOT shared, it might make sense in this case. That is a pragmatist's view.

The purist's way to do this is that your product key has to contain not only your product code but the batch number as part of the key. Write a summation query that sums product puts and product gets by both product and batch number. (Write it as a UNION query such that the pulls are subtracted while the puts are added.)

Then, you would have to write some sort of looping VBA that traverses your product inventory by oldest batch first (post-shrinkage, of course) to create pull records that match up product code only and write partial pull tickets. Each partial pull ticket takes its product from the oldest batch only in the amount still available in the batch. Your VBA code has to track how many items are in the partical ticket. Another query can summ up the pending pull tickets to show how much product has been pulled so far. The VBA code would stop in either of two cases:

(1) no product left to pull in ANY batch, or
(2) the order is satisfied.

The catch here is that when a partial pull ticket is generated, you will have to store the pull ticket and REQUERY to see what is left in the next batch.

This sounds confusing and it might require a multi-layer query to filter out all product records where the sum of puts and pulls is now zero. (You might also just go back and flag the batch records when that batch is fully exhausted, though that is a technically improper action. But hey, I've never denied being a pragmatist.)

No disrespect intended, but I'm really glad this is your problem and not mine. It is not a sweetheart under any circumstances.

 

[Avick]

Thanks for that information. Strange as it sounds it all add's up in some way.
This is by far the most difficult project I have had to build. In a perverse way I'm kinda enjoying it

No disrespect intended, but I'm really glad this is your problem and not mine. It is not a sweetheart under any circumstances.

If I get this to work as expected then it will be so heavily copy writhed the company won't be able to Fart without paying me money first

I will write back here if I manage to get things sorted, or I need more help with the fine details.

Alan

 

[The_Doc_Man]

In the USA, if you do it as an employee by hire or by contract to the company, they own whatever you write. In Ireland, if your laws are friendlier regarding ownership of product, good luck. Seems to me that it would be a useful product if you can "get this to work as expected."

 

[Rabbie]

In the USA, if you do it as an employee by hire or by contract to the company, they own whatever you write. In Ireland, if your laws are friendlier regarding ownership of product, good luck. Seems to me that it would be a useful product if you can "get this to work as expected."

In the UK your employer often owns the copyright for work you do in paid time. Read the small print in your contract of employment

 

[The_Doc_Man]

In a perverse way I'm kinda enjoying it

{Darth Vader voice}The force is strong in this one.{/Darth Vader voice}

 

[Avick]

It's different here in Ireland.
I am being commissioned to create the database. I then give the user the right to use the software but not to distribute or copy it in any way.

The up side is, I can give it to who I want for as much as I want.

The down side, you can't get the price you would get for a complete bespoke software development for one company.
If it was to be handed over to them on completion then they can do what they like with it and you are out of the picture.

 

[Rabbie]

It's different here in Ireland.
I am being commissioned to create the database. I then give the user the right to use the software but not to distribute or copy it in any way.

The up side is, I can give it to who I want for as much as I want.

The down side, you can't get the price you would get for a complete bespoke software development for one company.
If it was to be handed over to them on completion then they can do what they like with it and you are out of the picture.

Good luck. My point was that these things depend on the small print between you and who ever is paying you regardless of where you/they live. Of course local custom defines the type of agreement that is likely to apply it is the contract that has the last word

 

[gemma-the-husky]

personally, i thinkthis would be hard doing it from scratch even without the batch references, and the addition of batch references makes it at least doubly hard.

in practice i think you may find that the app you develop here is so specialized it wont be that easy to make a generic stock system that you can offer to other people, although you will probably find the code useful.

the other thing is that if this is a large scale manufacturer (which it sounds like) then the numbers of records/users may mean access isnt robust/fast enough for the job.

just some thoughts.....

 

[Avick]

Hi Gemma-the-husky

You could be right with how specialized the database is but I will be targeting a specific business model.
I have done this before and it worked so well that another 4 companies decided to hire me for there data management. In that case it was Paper and Cash register roll companies.

This one is a little more complex.

As for the power of access.
People often underestimate the power of it!!

I have one database in a company that has over 60,000 records and complex calculation with 4-6 people accessing it at the same time and its never caused a problem.
We had considered upgrading to MS SQL but it was not required. This company runs its entire factory off this database.

You would be more then surprised at what you think is a complicated back end database is in fact MS Access. And this is the case for some very high profile companies.

 

[Avick]

After a good night sleep and a look through some other databases I have on file. I think I have found a solution to my out of date stock.
And the good thing is, no input is required from the user.

I don't want to completely remove out of date stock. They will need to know what Batch Numbers (Stock Items) can not be sold so I have been thinking of the following.

Create a query with the following argument
IF current date is > creation date+lifecycle THEN expired = YES

Then filter the Expired field to only show results with NO

Create a second query that will show only results with YES. This query would be tied to nothing and will only be used to list stock to be destroyed. I might even create an update query that will remove records that have a YES value for the destroyed field.

Alan

By taking this route I can keep the integrity of the database intacked.

IF anyone can find holes in this then could you tell me where????
Sometimes its hard to see the wood for the trees

 

[The_Doc_Man]

Sometimes its hard to see the wood for the trees

Amen, brother.

Your idea is not wrong. It is exactly in line with my earlier comments on "shrinkage" as an action BEFORE you do stock draws.

As to whether you delete obsolete stock records once depleted, I wouldn't do that right away, but it IS a form of archiving. Perhaps you will want some sort of statistical analysis at the end of a week, month, quarter, etc. Keep it until it is of no further value for analysis. THEN dump the records and compact the DB.