How can I adjust numbering?

[Guest]

I have three groups, 4A, 4B and 4E. In my form I would like to be able to
number the entries 4A-1, 4A-2, etc and 4B-1, 4B-2 etc for each of the three
groups. The data is being pulled from two tables, one called Journal Entries
(where the numbering would need to reside) and the other Journal Entry
Details where the details for each form resides.

any suggestions appreciated. Thanks.

 

[Guest]

How are you to determine which 4A is ti be 4A-1 and which is 4A-2?
What is the field name(s)?
Do you want a permament change?

 

[Guest]

Assumption:
1 - Table JournalEntry - primary key is text, contains things like 4A, 5A,
5B, etc.
2 - Table JournalEntryDetail - primary key has two parts:
part 1 - link_JournalEntry - text to match key of table JournalEntry
part 2 - SequenceNumber - numeric - integer

Create a MODULE with the following VBA code:
Option Compare Database
Option Explicit

Public Function GetNextSequenceNumber(str_JE As String) As Integer
Dim int_Next As Integer

int_Next = Nz(DMax("SequenceNumber", _
"JournalEntryDetail", _
"link_JournalEntry = '" & str_JE & "'"), 0) + 1
GetNextSequenceNumber = int_Next
End Function


-------
When you want to create a JounralEntry detail, call the function with the
JournalEntry value as the argument. It will return the next sequence number.

If you must display the JournalEntryDetail's key as a single field in a
report then do it like this:

txt_JournalEntryDetail_ID = Trim([link_JournalEntry]) & "-" &
Trim(Str([SequenceNumber]))

 

[Guest]

The designation of 4A, 4B, etc is the assigned designation for the division
within the company (field name is simply "BU". The # itself just needs to be
sequential based on when the entry is made. This definitely needs to be a
permanent change, this portion of the database will be around for a long time.

There is a Primary key (auto number) set for the Journal Entry table which
is obviously linked to the detail table to keep it all together.

 

[Guest]

The # itself just needs to be sequential based on when the entry is made.
I assume you have something that determines which entry was first like a
datetime field OR Primary key (auto number).
Add a field for the sequential number - later you can combine in an update
query with the other field (4A, 4B, etc). Use a ranking query to generate the
sequential numbers.
Example ---
SELECT Q.Division, Q.EntryDate, (SELECT COUNT(*) FROM Product Q1
WHERE Q1.[Division] = Q.[Division]
AND Q1.EntryDate< Q.EntryDate)+1 AS Rank
FROM Product AS Q
ORDER BY Q.Division, Q.EntryDate;