Howdy, Stranger!

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

Categories

Recursive / Append Query

jmalcolmjmalcolm Member Posts: 1
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

  • dokken2dokken2 Member Posts: 532
    : 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.