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

> > >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;
>
>CREATE TABLE ACM_DOUBLEKNOCK
>(
> DN_ID SMALLINT NOT NULL,
> DN_AD_REGID D_NOT_NULL_ZERO ,
> DN_AC_ID D_NOT_NULL_ONE ,
> DN_PERIOD D_NOT_NULL_ZERO ,
> DN_DESCRIPTION VARCHAR( 128) NOT NULL COLLATE NONE,
> CONSTRAINT PK_DOUBLEKNOCK PRIMARY KEY (DN_ID)
>);
>
>ALTER TABLE ACM_DOUBLEKNOCK ADD CONSTRAINT U_DN_DESCRIPTION UNIQUE
> (DN_DESCRIPTION);
>
>Please note that DN_AC_ID is also a FOREIGN KEY REFERENCES ALARMCODES

This isn't really OK.
Please note that DN_AD_REGID is also a FOREIGN KEY,

Not anything in this arrangement?

>CREATE TABLE ALARMCODES
>(
> AC_ID SMALLINT NOT NULL,
> AC_TYPEMASK SMALLINT NOT NULL,
> AC_ALARMSTR VARCHAR( 128) NOT NULL COLLATE NONE,
> AC_DESCRIPTION VARCHAR( 128) NOT NULL COLLATE NONE,
> CONSTRAINT PK_ALARMCODES PRIMARY KEY (AC_ID)
>);

OK, here's the original stuff, with suggestions.

The sql use on the master IB_Query_Master is:

SELECT ACM_DOUBLEKNOCK.DN_ID
FROM ACM_DOUBLEKNOCK

KeyLinks contains:

ACM_DOUBLEKNOCK.DN_ID

OK.

And I have not provided Insert, Update or Delete SQL for master
IB_Query_Master.

Not necessary. But I'm suspicious of holding that FK link to the
AlarmCodes table in the master. Since your intersection table implements
the relationship as Many:Many, this column (and relationship) does not
belong and it should be removed.
If, on the other hand, your data model requires it to be there (there is a
1:Many relationship between DoubleKnock and AlarmCode) then
DoubleKnockItems doesn't belong in this picture.

The sql use on the detail IB_Query_Detail is:

SELECT
ACM_DOUBLEKNOCKITEMS.DNI_DN_ID,
ACM_DOUBLEKNOCKITEMS.DNI_AC_ID
FROM ACM_DOUBLEKNOCKITEMS
LEFT JOIN ALARMCODES
ON(ACM_DOUBLEKNOCKITEMS.DNI_AC_ID=ALARMCODES.AC_ID)
LEFT JOIN ACM_DOUBLEKNOCK
ON(ACM_DOUBLEKNOCKITEMS.DNI_DN_ID=ACM_DOUBLEKNOCK.DN_ID)

Right, now, null codes don't belong in a master-detail relationship. Null
"keys" just disappear, since they will never be found by a matching
masterlink.

I understand why you tried this - you want to see all AlarmCodes, including
those that are not currently associated with any DoubleKnock. This query
potentially gives you nulls in one or both segments of the keylinks and
that's why the dataset can't do the positioning it needs to do in order to
be scrollable.

1) the detail query is actually this simple:

SELECT
ACM_DOUBLEKNOCKITEMS.DNI_DN_ID,
ACM_DOUBLEKNOCKITEMS.DNI_AC_ID,
/* and, if you like */
(SELECT ACM_ALARMCODES.AC_DESCRIPTION
FROM ACM_ALARMCODES
WHERE ACM_ALARMCODES.AC_ID = ACM_DOUBLEKNOCKITEMS.DNI_AC_ID) AS
ACDescription
FROM ACM_DOUBLEKNOCKITEMS

Link the set's Mastersource property to the datasource pointed to by the
Master's datasource, and the MasterLinks as DNI_DN_ID=ACM_DOUBLEKNOCK.DN_ID.

Assuming you want all of the possible AlarmCodes to be part of the deal,
then this actually a Keysource-Lookup relationship, where the Keysource of
the AlarmCodes query is the datasource of the detail query.

Now, here is the query for your Alarmcodes:

SELECT
AC_ID,
..maybe other fields,
AC_DESCRIPTION
FROM ACM_ALARMCODES

Link this query's KeySource to the datasource of the detail query.

Set its KeyLinks as follows:

AC_ID=ACM_DOUBLEKNOCKITEMS.DNI_AC_ID

If your detail grid is making use of the description derived from the
correlated subquery on ACM_ALARMCODES (example suggested above) then you
can also set KeyDescLinks on this lookup dataset, as follows:

AC_DESCRIPTION=ACDescription.

The Insert SQL used on IB_Query_Detail is:

[snip]

Custom XxxxxSQL properties won't be needed. The Master-detail relationship
will take care of the first segment of the key, the Lookup relationship
will take care of the second. All that's needed is to pop a
TIB_LookupCombo into the detail grid.

If you don't want to implement the Keysource relationship using
tib_lookupcombo, no matter - provide some other way for the user to select
the required AlarmCodes key during Inserts and Updates. The KeyLinks will
ensure that the selected code gets written.

DeleteSQL is not needed, since, with Master-Detail, you are only going to
delete records from the current subset.

Now, where you are going to trip yourself up is on *changing* the master
side of the intersection table's primary key. If you implement
Master-detail, then you have to provide a way to change keys without
violating the existing relationship, i.e. switching the master side of the
key from the one that is visible (i.e. the one that currently selects it as
a detail) to one that is not visible (is not the current master).

Think about this one for a bit...and consider how you can implement another
part of your GUI that allows the master side of the intersection key to be
changed, given that the detail side of a master-detail relationship is
designed to form the relationship and to keep it intact.

Helen