Cycle Counting Database

[esi]

I'm trying to create a database to perform weekly cycle counts on our
inventory. I have a table with all items listed and a table which provides
a schedule for the counting. I'm not sure where to begin. Each week I want
to be able to bring up all items to be counted(based on my schedule) on a
form and report, enter the count values, and then the next week make sure
these item are not counted again. What is the best way to keep a log of
these counts? Do I need to create a table for each week? I want to be able
to keep a history of my counts? Any help would be appreciated. Thanks

 

[Kevin3NF]

If you are working with weeks, assign a week number to each product (1-52),
and pull a query with the appropriate week number in the Where clause. I
would keep inventory count history in its own table, away from surrent
stock.

Just shooting from the hip here....

--
Kevin Hill
President
3NF Consulting

www.3nf-inc.com/NewsGroups.htm

 

[esi]

Kevin,

How would I go about keeping the history table and flagging items that have
already been counted?

 

[Kevin3NF]

Create a new table called history with the relevant fields, and some
mechanism to populate that table with the "current" row form the inventory
table. There's about a dozen different ways to do this, based on exactly
how you want to manage your data and your business requirements.

Possibly an Inventory table that has a separate row for each count, so a
product could appear many times, with different dates and quantities, which
would provide for uniqueness and history.

For preventing duplicate counts, if you only want to count an item once a
year my earlier suggestion of using a week number works. You prevent
duplicate counting by generating a list for each week. So if widgets are
counted on week 1, and small doo-hickeys on week 2, if you query for all
week 2 items you would get doo-hickeys, but not widgets. Make any sense?

--
Kevin Hill
President
3NF Consulting

www.3nf-inc.com/NewsGroups.htm

 

[esi]

My thought was each week to have the items to be counted populate on a form.
The user would then enter the on hand value for the day and once all items
were counted, the history table would be updated with the user on hand
count, system count, and date. I'm sure this will involve some pretty
tricky code to make sure this process is done correctly. I think I have a
good place to start.