Creating a Cycle Count DB (1 Viewer)

alikona

Registered User.
Local time
Yesterday, 20:23
Joined
Dec 27, 2019
Messages
11
Hi all,

I've been thinking about how to implement a database to assist some of my coworkers in running cycle counts. Currently, the inventory system we use does not perform this very well so we do it manually via excel sheets. This is incredibly time consuming/tedious. This database idea has been difficult for me to wrap my head around. Input/ideas is welcome!

Current process:
The inventory manager creates a schedule at the beginning of the year. A worksheet is generated in excel with the scheduled part numbers. It is split up into print sections. There are populated fields and blank fields to fill in. Someone is given the printed worksheet and fills in the blank fields. They return it to the manager and they enter the data into the excel sheet and compare to the stock quantities. If the count is off, they calculate the inventory dollar amount. If it is over a certain amount, the personnel will need to recount and record that data. Adjustment Qty is listed if needed.

Printed section
Populated fields: Part Number, Part Description, Pick Sequence, Unit Cost
Blank Fields: Lot# (could be multiple), Count Qty, Recount Qty (if needed)

Section Not printed
Populated fields: Count$, System Qty (quantity in stock), System$
Blank Fields: Adjustment Qty (+ or - value)

There are some curve balls to this.
  • We cycle count on a weekly basis.
  • There are two departments that manage their own cycle counts.
  • We use an ABC approach to cycle counting, meaning each part is evaluated for critical/cost/inventory dollars on a yearly basis.
    • A = finished goods, $20,000 or greater inventory dollars. Need to be counted 4x a year.
    • B = Between $10,000 and $19,999 inventory dollars. Need to be counted 2x a year.
    • C = Less than $9,999 inventory dollars. Need to be counted 1x a year.
  • We are regularly adding parts throughout the year. New parts are automatically entered into the system as 'B's.

The database would ideally be able to handle the following:
  • List of part numbers/info - easy
  • A way to import (excel report) stock quantity for the yearly category review/schedule creation and for the weekly cycle count
  • For the yearly category review/schedule creation, ideally be able to assign cycle categories based on imported inventory dollars or whether or not the part is a raw material/sub assembly or finished goods. It would be favorable to have the ability to compare previous year vs suggested values and accept/deny the suggestions. Reasoning for this is perhaps we are backorded on a part in January when we run the report but know we have very large orders scheduled for the rest of the year.
  • Save a historical record for cycle category changes.
  • Generate a schedule for the two Locations.... basically determine how many counts need to be made in the year (A*4)+(B*2)+(C*1)/52 and then equally distribute random week number for when the part should be counted. Ideally keep historical data on this as well.
  • The ability to track new parts added throughout the year and if necessary, manually add them to a cycle count. Sometimes its a few hundred parts... this year it was around 3k parts that were added throughout the year.
  • Form that can query selected year/week number.
  • Generate a cycle count report for for personnel to fill in.
  • Save historical records for each cycle count.

I need some brainstorming on how to accomplish all of this (if some of this is even possible/worth the trouble). Does anyone have any thoughts/opinions?
 

vba_php

Forum Troll
Local time
Yesterday, 20:23
Joined
Oct 6, 2019
Messages
2,884
alikona,

Your description of this is quite long, and in general this type of advice would be something I would charge for because a detailed response, to be effective, would require a lot of content. However, here are some tips from a non-developer's prospective:

  1. as far as tracking historical data of any nature, google the string "ms access audit trail" and you should get some returns that show you how to track both data entry records and field changes by users via the form interface.
  2. regarding inventory tracking, I believe MS's northwind.mdb database example contains an inventory management system in it. However, I believe it only consists of 2 or 3 tables and I personally think the method they illustrate in it is pretty strange.
  3. in terms of querying records that have date/week/time data attached to them in a separate field, doing that would be easy more than likely because you would just have those time/date fields in the same tables as the relevant data you want to see.
  4. I have rarely ever worked with a person running a business that required a DB setup consisting of any other relationships other than 1-to-manys. There have been a few instances, but again, not many. I'm a huge proponent of those relationship because it makes adding/dropping records easier, writing query statements easier, and general documentation easier. Not to mention if a client wants to understand the actual build of the thing, they usually *get it* when looking at a blue print of the structure as a report print out. IMO, it sounds like you may need more of a set up than just a bunch of 1-to-manys, but if you do my personal recommendation would be to utilize this type of relay as much as possible. I also have used lookup tables quite extensively, but I believe either Micron or Moke said in an earlier thread today that looking up data in a table via a query statement is bad practice. But that's different than what I'm talking about, which is basically storing data for rare referencing purposes that is unrelated to most other data in your requirements.
That's about all I've got. Hope it can guide at least somewhat.
 

plog

Banishment Pending
Local time
Yesterday, 20:23
Joined
May 11, 2011
Messages
10,147
I need some brainstorming on how to accomplish all of this (if some of this is even possible/worth the trouble). Does anyone have any thoughts/opinions?

Lots of good detail, but one big missing component--your experience with Access and databases in general. That, not the specifics of the system you described, will be the stress point of this endeavor.

My hunch (which are pretty accurate and imply no judgement) is that you have at best a little exposure to Access, perhaps none. If so, I suggest you break off one piece of that long features list and focus on that (perhaps the task that is currently the most tedious and labor intensive).

Read up on normalization (https://en.wikipedia.org/wiki/Normalization), work through an SQL tutorial (https://www.w3schools.com/sql/), and then start working on that feature, posting on this forum when you need specific help.
 

AccessBlaster

Custom title here:
Local time
Yesterday, 18:23
Joined
May 22, 2010
Messages
2,895
Are you going to include bin locations? Also will the data have to be re-entered into other software i.e. proprietary software. The latter is why people put off this type of endeavor duplicating work really blows.
 

moke123

AWF VIP
Local time
Yesterday, 21:23
Joined
Jan 11, 2013
Messages
1,949
@ADAM,

Code:
 but I believe either Micron or Moke said in an earlier thread today that looking up data in a table via a query statement is bad practice.

Please speak for yourself especially when you are dead wrong, which is way too often.
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 21:23
Joined
Feb 19, 2002
Messages
30,163
If you have tablets or cell phones or laptops that you can take into the warehouse, use them to update a spreadsheet as people are doing the counts to save the extra data entry step. Then import into Access when back at home base.
 

Users who are viewing this thread

Top Bottom