If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How can I adjust numbering?
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. -- Lori A. Pong |
#2
|
|||
|
|||
How can I adjust numbering?
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? -- KARL DEWEY Build a little - Test a little "Lori" wrote: 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. -- Lori A. Pong |
#3
|
|||
|
|||
How can I adjust numbering?
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])) "Lori" wrote: 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. -- Lori A. Pong |
#4
|
|||
|
|||
How can I adjust numbering?
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. -- Lori A. Pong "KARL DEWEY" wrote: 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? -- KARL DEWEY Build a little - Test a little "Lori" wrote: 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. -- Lori A. Pong |
#5
|
|||
|
|||
How can I adjust numbering?
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; -- KARL DEWEY Build a little - Test a little "Lori" wrote: 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. -- Lori A. Pong "KARL DEWEY" wrote: 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? -- KARL DEWEY Build a little - Test a little "Lori" wrote: 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. -- Lori A. Pong |
Thread Tools | |
Display Modes | |
|
|