Recursive / Append Query

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 complete list and if it's already there, then skip it, if it's not, then append it to a new row.

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

[code]iCount = 0

DO
IF . = . THEN
END IF
ELSE
APPEND . to .
END IF
iCount = iCount + 1
WHILE iCount <= 2011[/code]

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

Thanks in advance.

Comments

  • : 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 complete list and if it's
    : already there, then skip it, if it's not, then append it to a new
    : row.
    :

    you don't want 657 separate tables for each customer, would be a nightmare to maintain. you need an intermediate or linking table that will allow you to store a customer name/id and a committed contract name/id. then by joining all 3 tables you would be able to have a current list [would also need a form with subform(s) that would allow you to select the contracts for particular customer

    table1 table2 table3
    customer1 [nothing] contract41
    customer2 [nothing] contract2000
    customer3 cust3 con1 contract1
    cust3 con2
    cust3 con100
    cust3 con2000
Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

In this Discussion