Database Answers City Temple.

Home Ask a Question Careers Contact us Data Models First Timers Search Site Map
Inventory Control Control for Publishing
Back to the Data Model
Requirement is a Database for Inventory Control for Publishing.
Requirements were defined by the User in a sequence of Chapters :-

Dated : 8th. April 2004 :-
Here's the scenario:

We're trying to track our newest choral and handbell products, which we call New Issues. 
These items will change every six months. 
There are about 30 of each, and we need to keep track of where they are.

LOCATIONS - Three possible locations: Bang (our printer), Backstock, Shelf.

Four totals: @Bang, Backstock, Shelf, and Total.

Seven possible actions: Printed (starts inventory count), Shipped,
Shelved, Promoted, Tossed, New Issues (a mailing), Physical Count.

The plan is to have a form that tracks each product and displays totals for the three locations. 
Within that form would be what I'm calling the Action subform. 
That would list Date, Action, and movement. 
e.g. 15,000 printed (so 15,000 are entered into the @Bang column), 
      6,000 shipped (add 6,000 to the Backstock column and subtract 6,000 from @Bang), 
      1,200 shelved (add 1200 to Shelf column, subtract 1200 from Backstock). 
The numbers in those columns (Bang, Backstock, and Shelf) should update the totals.

Lingering question #1: 
Is there a way to make physical count a different kind of equation, so our 
warehouse manager could simply type his count (e.g. 864) and it would not act 
like the usual sum, but just update the shelf when the action "Physical Count" is selected? 
Kind of like a reset, I guess.

Lingering question #2: 
I'd like to be able to track backstock location.
We use a system like E-5 or M-1 for our warehouse, so I don't know where
the best place to put that field. Occasionally, our stock would be broken up, 
so we might have 2000 in K-2, and 3000 in J-4.


Dated : 7th. April 2004 :-
Basically, I would like a Database that will track about 60 products in three locations. 
I've started with our choir music, and I think I have the layout right, but I thought if there was
something similar, I could modify it to suit us. 

I just need Code #, Title, Category, and then probably a subform that would have a date,
action lookup, then a column for each location where we would enter
movements. (e.g. 5100 printed, 2000 shipped, 600 shelved, etc.).

Those movements should update totals on the main form.

I don't know yet how to do a subform, and then I'd need to learn how to
automatically sum the columns to dynamically update the totals. I've
attached my first attempts.

Thanks a ton for any help.

- Harley

B. Things of Interest :-
B.1 Items
B.2 Locations
B.3 Product Categories

C. How are they related ?
C.1 An ITEM can be held at any or all of three Locations.

Barry Williams
8th. April 2004
Principal Consultant
Database Answers


Home Ask a Question Careers Contact us Data Models First Timers Search Site Map