Inventory Control, Location Table

[RainLover]

Sorry to intefere but,

@RainLover please tell me if I'm wrong, in your sample, based on Allen Browne's code, stock take is a manual operation. You have to manually input the desired date for the procedure to calculate stock in hand.
What if we had over 200 different products, multiple warehouses, and need to perform stock take each day, for each warehouse separately. Is there a way of doing it automatically?

Thanks.

If you use a Main form where you enter the Date and Stocktake.

In the Sub form link the two together via the Parent Child fields.

From here on as you enter a record into the Sub Form the Date and Stocktake get entered automatically. Do you understand? If not I will have to adjust the sub form for you to explain things better.

Please let me know.

 

[AlexN]

Brilliant thought!

I can manually enter only transactions though, and by this way have the stock take updated only for the dates of transactions. So problem still remains for the days there's no transaction.
Still can't combine prices table with stock take table, 'cause prices table is updated everyday (by insterting data from a .txt file) and stock table will not have all dates. And surely I can't update manually product prices for 200 products, daily.

I'll try adapting your idea though, somehow, but I see it already I won't work.


Thanks!

 

[RainLover]

I can't see what the problem is now.

Why do you need to enter new prices every day. Why do they change on a daily basis. What product are you dealing with.

 

[AlexN]

I can't see what the problem is now.

Why do you need to enter new prices every day. Why do they change on a daily basis. What product are you dealing with.

Ok, let's go once again.
I have 4 stores holding up to 200 products each. There are only incoming and outgoing products (no backorders or anyhting else). I have reached the point of designing data input for purchases and sells but now I want to go further, I want to know the value of products in each store for everyday, in a historical way I can present it on a chart.
I have a table with product prices over the last two years, with data on a daily basis. I would like to combine everyday's stock in hand for each store, with this table in order to calculate value (let's say this for product A, this for product B, etc, this for total).
The problem is that for the days there are no transactions, my aggregate (DSum) query has no records, while on the other hand, there are prices for the products in hand that change the stock value. I don't know how to do it, and thought that taking the stock in hand (Allen's approach) on a daily basis would lead somewhere. I can't do it manually though.

That's it. Hope I described well enough now, considering English isn't my spoken language.


Thanks

 

[RainLover]

What is this DSum for?

I think you need to post a copy of your Database.

What is the final result that you want. Is it the actual profit made or is it the average profit or is it something else.

Please explain the best you can.

 

[AlexN]

What is this DSum for?

I think you need to post a copy of your Database.

What is the final result that you want. Is it the actual profit made or is it the average profit or is it something else.

Please explain the best you can.

Well,
We have buys and sells. For UI interface reasons and for the reason the info we enter for each kind of transaction differs a lot, there are two separate tables and two separate data entry forms, one for each kind of transaction. Then there’s a union query that unites these transactions and comes to an aggregate query that calculates balance of product quantities. There’s where the DSum with product, store and date criteria, comes.

So, using the results of this query I have the stock in hand for every store, but only for the products involved in the transactions, and only for the dates transactions were made. There’s no info for resting products and for dates with no transaction. So I can’t combine prices table (already existing and concerning all products and all dates) to calculate value of every product’s stock in hand, in a historical way (on a daily basis).

Final result should be a table (or query) with a date field bearing all dates from 01/01/2013 till today, a field for products (productID), a field with the storeID, and a field with quantities in hand for every product, for every store, for every day.

Hope this time I explained well. I’m afraid I can’t do any better, and posting the database is out of the question because I’ll have to translate all captions, all products, and everything else that is in Greek which I believe you don’t master.

Thanks for your kindness and patience.

 

[RainLover]

I posted a copy of Allen Browne's Database.

Why are you not using that.

I do not understand your design

I cannot help other that what I have given already

 

[AlexN]

I posted a copy of Allen Browne's Database.

Why are you not using that.

I do not understand your design

I cannot help other that what I have given already

Thought so. Thanks for help already given.

 

[mjdemaris]

@jdraw and @RainLover: Here is a better description of what I am facing.
Our plant has about 150 employees any of which can come to Parts Room 1. We have three Parts Rooms, but the other two are accessed by just a few people. So, we have about 3100 parts and counting. The Parts Manager handles most of the ordering and stocking. If others order parts, they come to him first, then go out to the other Parts Rooms.
Our Excel program allows users to search and sign out parts, by selecting the part, getting it off the shelf, and entering their initials and quantity they are taking. The date is auto-entered. The On Hand quantity is calculated, and a flag is made if the On Hand qty goes below the Normal levels, and additionally if it goes below the Critical levels.
Our Maintenance Techs are responsible for monthly stock takes, in a section at a time. When the Techs do a stock take, it goes to the Manager for review, and if the discrepancy is large, he double checks the physical inventory and the sign out sheet.
So, we have three Parts Rooms, and possibly duplicate values for locations within each Parts Room. For example PR1, Aisle 1, Section A, Shelf 1.
Therefore, we end up with a hundred or more locations. If I keep the location fields separated on the form, so that each is modified individually, this might work unless there is an invalid location. In this case, I would need to have one table with ALL the locations listed, or three tables with all the locations listed for each Parts Room.
Simplicity: yes.
Unique: ...I think so, so that an invalid location cannot be selected.

Thanks.

 

[RainLover]

Why do you not answer my questions as asked.

Is there a problem?

 

[mjdemaris]

@Rain: The problem is how should I go about using the bin location? What is a good, solid method of saying that Parts Room 1 has 250 locations, allowing the manager to move a part to any other location?

That's the problem.

 

[mjdemaris]

Hi,

You may want to consider using a cascading combo boxes (you may also search the topic for this). What it does is, when you select WH1 in the first combo box, it will limit the values (or in your case the locations/shelvings) that a user can choose on the next combo box.

Ok, sounds good...but how do I limit the choices, using tables/queries? I just took count of about how many locations we have in Parts Room 1, and there are about 250. In #2, currently only about 20.

Thanks.

 

[RainLover]

Why do you need to limit.

A lot can change in the next year or so.

 

[mjdemaris]

Well, they can't just pick some arbitrary location that does not exist.