Consecutive numbers

[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

[Ken Snell]

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" <dr******@ruppertweb.com> wrote in message
news:f7**************************@posting.google.c 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

[Dennis Ruppert]

Thanks for the response. I must be doing something wrong, as I get a
result of 1 for every record in the appended table. My test:

tblSource has 4 fields; Job, CutLineNo, SOLineNo, DoorWidth

tblResult has same 4 fields, plus a field named LineNum

Entered as Expression:

LineNum: Nz(DMax("LineNum","tblResult","Job='" & [Job] & "'"),0)+1

I tried this as a select query, and as an append query. I added the
tblSource in the QBE, and included all 4 fields along with the
calculated field.

Where did I go wrong?

Thanks again,

Dennis

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!