At 04:10 PM 29/11/2004 +1100, you wrote:

> >
> > If I ended up with this kind of pickle in a master-detail
> > structure, I'd be taking a harsh, critical look at my table
> > structures to work out where I stuffed up in my data modelling.
> >
> > Helen
> >
> >
>
>Hi helen,
>
>OK I have set the IB_Query_Detail MasterSource Property to point to the
>IB_DataSource of the IB_Query_Master.
>
>The will need some help with the SQL, as I am not able to come up with an
>alternative.
>
>The detail table is defined as:
>
>CREATE TABLE ACM_DOUBLEKNOCKITEMS
>(
> DNI_DN_ID D_NOT_NULL_ONE ,
> DNI_AC_ID D_NOT_NULL_ONE ,
> CONSTRAINT PK_DOUBLEKNOCKITEMS PRIMARY KEY (DNI_DN_ID, DNI_AC_ID)
>);
>
>ALTER TABLE ACM_DOUBLEKNOCKITEMS ADD CONSTRAINT FK_DNI_AC_ID
> FOREIGN KEY (DNI_AC_ID) REFERENCES ALARMCODES
> (AC_ID)
> ON DELETE CASCADE
> ON UPDATE CASCADE;
>
>ALTER TABLE ACM_DOUBLEKNOCKITEMS ADD CONSTRAINT FK_DNI_DN_ID
> FOREIGN KEY (DNI_DN_ID) REFERENCES ACM_DOUBLEKNOCK
> (DN_ID)
> ON DELETE CASCADE
> ON UPDATE CASCADE;
>
>As you can see is a table with only two fields which are foreign keys.
>
>What I am trying to achieve with the SQL s that once the application user
>clicks on a record on the left grid(master records), which displays the
>DN_ID of the ACM_DOUBLEKNOCK, the right grid(detail records) will display
>any records which have DNI_DN_ID = DN_ID.
>
>And if the user would like to insert a new detail record, I would like the
>right grid to by default populate the DNI_DN_ID field with the currently
>selected master DN_ID record data.
>
>I am not sure if I have explain myself clearly.

OK...would you post the structures of AlarmCodes and DoubleKnock, too...
what I think you are after is to implement a classic Many:Many
relationship. - since your DoubleKnockItems record is nothing but an
intersection structure between DoubleKnock and AlarmCodes.

If what you really want to achieve is to match up/create intersection
records for each occurrence of a DoubleKnock and an AlarmCode, then there
*is* certainly an *easy* right way to do this

>Any help regarding the SQL for the detail would be very welcome

Yes, sure, but will you post the structures of AlarmCodes and DoubleKnock,
including the constraints?

Helen