Free Lessons
Fast Tips
Topic Index
Home   Courses   Seminars   Templates   TechHelp   Fast Tips   Forums   Help   Contact   Join   Order   Logon  
Tips & Tricks

New Tips Added Weekly!
Click here to get on our Mailing List


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 this:

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 purposes.

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


End Sub

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 the button:

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 difference between Inventory Parts and Units and the table setup better.



By Richard Rost   Richard Rost on Twitter Richard Rost on LinkedIn Email Richard Rost
Click here to sign up for more FREE tips


You may want to read these articles from the 599CD News:


Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
My Account
My Courses
Lost Password
Student Databases
Change Email
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Customer Support
Web Site Tour
Consulting Services
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Video Tutorials
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Terms of Sale
Contact Info
Support Policy
Email Richard
Mailing Address
Phone Number
Fax Number
Course Survey
Blog RSS Feed    Twitter

YouTube Channel    LinkedIn