loop through recordset and append data to a table

  • Thread starter Thread starter Julie
  • Start date Start date
J

Julie

Hi,

I need to loop through a table, grab the first record
where field 1 and field2 are the same, write that record
to another table and skip the rest in the "grouping" and
moved to the next "grouping".

Any help would be greatly appreciated!

Thanks
 
Hi,

I need to loop through a table, grab the first record
where field 1 and field2 are the same, write that record
to another table and skip the rest in the "grouping" and
moved to the next "grouping".

Any help would be greatly appreciated!
Click to expand...

You're in trouble.

A Table HAS NO ORDER. It's a "bag" full of records. There is no first
record, there is no next record, and there is no grouping - unless you
have some field or fields within the table which define an order; if
you do, you can use a Query to extract the data that you want.

I don't understand the "grouping" concept here, but if you just want
to select those records where field1 and field2 are the same, create a
Query based on your table. On the Criteria line under Field1 type

=[Field2]

Open the query and you'll see those records which meet that criterion.

Writing the record to another table would be to store data
redundantly, generally a bad idea. Note that you can base a Form, a
Report, an export, or another query on the suggested Query - it's not
necessary to create a new table for any of those purposes.
 
So, I gather from your response that you are unwilling to
help. Is there anyone else out there that can help with
my problem, which is not well understood by the first
respondee?

I need code, that allows me to work with each record - or
skip a group of records. Then, like I stated below,
write one of these records out to a new recordset.

Is anyone interested in helping rather than preaching?


-----Original Message-----
Hi,

I need to loop through a table, grab the first record
where field 1 and field2 are the same, write that record
to another table and skip the rest in the "grouping" and
moved to the next "grouping".

Any help would be greatly appreciated!
Click to expand...

You're in trouble.

A Table HAS NO ORDER. It's a "bag" full of records. There is no first
record, there is no next record, and there is no grouping - unless you
have some field or fields within the table which define an order; if
you do, you can use a Query to extract the data that you want.

I don't understand the "grouping" concept here, but if you just want
to select those records where field1 and field2 are the same, create a
Query based on your table. On the Criteria line under Field1 type

=[Field2]

Open the query and you'll see those records which meet that criterion.

Writing the record to another table would be to store data
redundantly, generally a bad idea. Note that you can base a Form, a
Report, an export, or another query on the suggested Query - it's not
necessary to create a new table for any of those purposes.


.
Click to expand...
 
Hi Julie!!

The code below is nothing very complex. but I think it
will do the trick!

Step 1: for this code to work you need to identify a
recordSet, which in turn, cycles through the desired data.

Ex:1
dim rsnew as recordSet
dim dbs as database

set dbs=currentdb
set rsnew=dbs.openRecordSet("SELECT * from Product")


Step 2: Use this recordSet with the 'With' statement.
This is the fun part, Once you use the 'With' you now have
access to each record and one line at the time. You use a
loop to cycle through everything.

Ex:2

with rsnew
..moveFirst
While not .EOF
.fields(0) ' being the first field in your
' recordSet.
.field("Price").Value ' Or this(any order).

if .field(1).value = .field(2).value then
' store result

end if
.moveNext
Wend

End With

Now, inside the 'While..Wend' loop you can put your code
for locating the field1 = field2. The fields can be access
by two different ways. See above.

Remember: when you use this code, any code placed inside
the loop will be applied to the line its curently pointing
to. (I hope this is what you mean by Next Grouping)
The moveNext is the key to cylcing through everything.
So to compare field1 = field2,it can only return one
result per line that its cycles through.

Now for storing the data, I'm not sure exactly how to
proceed. I'm not a Great programmer yet, I would need to
think about-it more. Put if I understood correctly, this
might just be the only thing left to do, sorry for that
part, hope this helps....
-----Original Message-----
So, I gather from your response that you are unwilling to
help. Is there anyone else out there that can help with
my problem, which is not well understood by the first
respondee?

I need code, that allows me to work with each record - or
skip a group of records. Then, like I stated below,
write one of these records out to a new recordset.

Is anyone interested in helping rather than preaching?


-----Original Message-----
Hi,

I need to loop through a table, grab the first record
where field 1 and field2 are the same, write that record
to another table and skip the rest in the "grouping" and
moved to the next "grouping".

Any help would be greatly appreciated!
Click to expand...

You're in trouble.

A Table HAS NO ORDER. It's a "bag" full of records. There is no first
record, there is no next record, and there is no grouping - unless you
have some field or fields within the table which define an order; if
you do, you can use a Query to extract the data that you want.

I don't understand the "grouping" concept here, but if you just want
to select those records where field1 and field2 are the same, create a
Query based on your table. On the Criteria line under Field1 type

=[Field2]

Open the query and you'll see those records which meet that criterion.

Writing the record to another table would be to store data
redundantly, generally a bad idea. Note that you can base a Form, a
Report, an export, or another query on the suggested Query - it's not
necessary to create a new table for any of those purposes.


.
Click to expand...
.
Click to expand...
 
Thanks for this post. I was looking for something like
this the other day. Thanks to Julie also for her
persistence.

Mark
-----Original Message-----
Hi Julie!!

The code below is nothing very complex. but I think it
will do the trick!

Step 1: for this code to work you need to identify a
recordSet, which in turn, cycles through the desired data.

Ex:1
dim rsnew as recordSet
dim dbs as database

set dbs=currentdb
set rsnew=dbs.openRecordSet("SELECT * from Product")


Step 2: Use this recordSet with the 'With' statement.
This is the fun part, Once you use the 'With' you now have
access to each record and one line at the time. You use a
loop to cycle through everything.

Ex:2

with rsnew
..moveFirst
While not .EOF
.fields(0) ' being the first field in your
' recordSet.
.field("Price").Value ' Or this(any order).

if .field(1).value = .field(2).value then
' store result

end if
.moveNext
Wend

End With

Now, inside the 'While..Wend' loop you can put your code
for locating the field1 = field2. The fields can be access
by two different ways. See above.

Remember: when you use this code, any code placed inside
the loop will be applied to the line its curently pointing
to. (I hope this is what you mean by Next Grouping)
The moveNext is the key to cylcing through everything.
So to compare field1 = field2,it can only return one
result per line that its cycles through.

Now for storing the data, I'm not sure exactly how to
proceed. I'm not a Great programmer yet, I would need to
think about-it more. Put if I understood correctly, this
might just be the only thing left to do, sorry for that
part, hope this helps....
-----Original Message-----
So, I gather from your response that you are unwilling to
help. Is there anyone else out there that can help with
my problem, which is not well understood by the first
respondee?

I need code, that allows me to work with each record - or
skip a group of records. Then, like I stated below,
write one of these records out to a new recordset.

Is anyone interested in helping rather than preaching?


-----Original Message-----
Click to expand...
<juliea@eccs-
seattle.com>
wrote:

Hi,

I need to loop through a table, grab the first record
where field 1 and field2 are the same, write that record
to another table and skip the rest in the "grouping" and
moved to the next "grouping".

Any help would be greatly appreciated!

You're in trouble.

A Table HAS NO ORDER. It's a "bag" full of records. There is no first
record, there is no next record, and there is no grouping - unless you
have some field or fields within the table which
Click to expand...
define
an order; if
you do, you can use a Query to extract the data that
Click to expand...
you
want.
I don't understand the "grouping" concept here, but if you just want
to select those records where field1 and field2 are
Click to expand...
the
same, create a
Query based on your table. On the Criteria line under Field1 type

=[Field2]

Open the query and you'll see those records which meet that criterion.

Writing the record to another table would be to store data
redundantly, generally a bad idea. Note that you can base a Form, a
Report, an export, or another query on the suggested Query - it's not
necessary to create a new table for any of those purposes.


.
Click to expand...
.
Click to expand...
.
Click to expand...
 
So, I gather from your response that you are unwilling to
help. Is there anyone else out there that can help with
my problem, which is not well understood by the first
respondee?
Click to expand...

I apologize for the tone of my advice, but not for the content.

A table STILL has no order. This is not "preaching" - this is an
incontrovertible fact of the way that Access stores data. New records
will be added to a table in whatever order the database engine finds
convenient; there is no guarantee that records *in a table* will be
stored sequentially or in any other particular order.
I need code, that allows me to work with each record - or
skip a group of records. Then, like I stated below,
write one of these records out to a new recordset.
Click to expand...

Bearing the above in mind, and assuming that you have a Recordset
based upon a query with an Order By expression - or that you're
willing to gamble that the records in the table have a meaningful
order, which they will nine times out of ten - you could use code like
the following:

Dim db As DAO.Database
Dim rsIn As DAO.Recordset
Dim rsOut As DAO.Recordset
Set db = CurrentDb
Set rsIn = db.OpenRecordset("tablename", dbOpenDynaset) ' or queryname
Set rsOut = db.OpenRecordset("outputtable", dbOpenDynaset)
rsIn.MoveFirst
Do Until rsIn.EOF
If rsIn!Field1 = rsIn!Field2 Then
rsOut.AddNew
rsOut!Field1 = rsIn.Field1
rsOut!thisfield = rsIn.thisfield
rsOut!thatfield = rsOut.thatfield
<etc>
rsOut.Update
End If
rsIn.MoveNext
Loop
rsOut.Close
rsIn.Close
Set rsIn = Nothing
Set rsOut = Nothing
 
Back
Top