Inventory Database

[coyote]

I have a client with a database for a clothing line shop which was designed by somebody else. Everything else is working except now He needs a way of accounting the daily stock. I have a way of showing TODAYS daily stock by category e.g Formal Trousers, Blouses, Jeans Trousers e.t.c . The problem comes when I want to know the Daily Stock of a Date two months ago. or even yesterday.

This is because the way the DB is designed Products are entered individually i.e
If its Formal Trousers instead of assigning a General code for all Formal Trousers
like FT00012 every formal trouser has its unique code i.e FT1,FT2,FT3.....
same case to all products in the DB.
Only the DateIn is recorded and the DateOut when the product is sold. A checkbox is ticked when the product is sold. Every thing else in this DB is perfect except this one problem.
Its a very big DB so redesigning is out of question.
Any help greatly appreciated.

 

[qdogfball]

for your query you will want the Sold checkbox field to be null, correct?

Then you will want the Datein to be Greater > than the query date the user inputs.

Should be a simple query.. I would think.

So what am I missing?

 

[missinglinq]

I have a way of showing TODAYS daily stock by category e.g Formal Trousers, Blouses, Jeans Trousers e.t.c . The problem comes when I want to know the Daily Stock of a Date two months ago. or even yesterday.

If you already know how to show TODAY'S stock, you either need to assign this info along with the current date to a table designed for this purpose or produce and hold (either in hard copy or as part of the db) a report of the info every day. WHcih you do would depend on what you are/may have to do with the historical data.

 

[pbaldy]

It should be fairly simple. The checkbox is not necessary, as the presence of a date in the date sold field does the same thing.The inventory for any given date is those items acquired before/on that date whose sold date is either null or after the given date.