Sequence numbering for Inventory System (1 Viewer)

[gardo]

I am currently working on an access inventory system that will track all of our items and their categories (IT materials, office equipment, Office Supplies, communication materials, etc.) by generating automated sequential numbers as new records are updated into the system.
For instance: the “Item ID” attributed to “Office Equipment” is 100, the “Item ID” attributed to “IT materials” is 300. The first chair that got purchased or donated to our organization will be automatically labeled 100-1 in the “Item ID” field when I select Office Equipment in the drop down combo box on the “Item Type” field. A month later we purchased a new couch as part of our furniture stock and as we enter it in the record and select “Office Equipment” in the Drop down combo box of the “Item Type” field, the “Item ID” auto-increments automatically and becomes 100-2 although there are other records (following the same process in between).

I have inserted all the information below since I don't know how to use the VBA data and adapt it to my system. I know this may be a lot to ask…but finalizing this would be very helpful in helping us making sure our resources are distributed and monitored well in order to provide good service to beneficiaries.
I have received some suggestions but I don’t know how to adapt them to my system since I am not fluent in VBA:
I have gotten a suggestion...but can use it because I don't know how to adapt his code to my system.
Please HELP!

Below are all the fields exactly in the same order they are positioned in a table I named "Asset Inventory System". However the only relevant ones for this task are the "Item ID" field for the identifier (example: 200-1, 300-4, 100-6) and the "Item Type" field for the categories of item (example: Office Equipment, Office Supplies, IT Material, etc.) :

Item ID- Item Type- Description- Make & Model- Serial #- Unit- Condition- Location or Personal Use- Department- Cost or Value in HTG/ USD- Currency- Total Cost or Value- Purchase #- Date received- Donor- Loss/ Theft- Comments

Below are the item categories in a drop down box in the "Item Type" field as well as their identifiers (and example of increments) assigned to them.

Office Equipment 100-1,2,3…etc.
Office Supplies 200-1,2,3…etc.
IT Material 300-1,2,3…etc.
Communication Material 400-1,2,3…etc.
Audio & Video Material 500-1,2,3…etc.
Medical Equipment 600-1,2,3…etc.
Medical Supplies 700-1,2,3…etc.
Construction Material 800-1,2,3…etc.
Rolling Stock 900-1,2,3…etc.

I have gotten a few suggestions while searching online...here is the primary table to start with:

IF EXISTS (
SELECT *
FROM dbo.sysobjects
WHERE id = OBJECT_ID(N'[DF_SequenceControls_NextAvailable]')
AND type = 'D'
)
BEGIN
ALTER TABLE [dbo].[SequenceControls]
DROP CONSTRAINT [DF_SequenceControls_NextAvailable]
END
go
/****** Object: Table [dbo].[SequenceControls] Script Date: 05/22/2012 08:51:32 ******/
IF EXISTS (
SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[SequenceControls]')
AND type in (N'U')
)
DROP TABLE [dbo].[SequenceControls]
go
/****** Object: Table [dbo].[SequenceControls] Script Date: 05/22/2012 08:51:32 ******/
SET ANSI_NULLS ON
go
SET QUOTED_IDENTIFIER ON
go
SET ANSI_PADDING ON
go
IF NOT EXISTS (
SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[SequenceControls]')
AND type in (N'U')
)
BEGIN
CREATE TABLE [dbo].[SequenceControls](
[SequenceCtrlID] [int] IDENTITY(1,1) NOT NULL,
[TableName] [varchar](30) NOT NULL,
[Category] [varchar](30) NULL,
[NextAvailable] [int] NOT NULL,
CONSTRAINT [PK_SequenceControls] PRIMARY KEY CLUSTERED
(
[SequenceCtrlID] ASC
) ON [PRIMARY]
END
go
SET ANSI_PADDING OFF
go
IF NOT EXISTS ( SELECT *
FROM ::fn_listextendedproperty( N'MS_Description' ,
N'SCHEMA',
N'dbo',
N'TABLE',
N'SequenceControls',
N'COLUMN',
N'SequenceCtrlID'
)
)
EXEC sys.sp_addextendedproperty @name=N'MS_Description',
@value=N'Unique ID for each row' ,
@level0type=N'SCHEMA',
@level0name=N'dbo',
@level1type=N'TABLE',
@level1name=N'SequenceControls',
@level2type=N'COLUMN',
@level2name=N'SequenceCtrlID'
go
IF NOT EXISTS ( SELECT *
FROM ::fn_listextendedproperty( N'MS_Description' ,
N'SCHEMA',
N'dbo',
N'TABLE',
N'SequenceControls',
N'COLUMN',
N'TableName'
)
)
EXEC sys.sp_addextendedproperty @name=N'MS_Description',
@value=N'Name of the Table in which the Sequence Number is to be controlled.' ,
@level0type=N'SCHEMA',
@level0name=N'dbo',
@level1type=N'TABLE',
@level1name=N'SequenceControls',
@level2type=N'COLUMN',
@level2name=N'TableName'
go
IF NOT EXISTS ( SELECT *
FROM ::fn_listextendedproperty( N'MS_Description' ,
N'SCHEMA',
N'dbo',
N'TABLE',
N'SequenceControls',
N'COLUMN',
N'Category'
)
)
EXEC sys.sp_addextendedproperty @name=N'MS_Description',
@value=N'Optional category to be used to allow control of multiple sequences within a table' ,
@level0type=N'SCHEMA',
@level0name=N'dbo',
@level1type=N'TABLE',
@level1name=N'SequenceControls',
@level2type=N'COLUMN',
@level2name=N'Category'
go
IF NOT EXISTS ( SELECT *
FROM ::fn_listextendedproperty( N'MS_Description' ,
N'SCHEMA',
N'dbo',
N'TABLE',
N'SequenceControls',
N'COLUMN',
N'NextAvailable'
)
)
EXEC sys.sp_addextendedproperty @name=N'MS_Description',
@value=N'The next available sequence number within a given Table/Category sequence' ,
@level0type=N'SCHEMA',
@level0name=N'dbo',
@level1type=N'TABLE',
@level1name=N'SequenceControls',
@level2type=N'COLUMN',
@level2name=N'NextAvailable'
go
IF NOT EXISTS ( SELECT *
FROM dbo.sysobjects
WHERE id = OBJECT_ID(N'[DF_SequenceControls_NextAvailable]')
AND type = 'D'
)
BEGIN
ALTER TABLE [dbo].[SequenceControls]
ADD CONSTRAINT [DF_SequenceControls_NextAvailable]
DEFAULT ((1))
FOR [NextAvailable]
END
go

and here is the Stored procedure to get the next available number in the sequence:

-- Author: Ralph D. Wilson II
-- Create date: 2012-05-22
-- Description: dbo.usp_TWG_Fetch_Next_Availble_Number
-- This procedure will accept a TableName
-- and a Category and return the lowest
-- NextAvailable value for that sequence.
--
-- If the sequence does not exist within
-- the SequenceControl table, it will initialize
-- the sequence. If there is only one row available
-- for that sequence, it will increment the
-- NextAvailable value and insert another row
-- with the new NextAvailable value.
--
-- MODIFICATION HISTORY:
-- Date Author Comment
-- ---------- ------ -------------------------
-- 2012-05-22 RDWII Initially Coded
-- =============================================
ALTER PROCEDURE dbo.usp_TWG_Fetch_Next_Availble_Number
@TableName Varchar(30) = ''
,@Category VarChar(30) = 'NONE'
,@NextAvailable INT OUTPUT
AS
BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON;

DECLARE @AvailableNumbers INT;

SELECT @AvailableNumbers = COUNT(1) FROM SequenceControls WHERE TableName = @TableName AND Category = @Category; IF (@AvailableNumbers = 0) BEGIN -- Since there are no rows whatsoever for this sequence -- we need to initialize the sequence, returning the -- first sequence number, and insert a second -- sequence number INSERT INTO SequenceControls ( TableName ,Category ,InUse ) Values ( @TableName ,@Category ,'Y' ); INSERT INTO SequenceControls ( TableName ,Category ,NextAvailable ) Values ( @TableName ,@Category ,2 );

SET @NextAvailable = 1; END ELSE BEGIN -- Since there ARE rows whatsoever for this sequence -- we need to try to get the NextAvailable sequence -- number

SELECT @AvailableNumbers = COUNT(1) FROM SequenceControls WHERE TableName = @TableName AND Category = @Category AND InUse = 'N'; SELECT @NextAvailable = NextAvailable FROM ( SELECT TOP 1 NextAvailable FROM SequenceControls WHERE TableName = @TableName AND Category = @Category AND InUse = 'N' ) Z ORDER BY NextAvailable; IF @AvailableNumbers = 0 BEGIN -- Oops, all of the sequence numbers are marked as InUse. -- That means that we need return a sequence number one -- greater than the highest one in the sequence and then -- insert the next one after that. SELECT @NextAvailable = MAX(NextAvailable) + 1 FROM SequenceControls WHERE TableName = @TableName AND Category = @Category;

PRINT 'Inserting a new available row for: ' + @TableName + '/' + @Category;

INSERT INTO SequenceControls ( TableName ,Category ,NextAvailable ) Values ( @TableName ,@Category ,@NextAvailable + 1 );

INSERT INTO SequenceControls ( TableName ,Category ,NextAvailable ,InUse ) Values ( @TableName ,@Category ,@NextAvailable ,'Y' );

END ELSE BEGIN -- Okay, we got one. Now, we need to see if there was only one available.

IF @AvailableNumbers = 1 BEGIN -- Yup, only one was available, so we need to flag that one as InUse -- and insert another row with the next higher NextAvailable value. UPDATE SequenceControls SET InUse = 'Y' WHERE TableName = @TableName AND Category = @Category AND NextAvailable = @NextAvailable; INSERT INTO SequenceControls ( TableName ,Category ,NextAvailable ) SELECT TableName ,Category ,MAX(NextAvailable) + 1 AS NextAvailable FROM SequenceControls WHERE TableName = @TableName AND Category = @Category AND NextAvailable = @NextAvailable GROUP BY TableName ,Category; END BEGIN -- Nope, more than one was available, so we just need to flag -- that one as InUse. UPDATE SequenceControls SET InUse = 'Y' WHERE TableName = @TableName AND Category = @Category AND NextAvailable = @NextAvailable; END END END END Here is the Stored procedure to release a previosuly selected (but not used number)in the sequence.-- Author: Ralph D. Wilson II-- Create date: 2012-05-22-- Description: dbo.usp_TWG_Release_Sequence_Number-- This procedure releases a sequence -- number that was previously marked -- as InUse.---- MODIFICATION HISTORY:-- Date Author Comment-- ---------- ------ --------------------------- 2012-05-22 Initials Initially Coded-- =============================================ALTER PROCEDURE dbo.usp_TWG_Release_Sequence_Number @TableName Varchar(30) = '' ,@Category VarChar(30) = 'NONE' ,@SequenceNumberToRelease INTASBEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; UPDATE SequenceControls SET InUse = 'N' WHERE TableName = @TableName AND Category = @Category AND NextAvailable = @SequenceNumberToRelease; END Author: Ralph D. Wilson II-- Create date: 2012-05-22-- Description: dbo.usp_TWG_Consume_Sequence_Number-- This procedure consumes a sequence -- number that was previously marked -- as InUse.---- MODIFICATION HISTORY:-- Date Author Comment-- ---------- ------ --------------------------- 2012-05-22 Initials Initially Coded-- =============================================ALTER PROCEDURE dbo.usp_TWG_Consume_Sequence_Number @TableName Varchar(30) = '' ,@Category VarChar(30) = 'NONE' ,@SequenceNumberToRelease INTASBEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; DELETE FROM SequenceControls WHERE TableName = @TableName AND Category = @Category AND NextAvailable = @SequenceNumberToRelease AND InUse = 'Y'; END Author: Ralph D. Wilson II-- Create date: 2012-05-22-- Description: dbo.usp_TWG_Consume_Sequence_Number-- This procedure consumes a sequence -- number that was previously marked -- as InUse.---- MODIFICATION HISTORY:-- Date Author Comment-- ---------- ------ --------------------------- 2012-05-22 Initials Initially Coded-- =============================================ALTER PROCEDURE dbo.usp_TWG_Consume_Sequence_Number @TableName Varchar(30) = '' ,@Category VarChar(30) = 'NONE' ,@SequenceNumberToRelease INTASBEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; DELETE FROM SequenceControls WHERE TableName = @TableName AND Category = @Category AND NextAvailable = @SequenceNumberToRelease AND InUse = 'Y'; ENDStored Procedure to remove (consume) a previously selected sequence number (Note: if an unselected number is specified, nothing happens): Author: Ralph D. Wilson II-- Create date: 2012-05-22-- Description: dbo.usp_TWG_Consume_Sequence_Number-- This procedure consumes a sequence -- number that was previously marked -- as InUse.---- MODIFICATION HISTORY:-- Date Author Comment-- ---------- ------ --------------------------- 2012-05-22 Initials Initially Coded-- =============================================ALTER PROCEDURE dbo.usp_TWG_Consume_Sequence_Number @TableName Varchar(30) = '' ,@Category VarChar(30) = 'NONE' ,@SequenceNumberToRelease INTASBEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; DELETE FROM SequenceControls WHERE TableName = @TableName AND Category = @Category AND NextAvailable = @SequenceNumberToRelease AND InUse = 'Y'; END


This is all! i would be very very very greatful if someone could help! thank you again!!!

 

[VilaRestal]

In SQL you can set identity fields to increment by any number.

Why you would need one to increment in hundreds I can't fathom.

The primary key for each table (and therefore the foreign key in the items table - Item Type) should just be a simple number field. It would be far more efficient.

Your display text could be calculated. And if that calculation means too much processing to do each time the view or sp is opened then do it with a trigger storing the calculated value as records are added.

But for pity's sake don't make it your primary key.

 

[VilaRestal]

In fact, I would go further and say your table design:

Item ID- Item Type- Description- Make & Model- Serial #- Unit- Condition- Location or Personal Use- Department- Cost or Value in HTG/ USD- Currency- Total Cost or Value- Purchase #- Date received- Donor- Loss/ Theft- Comments

Looks not very well normalized and without a good naming convention.

I see a 'create SequenceControls' in the SQL but nothing for creating this or any other tables.

 

[gardo]

Thank you very much for your insights VilaRestal

The increments are not in hundreds. There are two parts in the Item ID field: the hundreds numbers that are just merely identifying the Item Types, and the increments that are counting the amount of materials entered into the system for that corresponding Item Type. So if in the drop down box located on the Item Type field you select Office Equipment for the third time in the system, the corresponding Item ID will be 100-3 (since "100" is for Office Equipment, and "3" is for the third item in that category. I want to have it that way for every Item Type.
That process will help us put label stickers on our materials donated and purchased without mistakes And it will also be quicker as the sytem is already automated instead of having to figure it out for every item type. I know it may be sampler to purchase a bar code system that may do that kind of work, however we don't have the budget for it.

As far as only using the Item Type at a number field, that would make it difficult for other parties entering information in that same system (i am the only one doing the data entry) in terms of remembering, knowing or even constantly avoiding making mistake in putting the right number for the corresponding category.
For the table design, we are not really focusing on being conventional but more on the information that we need to retain about each product for internal control

If they can be done in SQL instead, that would be great also, but I am not knowledgeable in SQL either as VBA is the first computer programming language I am trying to use and learn.

So basically, for the VBA that were suggested to me below, i just dont know how to use it to paste the information for my table to make it work. So I would really appreciate some HELP on that matter. Please!
It seems like what he described is exactly what I am looking for, but cant go further with these codes. your help would again be appreciated.

Thank you!!

 

[Pat Hartman]

Combining two attributes in the same field - type and sequence number - will lead to problems sooner or later. Use two separate fields. As vila has already said, use an autonumber as the primary key for the table and use that in all relationships. Create a unique index on type and seqNum so you can ensure that no duplicates are entered.

The user will choose the type from a combo and in the BeforeInsert event of the FORM can generate the next available sequence number:
Me.txtSeqNum = Nz(DMax("SeqNum", "yourtable", "ItemType = '" & Me.cboItemType & "'"),0) + 1

Keep in mind that this could generate duplicates if multiple people are doing data entry at the same time, so you need to create a code loop that generates numbers until one is accepted or at a minimum, reject the entry and tell the user to just press save again.

 

[VilaRestal]

The thing is, there can be (and often should be) a difference between the 'identifiers' users see (the business key) and the identifier the database uses.

Using numbers as keys is far better than using text. SQL can filter and find a number much more quickly than text.

I'm not going to translate all that SQL into VBA. I'm not even going to read it. It looks horrendously complicated for what sounds like (or sounds like it should be) a simple thing.

I will gladly help you along the lines of:

tllItemTypes
TypeID (int, primary key)
strName (varchar)

qryItemTypes
SELECT *, 100*TypeID As [Display ID] FROM tllItemTypes

tblItems
ItemID (int, identity, primary key)
BusinessID (int, set by a trigger on insert)
TypeID (int, foreign key, not null)
...
other fields
...

qryItems
SELECT *, CAST(100*TypeID AS varchar) + '-' + CAST(BusinessID AS varchar) AS [Display ID] FROM tblItems

And then all you need is the trigger, which shouldn't be too complicated (certainly not as complicated as all that stuff above).

Edit: You can do it at the front end as Pat says. If it was me I would do it with a trigger. It would be more reliable.

 

[RainLover]

Vila

Using numbers as keys is far better than using text. SQL can filter and find a number much more quickly than text.

If you were to add an index then the speed would not be noticable.

 

[RainLover]

Gardo

I am currently working on an access inventory system that will track all of our items and their categories (IT materials, office equipment, Office Supplies, communication materials, etc.) by generating automated sequential numbers as new records are updated into the system.
For instance: the “Item ID” attributed to “Office Equipment” is 100, the “Item ID” attributed to “IT materials” is 300. The first chair that got purchased or donated to our organization will be automatically labeled 100-1 in the “Item ID” field when I select Office Equipment in the drop down combo box on the “Item Type” field. A month later we purchased a new couch as part of our furniture stock and as we enter it in the record and select “Office Equipment” in the Drop down combo box of the “Item Type” field, the “Item ID” auto-increments automatically and becomes 100-2 although there are other records (following the same process in between).

I understand your system of identifying items. I would suggest that instead of using two groups of numbers like 100 01, 100 02, 100 03 etc it would be better to use something more meaningfull. Like Furn 01, Furn 02 etc for Furniture. This would be better from a user's point of view.

Otherwise an Autonumber would be just as good if not better than your proposed convention. I will attach a file in 2003 that creates a numbering system that can't be broken by multiple users. That is they cannot create duplicate values.

It is not simple and requires some degree of VBA.

 

[VilaRestal]

Vila

Using numbers as keys is far better than using text. SQL can filter and find a number much more quickly than text.

If you were to add an index then the speed would not be noticable.

True, but the storage space would be improved and index fragmentation wouldn't be an issue, which could gradually wear down the performance of the indexed text field (with 'numbers' getting reassigned).

Most importantly: calculating a new number to assign will be much quicker and easier if the numbers are stored as numbers.

 

[RainLover]

Vila

Please excuse my ignorance but what do you mean by "Index Fragmentation".

I shan't argue with theother stuff. My post was more of a FYI thing.

 

[VilaRestal]

Probably not a major consideration here (and I'm not certain SQL Server is actually the back end that's being used - although I assume it is from all the T-SQL code posted).

To quote from http://www.sqlservercentral.com/articles/Stairway+Series/72443/, which is a very good article on it:

"external [index] fragmentation refers to the lack of correlation between the logical sequence of an index and its physical sequence."

Such fragmentation is, as I understand it (and I could be wrong - I'm not an expert), virtually impossible in an identity number field until random deletions happen and then not serious. In a text field such as that suggested it certainly isn't and performance would gradually deteriorate without defragmentation - something that most DBAs never bother to do.

But, as I say, that's not the main reason to do this as numbers not text. The calculation to find available numbers will be much quicker if the numbers are stored as numbers.

 

[RainLover]

I would have to research the matter but I think that if a record is deleted then the table is reindexed.

This is why saving is slower in indexeed fields. But then faster in retrival.

I think I am right but not 100% certain.