Help: Expiry dates, batch numbers, and stock levels problem

[hhb2635]

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 works 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 same or different expiry date but different quantities e.g. product 1 batch 123 pack 1 exp 2/14 qty 20; product 1 batch 123 pack 2 exp 4/14 qty 10) 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 each 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 wouldn't 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 don't come in straight away.

Also just off topic a bit does anyone know how i can import the data from the weekly 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.

Thanks in advance!

[maximus]

Can you please upload what you have done so far.....