Access 2007-2019
Access 2000-2003
Access Seminars
TechHelp Support
Tips & Tricks
Access Forum
Course Index
Topic Glossary
Insider Circle
 
Home   News   Tips   Glossary   Templates   Forums   Help   Logon   Order   Contact Us  
 
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.AddNew
  rs!ProductID = Forms!ProductF!ProductID
  rs!DateScannedIn = Date
  rs.Update
Next

rs.Close
Set rs = Nothing
Set db = Nothing

Me.Refresh

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 Facebook Richard Rost on Twitter Richard Rost on Google Plus 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:

 

Learn
 
Accessindex
Excelindex
Wordindex
Windowsindex
PowerPointindex
Photoshopindex
Visual Basicindex
ASPindex
Seminars
More...
Customers
 
Account Login
Online Theater
Downloads
Lost Password
Free Upgrades
Insider Circle
Student Databases
Change Email
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Articles
Search Our Site
Waiting List
Production Schedule
Collapse Menus
Help
 
Live Chat
Customer Support
WalkThru Tutorials
Troubleshooting
FAQs
TechHelp
Consulting Services
About Us
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Order
 
Video Tutorials
Handbooks
MYOLP Memberships
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Gift CDs
Contact
 
Live Chat
General Info
Support Policy
Contact Form
Email Us
Mailing Address
Phone Number
Fax Number
Course Survey
Facebook    Twitter

Google Plus    LinkedIn

Blog RSS Feed    YouTube Channel
Richard Rost Microsoft MVP