substract stock items from my oldest date

[judvos]

How can I substract a stock item from the total stock of that same item from my oldest date ? Secondly, if my stock from my oldest date is not enough, how can I substract from my second oldest date ?
E.G. If my stock article Crunch on 1 dec 2003 = 20, and on 15 dec = 5.
On the 20th I will use 30 crunches.

So how can I substract from my first oldest and second oldest date ?

 

[jebry]

Hi!

What's the point of this? I know that stock rotation is important but that is a physical thing not a computer thing. In your example above it seems that you are keeping transaction information and figuring totals from that so you do the same thing on the other side of the ledger and keep just transactions and subtract instead of add. No computer program in the world will tell you how old your stock is only a physical check can do that. If you are planning on using the program to determine what stock needs to be returned or tossed because it is too old, then a computer program will not give you the answers you need. It may tell you everything is okay when you stock is out of date.

hth


Jeff Bridgham
bridgham@purdue.edu

 

[docmeizie]

Actually you can create the program as long as you have a creation date, correct??? And then as far as showing which stock is the oldest, thats just a matter of ascending or descinding order. Now as far as making the program pull the oldest stock all you would have to do is run a query showing the maximum aged products and pull those from stock. A program can do it, the manual labor of shipping it and stuff will of course be up to human hands until we can figure out a way to make robots do that too.

If I take a peek in your Windows, to fix a problem, does that make me a "Peeping Tom"? Hmmmmmmmmmmv

 

[jebry]

Yes!

My point exactly (maybe some parts are stated more clearly) the only problem I see is like I said, trusting the program to give an accurrate picture of what is actually in inventory. This could lead to problems.



Jeff Bridgham
bridgham@purdue.edu

 

[docmeizie]

Okay I am going to give an idea that I use with all my databases that deal with money transactions. What I have is an Accounts table, Accounts Details table, and a Payments table. Now when I put this into form/subform view my Account Details and my Payments are subforms connected to the main form Accounts. These are connected by an Account Number. Each Account has charge items(Account Details) and each Account has payments to that account(Payments). Well lets relate that to your situation. Right now I can see Product, In Stock, and Out Stock being viable tables to pull this off. Product table would have basic information about the product(Name, ProductID, yada yada yada). In Stock table would have how stock you have on hand and what type and the creation date. And the Out Stock table would have stock that is going out to companies and blah blah blah yackitty smackity. Now to add to this you would have to create a delete query that would run once a certain stockpile hit zero, but that is easy to do. This should help you get started as to what you want to do. Of course this is just one means to an end that I saw, there may be other ways to skin this cat. Always choose what works best for you. Good luck.

If I take a peek in your Windows, to fix a problem, does that make me a "Peeping Tom"? Hmmmmmmmmmmv

 

[judvos]

Hi,

thanks for the reaction. The database is just to know the items that I have in stock. It's a wharehouse database. Items are coming in and going out. Based on my oldest date I have to select those items to go first out of the wharehouse.

And this is where i'm stock. I can append my incoming articles but my outgoing articles have to be based on my oldest date

 

[garethfx]

I have to agree with the comment earlier by jebry. As a retailer for many a year and having trained countless managers both in retail and warehousing. the sell by, use by and B.Before dates are important and to have them in a system is an idea BUT, you have to be realistic and consider the "picker" who is really responsible for the rotation and selection of the stock called off.

Depending on the product shelf life is normally 6 weeks
(chilled/ fresh not included). Therefore to cover yourself for the sake of trading standards and EHO it would be more acceptable to have a system of good training, enforced rotation and replenishment worked on "just in time".

Spot checks on your slot allocation is always best practice. After all, you may call off the lowest datecode and the human resorts to the " to hell this will do" thus leaving the low coded stock for the possible next time.

hope this helps in the decision making process?

Regards and Merry Christmas to all

Gareth

 

[docmeizie]

Thats why you give them not only a Product ID but also a date which will identify certain box to go first. Date coupled with ProductID will give a certain distinctness to it. That way if you give them a sheet on what needs to go, saying this ProductID 4356 with an expiration date of 01/01/04 you can come up with the ID of 4356-010104 which would be specific enough to be picked out. Now if an employee can't follow directions, then I suggest new employees. But as far as I have seen, this method works pretty good with little error.

If I take a peek in your Windows, to fix a problem, does that make me a "Peeping Tom"? Hmmmmmmmmmmv

 

[judvos]

Yes, indeed it is important to know which product goes first out of stock. The out stock is based on first in first out principle, which of course is bound to a receiving date. I do not work with the expiring date of the product just receiving date.

To substract my out stock from my in stock I have to pick the oldest date and if that is not enough the second. Thus leaving the first date / second date of my article with a total Qty.

But still I have to find the first date of my article to substract my out stock from my in stock.

If I have 20 in stock on 12-01-03 and my stock on a certain date will be 15, then I have 5 left.

 

[judvos]

docmeizie, earlier you suggested to make a query where it shows the maximum age of the products.

I tried to execute a query with the Dfirst function, but i get an error.

Which function do you susggest

 

[jebry]

Hi!

Group by your product ID and Received Date and Sum by quantity. Then order by Received date ascending.

hth


Jeff Bridgham
bridgham@purdue.edu

 

[docmeizie]

Sorry for the late reply, it being Christmas Eve we got to have the day off. Jebry has the right idea for running your query. That will show your products by the oldest date. Now I got to thinking about the setup that I initially advised of, and how to make it work right for you, well I came up with this:

1) For your Products you would want to query only records that have more than 0 in stock. That way when you run out of a certain stock it will just show stock more than zero ordered by oldest receiving date. Also it should have a running total of all your stock of the product ID only to show you how much you have total and not just one day.

2) Your In Stock table/subform will be responsible for showing how much you have received for a certain day and should have these fields among anything else that you need:
a. Receiving date
b. Total stock received(On Hand)
c. Stock on Order(Going Out)(count from On Order/Out Stock table/subform)

3) Your Out Stock or Ordered table/subform will be linked to your In Stock Table/subform so that you may keep a running total of what has been ordered from that stockpile of product. Viable fields for this table/subform would be related to each individual order for product, quantity going out and who got the product:
a. Date order placed
b. Quantity ordered
c. Company getting the order

Now your relationship for this should allow for a Main --> Sub --> Sub form format (arrowhead being the many side of the relationship. Your calculation will come into play when the sub sub form Ordered shows a running total of the quantity. This quantity should be referenced by Subform In Stock so that you can make a calculation on that form that will take away Ordered quantity from the In Stock quantity. That new result(Currently On Hand) will be tallied and referenced in the Main form(Products) so that you have a working register of whats going in and whats going out. But thats just how I see it working together cohesively as one unit. Let me know what you think.

If I take a peek in your Windows, to fix a problem, does that make me a "Peeping Tom"? Hmmmmmmmmmmv

 

[judvos]

Sorry for the late respond. At some point I got the result as docmeizie suggested. I have a main and sub form.
I had to work with an update and append query.

If I update my record in a recordset, the program updates the whole colum. Instead I need to update the first record in my recordset.
Do you know a VBA code to search for my first record in my recordset.

 

[docmeizie]

Which form are you updating, cause if you update the sub sub form then that would change the total of all the records.

If I take a peek in your Windows, to fix a problem, does that make me a "Peeping Tom"? Hmmmmmmmmmmv

 

[judvos]

Indeed, it updates all the records in that colum. My Outstock form has a query which updates the Inventory table.

Inventory table -
[wharehouse], [article number], [receive date], [expired date], [InStock], [OutStock], [Total]

I append my New InStock Items into the Inventory table and my OutStock needs to be updated with the field [OutStock] in the Inventory table. Because on 1-1-2004 I have 20 items. On 1-2-2004 I'll take 10 items from the date 1-1-2004, so 10 items remains. This goed on until my [total] hits zero. Then comes the second record.

Thus via a update query I need to update my [OutStock] field

Update Query. Inventory table has the following calculation.
[OutStock]+forms!OutStock!Qty

There's needs to be a criteria for my receive date to select only the first record in my recordset.

I used the rs.findfirst function but I didn't succeed.

 

[docmeizie]

Sorry so long for a reply, trying to get back in the mind frame of Access for a minute. In your query try using the min and max on the query in the outstock form. Or else you can use Like "*" & [Receive Date] & "*". That should narrow it down to the date that you are looking for. Worst case it will ask you for that parameter of receive date and you can enter that. Try and see what happens.

If I take a peek in your Windows, to fix a problem, does that make me a "Peeping Tom"? Hmmmmmmmmmmv