Creating a single flat record from a one to many query revisited

[Tim Jones]

Hope everyone is well, I have been trying to create a flat file that can be
saved as a fixed length file. This file is required to transmet claims over
an edi connection.
Challenges
Take one table and loop through it for each claim nymber
with each claim number find records in the parts table
loop through the parts table and append or update three fields on the end of
the claim table.

the loop would look something like this

dim recordset one
dim recordset two

do until eof recordset one
var =recordset one!claim number
do until eof recordset two
if recordset two!claim number = recrodset
one!claim number
recordsetone!part1 =
recordsettwo!part
recordsetone!part1price =
recordsettwo!price
elae
recordsettwo,movenext
loop
recordsetone.movenext
loop
end

Here is the problem. For some reason I cannot get the variables to match
between the two recordsets. I have tried several approaches, find first
makeing recordset two based only on the criteria of recordsetone!claim
and I just keep falling short.
Does anyone know of a good example or article on this so I could research it

 

[Alex Dybenko]

Hi,
I think you have to add:
recordset two.movefirst

before:
do until eof recordset two

Also - it looks like you can do the same ting very easy with update query
(if recordset 2 source SQL is updatable)

Alex