Greetings
Seems like this should be easy, but I am stuck!
I import data in a table from another program. I link to the table using ODBC, and use an Append Query with 6 of the fields. The primary key in my new table is multi-field; fieldA, fieldB, fieldC.
fieldA is a "Job Number", in text format. fieldB and fieldC are number fields. fieldD, fieldE, and fieldF are "width", "length", and "date".
Source example;
"43831C", "123", 10, "24.75", "32.25", 05/15/04
"43831C", "175", 18, "28.00", "18.75", 05/18/04
"43914B", "241", 12, "16.25", "32.50", 05/15/04
"43914B", "167", 18, "13.25", "32.50", 05/17/04
"43914B", "058", 12, "19.25", "32.50", 05/15/04
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 at startup. I will sort on fieldD, ascending.
The new table would contain all 6 fields, plus another field; "fieldLineNo". The value for that field needs to be 1 for the first record based on my sort, then 2, 3, etc until the value of fieldA changes. Then the consecutive numbers start over. The last field is my "fieldLineNo"
Desired result:
"43914B", "167", 18, "13.25", "32.50", 05/17/04, "1"
"43914B", "241", 12, "16.25", "32.50", 05/15/04, "2"
"43914B", "058", 12, "19.25", "32.50", 05/15/04, "3"
"43831C", "123", 10, "24.75", "32.25", 05/15/04, "1"
"43831C", "175", 18, "28.00", "18.75", 05/18/04, "2"
I searched all thoroughly amongst the posts, but could not find what I needed, (or understood)! Another forum pointed me towards using a query expression, (Nz(DMax("fieldLineNo","tblResult","fieldA='" & [fieldA] & "'"),0)+1) But I can't see how that would work. I assume I need to use some VBA, possibly with a loop?
Dim db As Database
Dim rs As Recordset
Dim lngLoop As Long
Set db = DBEngine(0)(0)
Set rs = db.OpenRecordset("tblMySource")
lngLoop = 1
Do
rs.Edit
rs!ListLineNum = lngLoop
rs.Update
lngLoop = lngLoop + 1
rs.MoveNext
Loop Until rs.EOF
rs.Close
Set rs = Nothing
Set db = Nothing
Set MyRS = Nothing
Set conn = Nothing
Obviously will sequentially number ALL the records, can I
modify that to reset at a new "fieldA" value?
An Access report handles this quite easily, but I need it in a table.
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
Seems like this should be easy, but I am stuck!
I import data in a table from another program. I link to the table using ODBC, and use an Append Query with 6 of the fields. The primary key in my new table is multi-field; fieldA, fieldB, fieldC.
fieldA is a "Job Number", in text format. fieldB and fieldC are number fields. fieldD, fieldE, and fieldF are "width", "length", and "date".
Source example;
"43831C", "123", 10, "24.75", "32.25", 05/15/04
"43831C", "175", 18, "28.00", "18.75", 05/18/04
"43914B", "241", 12, "16.25", "32.50", 05/15/04
"43914B", "167", 18, "13.25", "32.50", 05/17/04
"43914B", "058", 12, "19.25", "32.50", 05/15/04
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 at startup. I will sort on fieldD, ascending.
The new table would contain all 6 fields, plus another field; "fieldLineNo". The value for that field needs to be 1 for the first record based on my sort, then 2, 3, etc until the value of fieldA changes. Then the consecutive numbers start over. The last field is my "fieldLineNo"
Desired result:
"43914B", "167", 18, "13.25", "32.50", 05/17/04, "1"
"43914B", "241", 12, "16.25", "32.50", 05/15/04, "2"
"43914B", "058", 12, "19.25", "32.50", 05/15/04, "3"
"43831C", "123", 10, "24.75", "32.25", 05/15/04, "1"
"43831C", "175", 18, "28.00", "18.75", 05/18/04, "2"
I searched all thoroughly amongst the posts, but could not find what I needed, (or understood)! Another forum pointed me towards using a query expression, (Nz(DMax("fieldLineNo","tblResult","fieldA='" & [fieldA] & "'"),0)+1) But I can't see how that would work. I assume I need to use some VBA, possibly with a loop?
Dim db As Database
Dim rs As Recordset
Dim lngLoop As Long
Set db = DBEngine(0)(0)
Set rs = db.OpenRecordset("tblMySource")
lngLoop = 1
Do
rs.Edit
rs!ListLineNum = lngLoop
rs.Update
lngLoop = lngLoop + 1
rs.MoveNext
Loop Until rs.EOF
rs.Close
Set rs = Nothing
Set db = Nothing
Set MyRS = Nothing
Set conn = Nothing
Obviously will sequentially number ALL the records, can I
modify that to reset at a new "fieldA" value?
An Access report handles this quite easily, but I need it in a table.
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