Recursive / Append

[orreymalcolm]

Hey everyone!

I am currently working on a database with 3 Tables. 1 is the customer list (657) with all their details, 1 is a contract list (2011) with all those details and the 3rd is a complete customer list with all the current contracts they are committed to (486,974).

What I need to do is find a way to add all the contracts (committed to or not) to the complete list. This seems simple enough, but I can't seem to find a way to do it. So i decided I would do one big split off into 657 tables, run a VBA or SQL script to look through which contracts are already inside the Member's listing and if it's already there, then skip it, if it's not, then append it to a new row. At the end of all this, I need to merge them all back together in one table which at this point should now have 1,321,227 records.

Since I'm horrible at explaining, and some of that might not make sense, let me write my pseudocode:


iCount = 0

DO
IF <ContractTable>.<CurrentRow> = <MemberTable>.<CurrentRow> THEN
END IF
ELSE
APPEND <ContractTable>.<CurrentRow> to <MemberTable>.<NewRow>
END IF
iCount = iCount + 1
WHILE iCount <= 2011


Basically something like that, unless someone has a way for me to do this all in JetSQL. Any help is appreciated!

Thanks in advance.

 

[Remou]

It is nearly always a good idea to post some sample data and a hoped-for result.