Sergey

Thanks for explaining how to do this.

BTW the WHERE ac.CID + pr.CID+ ct.CID NOT IN ( ;
SELECT bkey FROM crsBkeys )
caused a few problems with my tables, but I just used an intermediate cursor and it works very well.

Thanks
Cyril


>Cyril,
>
>Try

* Get all existing combinations
>SELECT DIST FK_Accounts + FK_Periods + FK_Categories AS bkey ;
>		FROM Budget ;
>	INTO CURSOR crsBkeys NOFILTER
>* Get new combinations	
>SELECT 	ac.CID AS FK_Accounts, ;
>		pr.CID AS FK_Periods, ;
>		ct.CID AS FK_Categories ;
>	FROM Accounts ac, Periods pr, Categories, ct ;
>	WHERE ac.CID + pr.CID, ct.CID NOT IN ( ;
>			SELECT bkey FROM crsBkeys ) ;
>	INTO CURSOR crsNewbkeys		
>

>>How do you get all possible combinations from three tables into a fourth table - but only if that particular comination does not already exist?
>>
>>The kind of data I am working with is:-
>>
>>
>>
>>Sample report from a Budget table:
>

>>       Accounts:       Periods:        Categories:     Budget amount:
>>---------------------------------------------------------------------
>>       Rent              Jan 2004       South           100.00
>>       Rent              Feb 2004       South           200.00
>>       Electricity       Jan 2004       South           300.00
>>
>>
>>The records in the budget table look like this:
>>
>>CID   FK_Accounts    FK_Periods      FK_Categories    nAmount
>>--------------------------------------------------------------
>>B1       A1              P1               C2           100.00
>>B2       A1              P2               C2           200.00
>>B3       A2              P1               C2           300.00
>>
>>The three FK (foreign key) fields come from three tables as follows:
>>
>>a) Accounts:
>>CID    Description
>>-------------------
>>A1      Rent
>>A2      Electricity
>>
>>b) Periods:
>>CID    Description
>>-------------------
>>P1      Jan 2004
>>P2      Feb 2004
>>
>>c) Categories:
>>CID    Description
>>-------------------
>>C1      North
>>C2      South
>

>>Here is the question: I want to create a record in Budget for every possible combination of the records in the three tables, but only if that record does not exist already in Budget. Only one of each combination is allowed in Budget.
>>
>>
>>Typical number of records:
>>a) Accounts: 300
>>b) Periods: 24
>>c) Categories: 100
>>
>>The budget table is therfeore quite big.
>>Is there a good SQL approach to this kind of problem?
>>
>>Cyril