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
|
|||
|
|||
Consecutive numbers
Greetings
This should be easy, but I am stuck! I have a table that I import from another program. There are 25 fields, but I only need to use 3 of them for what I need to do. After I import the data, using ODBC, the primary key is multi-field; fieldA, fieldB, fieldC. fieldA is a "Job Number", in text format. fieldB is a number field with a "width", fieldC is a number field with a "length". csv example; "43758A", "24.75", "32.25" There are anywhere from 1, to 50 records having a unique "Job Number", ("fieldA"). I want to create a new table in my database, for use throughout my application. I would use an Append Query, or VBA code, to update the table periodically. The table will contain fieldA, fliedB, fieldC. I will sort on fieldB, ascending. The new table would contain another field; "fieldLineNo". The value for that field needs to be 1 for the first record, then 2, 3, etc until the value of fieldA changes. Then the consecutive numbers start over. end result: "43758A", "12.75", "32.25", "1" "43758A", "13.75", "32.25", "2" "43758A", "14.75", "32.25", "3" "51789A", "9.75", "32.25", "1" "51789A", "10.75", "32.25", "2" "51789A", "11.75", "32.25", "3" Can anyone help me out here? I am sort of new to Access and VBA, but I can usually get stuff working with a little push in the right direction. Thanks, Dennis |
#2
|
|||
|
|||
Consecutive numbers
In the append query, use an expression similar to the following as the
calculated field that appends to the fieldLineNo field (this expression assumes that the "source" field name is the same as the target field name (that is, fieldA): Field4Value: Nz(DMax("fieldLineNo", "TableNameToWhichDataAreBeingAppended", "fieldA='" & [fieldA] & "'"), 0) + 1 -- Ken Snell MS ACCESS MVP "Dennis Ruppert" wrote in message om... Greetings This should be easy, but I am stuck! I have a table that I import from another program. There are 25 fields, but I only need to use 3 of them for what I need to do. After I import the data, using ODBC, the primary key is multi-field; fieldA, fieldB, fieldC. fieldA is a "Job Number", in text format. fieldB is a number field with a "width", fieldC is a number field with a "length". csv example; "43758A", "24.75", "32.25" There are anywhere from 1, to 50 records having a unique "Job Number", ("fieldA"). I want to create a new table in my database, for use throughout my application. I would use an Append Query, or VBA code, to update the table periodically. The table will contain fieldA, fliedB, fieldC. I will sort on fieldB, ascending. The new table would contain another field; "fieldLineNo". The value for that field needs to be 1 for the first record, then 2, 3, etc until the value of fieldA changes. Then the consecutive numbers start over. end result: "43758A", "12.75", "32.25", "1" "43758A", "13.75", "32.25", "2" "43758A", "14.75", "32.25", "3" "51789A", "9.75", "32.25", "1" "51789A", "10.75", "32.25", "2" "51789A", "11.75", "32.25", "3" Can anyone help me out here? I am sort of new to Access and VBA, but I can usually get stuff working with a little push in the right direction. Thanks, Dennis |
Thread Tools | |
Display Modes | |
|
|