Unanswered: Help: Expiry date, batch and stock level problem
Hi everyone, new to the forum, fairly new to access (only know how to do e.g basic queries) and need someone's expertise. What im trying to do is create a stock database and iv imported a blank version of the northwind template and filled it in with my own products which is fine up to a point.
What i also have though is i get weekly reports of the stock of each product with their batch numbers, expiry date and quantity remaining for that individual lot sent to me from another company. (So i might have several lines with the same batch number and expiry date but diffeent quantities e.g. product 1 batch 123 exp 2/14 qty 10; product 1 batch 123 exp 2/14 qty 40; product 1 batch 222 exp 4/15 qty 30) So what i want to ask is:
Is there any way for me to combine the data in the weekly reports with the stock level data in the northwind template?
What i want to be able to have is where somehow i can see the total quantity of eac product with the same batch number and expiry date and then it tells me when that batch has expired and it also automatically deducts the stock from my total stock when it gets to within e.g 9 months of the expiry date as we will not likely sell those.
I need to do this as at the moment all i can get from my database is how much stock has been ordered, sold and what we have in total on hand but if the stock is close to expiring then i wouldnt know and i need to be able to see when to order stock in advance as i want a minimum of 6 months cover and ideally 9 months cover as orders for stock dont come in straigjt away.
Also just off topic a bit does anyone know how i can import the data from the wekly reports into excel/access easily as they come as word files presented as a table but the data is put in frames i think and iv tried removing them but then it all looks messed up.