Expanding a number range from AC data entry form into table

[wynterBB]

I am building an inventory database that needs to track individual items. Each item has a unique number associated with it. However, we receive these items in bulk quantities, and distribute them likewise. I have set up a data entry form to input the start of the item number range, and the end of the item number range. For example, the item range would look something like this -- 1056-56701 through 1056-56800. The item number is always 4 digits followed by a dash and 5 digits. I am looking to expand the range that is put into my data entry form, so that each individual item number within the range is saved to my table. In this case-- 1056-56701, 1056-56702, 1056-56703, 1056-56704....156-56800. I also need to save the corresponding info for each item range as it is saved as an individual number. That would include things like 'date received', 'size', etc.

I have read some other responses to similar problems, but I still can't get this to function properly. Any thoughts?

[PhilOfWalton]

Please clarify. Am I correct in assuming that you have a form with 3 fields on it
Item your 1056
StartRange your 56701
EndRange your 56800

Then you want to press a button and add these 100 numbers to a table in the form of a string. Let's call these numbers ItemIndividual. I presume this will be a Unique Key.
At the creation stage you want to add DateReceived & Size. Does that only apply to the Item or the ItemIndividual as well.
What additional information is needed against the 100 ItemIndividual numbers. How do you intend to display the 100 numbers (you can display about 30 lines on a continuous form). How do you intend to find the ItemIndividual?
Don't worry.creating the ItemIndividual string is easy with a loop

Phil

[jforbes]

There are usually two way of tracking Inventory Items, Serialized and Lot. Serialized Items each have a Unique Number that is either assigned to them as they are received or captured during receipt. Lot items are similar in that the lot is either assigned or captured at receipt of the items, but it not unique across the items; a single lot identifier can be assigned to multiple items.

It sounds like you are assigning numbers to Serial Tracked items on receipt. Usually in this case, a Form is presented to the Clerk, person receiving the material. They enter in the Item Number (or Part Number specific to the type of item to be received) on the Form and the Form determines the Next available Serial Number. The Clerk then enters in how many are to be received into inventory and clicks a "Receive" button of some sort to:

• Print out the Serial Number Labels to be placed on the Individual Items.
• Create entries into a SerialTrack table for each individual Serial Number used. Often the Item Number is included if there is a chance that the same Serial Number can be used on different Items.
• Receive the Items into Inventory by incrementing the on hand counts, or creating inventory transaction records, or both.

I've worked on two different systems that insert Serial Numbers into the SerialTrack table. Both of them use a loop that counts from the First Serial Number to the Last. The Distribution Style system did this early in the process and stored the results into a temp table and when the "Receive" button was pressed an Insert Query was ran with the Temp Table as the Source Table. The other, Manufacturing System, looped through and created the Serial Numbers and performed individual inserts when the "Receive" button was pressed. There was very little real advantage to either system, other than the one with the Temp Table also gave a method to manually enter the Serial Numbers.

So how are you going about this and what problems are you facing?

[wynterBB]

I actually have a only 2 fields on my form. I could separate out the prefix and affix, but I kept them as one string because only the entire number is unique. So, if I were to separate those numbers into 3 fields, I would no longer have a unique key. The other corresponding info like DateReceived and Size applies to ItemIndividual. I intend on finding the ItemIndividual by creating a search form. The search form will allow you to search the actual number, or search by date, and other fields.


I don't necessarily need to display these 100 numbers in a form. I can do so with a query. But, I do need to track each item to the end of its life. That includes; which items were issued and to whom, the date they were issued, whether that item was used, etc. When an item is "used", I need to take it out of inventory.

[wynterBB]

JForbes, that is really great information. Thank you. So to answer your question, I am facing several problems. The biggest is that, this is my first attempt at creating an Access database. So I imagined that I would have two major tables-- a Receivments table and a table displaying the Ownership of those items and other important info. It is critical to know who the item was issued to (the owner), especially since owners can transfer individual items and items in bulk between one another. Items can also be "used", and at that point, I would want to take them out of the inventory.

So my biggest struggle is conceptualizing how this will actually function. We need to track individual items, but at the same time, we want to be able to input data and create reports using a range of item numbers.

[PhilOfWalton]

I have a form with 3 fields
Item
StartRange
EndRange
and a command Button CreateNumbers

Option Compare Database
Option Explicit
 
Function OutPutKey() As String
 
    Dim i As Long
    Dim OutputStr As String
 
    For i = StartRange To EndRange
        OutPutKey = Str(Item) & "-" & Format(i, "00000")
        Debug.Print OutPutKey
    Next i
 
End Function
 
Private Sub CreateNumbers_Click()
 
    OutPutKey
 
End Sub
 

I leave it to you to check the 3 input numbers are numeric and that EndRange > StartRange

Phil

[jforbes]

I think you are on the right track. This may be helpful to you: Database Normalization and Table Structures

This is the table structure I would create based on what you have provided so far, you may want different names, but this should give you the basic idea:

• Locations - You may want to call this Owners. This is would be a list of all the places that an Item could be stocked. Depending on how granular you need to get with this, it may just a basic location, like a persons name or Employee ID. But if you need to really pin things down further, like by bin locations, you may need to split this table up a little further.
• Items - A table for the Item and all it's information and with a Primary Key of ItemNumber. Often, an OnHandQuantity is maintained at this level.
• SerialTrack - A table for the information that is tracked per Individual Item with a Primary Key of SerialNumber. Or if you could have the same Serial Number used multiple times, you may want a Compound Key of SerialNumber and ItemNumber. It sounds like from your description that this is where you would put the Foreign Key for the Locations, so that you can determine each Individual Items Location.
• ItemTransaction - A Table that has a record inserted into it anytime an items is Received, Transferred, or Shipped. This would be your history Table. You may not need this, but it's a good idea to have it. It would include the ItemNumber, a FromLocation, a ToLocation and a Quantity. Depending on how you big our database will get, you could use queries on this table as the basis for your OnHandQuantities instead of maintaining a number in Items. Sometimes both methods are used in attempt to bridge the gap between the accuracy of a Query and speed of looking up a single number. Typically in this case the Query is run periodically and the results are stamped back onto the Item Record.
• SerialTrackTransactions - A Table that has a record inserted into it for each transaction against a SerialNumber. You would only need this if you need to keep a history of every place a Serial tracked item has been. This table can be a lot of work to maintain because you won't be able to do bulk inserts against it, so it will need a looping structure to iterate through Serial Numbers every time an Item is moved.

With a structure like the one above, you can Query against the SerialTrack Table to Report by SerialNumber, or Query against the Item Table to Report by ItemNumber, or lastly Query against the Locations Table to see who is hording all your inventory.

You would then need a Form or Forms for Receiving, Shipping and Transferring. These are all very similar functions and in your case, you may be able to consolidated it down into one Form. And since you have the need to ship a specific range of serial numbers from one Location to another, you probably should implement a Temp Table approach. That way you can tightly define the Serial Numbers that need to be transferred. Because there will be someone that takes a Serial Number from out of the middle of a batch, or one gets dropped and broken which messes up the nice neat range of Serial Numbers that are to be transferred.

Wow, that was a lot more than I intended to type this morning.