Free Lessons
Courses
Seminars
TechHelp
Fast Tips
Templates
Topic Index
Forum
ABCD
 
Home   Courses   Index   Templates   Seminars   TechHelp   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.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 LinkedIn Email Richard Rost
Click here to sign up for more FREE tips

 

 

 

The following is a paid advertisement
Computer Learning Zone is not responsible for any content shown or offers made by these ads.
 

Learn
 
Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
Seminars
More...
Customers
 
Login
My Account
My Courses
Lost Password
Memberships
Student Databases
Change Email
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Help
 
Customer Support
Web Site Tour
FAQs
TechHelp
Consulting Services
About
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
PCResale.NET
Order
 
Video Tutorials
Handbooks
Memberships
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Contact
 
Contact Info
Support Policy
Mailing Address
Phone Number
Fax Number
Course Survey
Email Richard
[email protected]
Blog RSS Feed    YouTube Channel

LinkedIn
Copyright 2024 by Computer Learning Zone, Amicron, and Richard Rost. All Rights Reserved. Current Time: 3/28/2024 7:44:24 AM. PLT: 0s