Logical Item Numbers

[Mackster]

I have an Access 2003 database for tracking evidence. The table includes
fields for case numbers, description of the evidence, and item numbers (plus
others that aren't relevant for this question). Under each case number, I
may have several entries for each item. I need to find a way to
automatically number each item so that under case number H5634 I have item 1,
item 2..etc and then for case number G5423 I also have item 1, item 2..etc.

This database was converted last week from DBase III and has over 60,000
records.

 

[John Vinson]

I have an Access 2003 database for tracking evidence. The table includes
fields for case numbers, description of the evidence, and item numbers (plus
others that aren't relevant for this question). Under each case number, I
may have several entries for each item. I need to find a way to
automatically number each item so that under case number H5634 I have item 1,
item 2..etc and then for case number G5423 I also have item 1, item 2..etc.

This database was converted last week from DBase III and has over 60,000
records.

You can do this using a Form to enter the data. I'd imagine a Case
form with an Items subform. The items table would have fields
[CaseNumber] - used as the master/child link field of the subform -
and ItemNo, perhaps as a joint two-field Primary Key.

In the Subform's BeforeInsert event you could have code like:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!ItemNo = NZ(DMax("[ItemNo]", "[Items]", "[CaseNumber] = '" _
& Me!CaseNumber & "'")) + 1
End Sub

This will look up the maximum existing ItemNo for the currently
selected CaseNumber; NZ() will return 0 if there aren't any items yet.
It will then add one to that value and store it into the ItemNo
control on the form.

John W. Vinson[MVP]

 

[Mackster]

I think I understand the concept, but if I understand correctly, I would have
to create a new table to track item numbers...Is there a way to do it without
creating a new table? I tried using a subform based on the master table
using the fields and code you mentioned, but it's not finding the previous
item numbers and starts everything as item 1.
--
I'm not young enough to know everything.

I have an Access 2003 database for tracking evidence. The table includes
fields for case numbers, description of the evidence, and item numbers (plus
others that aren't relevant for this question). Under each case number, I
may have several entries for each item. I need to find a way to
automatically number each item so that under case number H5634 I have item 1,
item 2..etc and then for case number G5423 I also have item 1, item 2..etc.

This database was converted last week from DBase III and has over 60,000
records.

You can do this using a Form to enter the data. I'd imagine a Case
form with an Items subform. The items table would have fields
[CaseNumber] - used as the master/child link field of the subform -
and ItemNo, perhaps as a joint two-field Primary Key.

In the Subform's BeforeInsert event you could have code like:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!ItemNo = NZ(DMax("[ItemNo]", "[Items]", "[CaseNumber] = '" _
& Me!CaseNumber & "'")) + 1
End Sub

This will look up the maximum existing ItemNo for the currently
selected CaseNumber; NZ() will return 0 if there aren't any items yet.
It will then add one to that value and store it into the ItemNo
control on the form.

John W. Vinson[MVP]

 

[John Vinson]

I think I understand the concept, but if I understand correctly, I would have
to create a new table to track item numbers...Is there a way to do it without
creating a new table? I tried using a subform based on the master table
using the fields and code you mentioned, but it's not finding the previous
item numbers and starts everything as item 1.

Well, if you have a one to many relationship from the master table to
the items table, you shouldn't need a third table; just an ItemNo
field in the Items table.

If you DON'T have an items table... how can you store information
about items!?

Please explain the structure of your tables, their relationships, and
perhaps post the actual code you're using.

John W. Vinson[MVP]

 

[Mackster]

Out evidence database was created in 1987 using DBase III. It created four
..dbf files: 'EVMASTER', 'EVDETAIL', 'OFFICER', and 'EVTYPE'. I pulled all
four .dbf files into Access 2003. 'EVMASTER' has the following fields:
'CaseNumber', 'evidence', 'year', 'item', 'description', 'badge', 'type',
etc. 'EVDETAIL' has: 'evidence', 'year', 'item', 'datein', 'dateout',
'detail'. 'OFFICER' has: 'badge', 'name'. 'EVTYPE' has: 'type',
'description'. It's a real mess and has a lot of repeated information, but
with over 60,000 records, I'd like to work with the existing tables unless
there's an easy way to reorganize the structure.

Using the existing tables, I created the following relationships:
'EVMASTER, badge' is linked to 'OFFICER, badge', 'EVMASTER,
evidence-year-item' is linked to 'EVDETAIL, evidence-year-item'. 'EVMASTER,
type' is linked to 'EVTYPE, type'.

I created a form based on the 'EVMASTER' table with a subform from
'EVDETAIL'. One field on the form lists information from 'OFFICER, name',
and another lists information from 'EVTYPE, description'.

The EVMASTER table has records of each entry for evidence. The EVDETAIL
table shows when and where the evidence was signed out and returned. The
OFFICER table records officers' badge numbers and names. The EVTYPE table
records and evidence type code and description of that type.

The biggest problem is when we enter a new item of evidence, if we
accidentally duplicate item numbers, then the EVDETAIL record shows up for
the wrong evidence since it is based on a relationship which includes the
item number. If I could use the DMax function to automatically calculate the
new item number, it would solve a lot of my human error problems.

You've already given me a step in the right direction and, for the time
being, I have including a subform which lists all of the evidence under the
case number being added. Thank you.