Consecutive numbers

  • Thread starter Thread starter Dennis Ruppert
  • Start date Start date
D

Dennis Ruppert

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
 
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
 
Back
Top