By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,209 Members | 1,092 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,209 IT Pros & Developers. It's quick & easy.

Inventory Management (With Serial Numbers)

blyxx86
100+
P: 256
Great insight needed!!

Good evening everyone.. I've come to a point where I can't even fathom how to go forward.

I'm attempting to create a serialized inventory. I don't know where to begin.

I need to keep a running inventory of serials by customer, by model and then by serial.

Customer 1>Model1>Serial1
  • Customer1
    • Model1
      • Serial1
      • Serial2
    • Model2
      • Serial3
      • Serial4
  • Customer2
    • Model1
      • Serial5
      • Serial6
    • Model2
      • Serial7
      • Serial8

I can get that far within a database using relationships... It now has to do with adding and removing them from the active inventory.

I could run a series of update/append/delete queries. Use an append query to add items to the inventory, a delete query to remove. The delete query being used on the 'Shipment' form, the append being used on the 'Receive' form.

That seems simple, but now here is the part that makes it tricky. Not all the Models have serial numbers, only quantities. To explain, I will demonstrate a scenario using more common things than I actually deal with; an office store for instance.
The office store has pencils, paper, computers and printers. The pencils and paper would not have serial numbers, but would still need to be kept track of using quantity. The computers and printers would have serial numbers.

I would run a delete query everytime I shipped a computer or printer where the serial number equaled the one in the customer inventory. I would run an append whenever a serial number came in. I would also have a stated quantity that should (theoretically) never deviate from the count of serialized inventory.
Just speaking of it is confusing me, perhaps because I've been working with this database for the past twelve hours, or just because it's complex.

If anyone has some insight, or maybe just some tips, I would appreciate it.

I've taken a look at Allen Browne's inventory system and am using it as a sort of guide..
Jun 7 '07 #1
Share this Question
Share on Google+
2 Replies


blyxx86
100+
P: 256
I think I'm going to have to do a few things that I really don't understand yet... I'm attempting to dive headfirst into this, but I can't seem to figure out how to set this up to work.

I'll have to have a one-to-many relationship for the JOIN table of CustomerModels (Using a many-to-many for Customers and Models)... and have the JOIN table have a unique ID that would identify the serial numbers to be part of the customer's inventory...

Which still leaves the quantity up in the air, and also the possibility of duplicate entries, which I'm not liking.. I'd still also have an issue with the regular "Quantity Only" items that do not have serial numbers...

I am still having a hard time imagining how to even set up the tables to keep track of this type of mixed inventory... Any ideas?
Jun 8 '07 #2

blyxx86
100+
P: 256
Here is what I've come up with. Using Allen Browne's example, and expanding upon it for each customer.

Now I'm still working on the serialized side, I think it's a start.

Jun 13 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.