Inventory Products vs. Units
How to track specific units for each product
Q: Our company uses an Access database to record all
deliveries into our warehouse. In one field we create an Autonumber
which we convert to a barcode for printing and attach to each delivered
pallet or item. The issue I have is that aside from this one field, most
loads contain exactly the same data in all other fields. So we may have
to enter 30 records with the only difference in each record being the
auto generated number - so we duplicated the same record 30 times
manually. I would like to be able to enter the 30 records in a single
entry but have it generate the 30 individual records in our delivery
table so I'm able to generate the individual barcodes. >Steve
A: Steve, instead of trying to
shove all of this data into one table (including all of the duplicates)
you should make two tables - one to store the part / item information,
and another to store the individual IDs of the units as you scan them
in. Here's how I would build it:
First, you need two tables. I would build an ProductT table that has
information on the product / part itself:
So entering in some sample data for your product line, you might have
Notice that this table doesn't tell you anything about how many
units you have in stock, or what the actual unit details are. This is
just information about the products in general.
Now you need to create a second table to store specifics on the actual
units themselves. I'll call it UnitT.
Notice I've got a UnitID which is the Autonumber and it uniquely
identifies each ITEM you scan in. I've got a ProductID which is the
foreign key and relates each item back to a product type. Then I added a
DateScannedIn and DateScannedOut field so you can track when the items
come in and go out... this is optional, but it's just for illustration
Here's some sample data:
Notice that on 3/1/09 I scanned in three ProductID 2s (mouse). I scanned
one of them out on 3/3/09. You can see how you can use this table to
track inventory quantity levels too (if you have items without a ScanOut
date, they should be in stock).
On 3/3 I scanned in two ProductID 1s (keyboard) and then I scanned
another keyboard in on 3/5, but I scanned one of the first ones out.
Then, on 3/7, I scanned in two 21" monitors (ProductID 4). See how
this table tracks ins and outs of specific UNITS without duplicating any
of the PRODUCT information?
Now, we need to build a form for the Products. Nothing fancy here, just
a simple form:
Now I'll build a subform for the Unit information, which I'll set up as
a continuous form:
The Unit form will look like this by itself:
But if you drop it into the Product form as a subform, you get:
Access should automatically set up the relationships for you (link child
/ parent IDs of ProductID). Now notice when you're on product 1, you see
all of the units for product 1 in the subform. Switch to product 2 and
you'll see all of the units for product 2, and so on.
So for your specific database application, now all you need to do is
create 30 new records in the Unit subform if you want to add 30 new
items to your database. You could even do this with a button and a loop
to automatically add X unit records. You could do this with an append
query, or a recordset. I'll show you:
I'll drop a button on my ProductF and cancel the wizard if it starts up.
Go to the Code Builder. Here's the VBA code I used:
Private Sub Command5_Click()
Dim X As Long, Y As Long
X = CLng(InputBox("How many units to add?", "Add Units", 1))
If IsNull(X) Or X < 1 Then Exit Sub
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset("UnitT", dbOpenDynaset)
For Y = 1 To X
rs!ProductID = Forms!ProductF!ProductID
rs!DateScannedIn = Date
Set rs = Nothing
Set db = Nothing
I cover all of this in my Access 320- series on Recordsets. As you can
see they make it real easy to add records to a table. Now, I'll just
pick a product, let's say Keyboards, and add some units by clicking on
I'll type in 5 and then hit OK. The recordset adds the 5 units to the
table with a ProductID of 1 and today's date for the ScanIn date. The
UnitIDs are high because I was playing around with the recordset,
testing it, and 'spent' a bunch of the lower IDs. No big deal.
Now you should easily be able to create a report that prints out your
barcode labels for the new items. I would personally create a "BarCodePrinted"
field (y/n) for the Unit table, and then you could just make a query
with just the items that have not been printed yet. When you're done
printing the labels, run an Update Query to set all of those fields
equal to Yes. Then slap the stickers on the products.
Hope this helps!
UPDATE: Here's a new video I recorded that explains the
Inventory Parts and Units and the table setup
By Richard Rost
Click here to sign up for more FREE tips