individual numbering in a inventory database

  • Thread starter Thread starter Ashley Langley
  • Start date Start date
A

Ashley Langley

Hello,

I have an inventory database that I am working on for a small
maintenance facility. In the "old" database, each part was assigned an
individual number. The maintenance technicians are quite wedded to this
idea although they want the bells and whistles of a relational database.

Currently I have a normalized table structure where users can enter
information about an order that includes multiple parts. Right now I
have no good way of assigning an individual number to a part. I know
that it will require a separate table to stay normalized (with
individual part number and enough identifying information to match it
with the part shipment information); however I cannot figure out how to
automate the entry process so that a person can say we have ordered the
15 widgets and then find out the 15 individual numbers that go with it
(and make Access do the work of number generation).

Thank you for any insight you might have.

Ashley Langley
 
Is Access Autonumber data type not working for your purpose? Are you
trying to use the same numbers the techs remember from the "old" days?

Pavel
 
It sounds like they want basically a serial number for each part. If the
pars do not truly have serial numbers, I would tell them that was a
LIMITATION of their old system and I would not build it into the new system.
If you you pull 15 identical widgets, why add the complexity of telling it
which 15 you pulled, and which ones remain on the shelf.

That being said, you would need an additional table to contain each part
with the part number (pulled from your current table) and a field for the
serial number. The table you currently have could count the number of like
items in your new serialized table to determin on-hand. When you sell
Items, I would pull from the serialized table and I would make them pull the
part by serial number (or by part and serial) if the serial numbers will be
duplicated from one type of product to the next.

Rick


Hello,

I have an inventory database that I am working on for a small
maintenance facility. In the "old" database, each part was assigned an
individual number. The maintenance technicians are quite wedded to this
idea although they want the bells and whistles of a relational database.

Currently I have a normalized table structure where users can enter
information about an order that includes multiple parts. Right now I
have no good way of assigning an individual number to a part. I know
that it will require a separate table to stay normalized (with
individual part number and enough identifying information to match it
with the part shipment information); however I cannot figure out how to
automate the entry process so that a person can say we have ordered the
15 widgets and then find out the 15 individual numbers that go with it
(and make Access do the work of number generation).

Thank you for any insight you might have.

Ashley Langley
 
Don't attempt to use AutoNumber as the "PartID" number! And don't make your
legacy part number the primary key!

You surely have a table with a unique record for each inventoriable item.
One of its attributes is your old part number. Another is its description.
Etc.

You'll be able to use this table in a child a.k.a. subForm to select all the
parts in inventory that are shipped to a customer, or whatever you want to
do.
 
So are you saying that these part numbers are to be dynamically assigned per
order? Like part 1 of n parts?
 
Thank you for all the input. I have been of the mind to make them use
other identifying information rather than a single number. While I
could easily change the structure of the database to support the old
numbering convention, I had no idea how to generate those numbers (and
autonumber is insufficient) in a way that was transparent to the user.

I think I will proceed onward with the NEW and IMPROVED database.

Ashley
 
Back
Top