Thanks Helen.
You really do have incredible patience with other proples problems !
I shall sit and digest your words and see if I can make it all work.

Dave

--
'Probable impossibilities are to be preferred to improbable possibilities.'
Aristotle
"Helen Borrie" <helebor@...> wrote in message
news:5.1.0.14.2.20030205103137.04196970@......

> At 10:58 PM 4/02/2003 +0000, you wrote:
> >Looking for Ideas and a solution.
> >I have a table with four columns. Lets call them ColA, ColB, ColC and
> >ColR.
> >
> >(The table represents finding a tyre code from a manufacturer).
> >Suppose A has three different values in it 13, 15, and 17.
> >For each of these values B has several values such as 145, 155, 165
> >etc perhaps up to ten different values for each value in col A.
> >Col C has several values for each value of B. Col R is the 'result'
> >that we are trying to find.
> >So a few records from the table could look like:
> >
> >13,145,45,P
> >13,145,50,Q
> >13,145,65,R
> >15,155,50,Q
> >15,155,60,R
> >15,155,75,T etc
> >
> >The user needs to enter a lgitimate value for A then one for B then
> >one for C and hence get the result from R.
> >
> >So I have the table called 'tyres' and i put a query on col A
> > select distinct A from tyres,
> >
> >Then another query on col B
> > select distinct B from tyres where A = :A
> >
> >Then another query on col C
> > select C,R from tyres where A = :A and B=:B
> >
> >Passing the values from one query to the next. Fine it works.
> >But why not Master-Detail. OK I set that up, that works nicely.
> >
> >But these are actually lookup combos and I want to link them into a
> >table called Cars.
> >
> >As soon as I try to make a query operate in Master-detail and link
> >its keylink to the Cars key it all siezes up solid.
> >
> >For example query B says 'select distinct colB from tyres'
> > master source is a source from queryA
> > master links say colA=tyres.colA
> > keysource is source from cars query
> > keylinks say colB (of course cars has a colB).
> >
> >I have struggled for two days. But I get a message on the lines
> >of 'multiple lines from a singleton query'. As well as assorted key
> >violations.
> >
> >Perhaps I am approaching the problem from the wrong end.
>
> David,
>
> 'Multiple lines from a singleton query' always indicates some form of
> ambiguity that the logic of your application can't resolve. There
> are...errmm....several ambiguities in this setup.
>
> A master-detail needs a set of unique master rows, each of which can
> connect to zero or more detail rows. The detail set's masterlinking needs
> to point back to one and only one master at each respective level. But,
> by using SELECT DISTINCT, you have set up ambiguous masterlinking at both
> levels of the drill-down structure.
>
> Now, going to the other end of your model, what you are actually looking
> for is a single keylinks value of colR to hook up with the polling column
> in Cars, right? Now, ColR is not a unique pointer to any of the other
> columns in Tyres. For example, Colr='Q' can apply (theoretically) to any
> combination of ColA + ColB + ColC. Here you have another ambiguity. The
> polling code in a keylinked relationship (the pointing key in the
> Keysource) must point to one and only one row in the lookup table. Your
> structure doesn't permit this.
>
> To make this work, back in DDL-land, you need to alter the Tyres table to
> make a surrogate key (call it ColPK) for the complete combination of all

of

> the other columns that form the unique group - in this case, presumably
> ColA, ColB and ColC. Place a unique constraint across these three
> columns. If you must implement keysource linking between the two
> structures, then the unique key of the Tyres table (PointToColPK) is what
> you must store in Cars, not a pointer to ColR (PointToColR).
>
> Now, let's say you DO want this live-linking, rather than just to ensure
> that a valid ColR code gets stored there.
>
> In this case, the Keysource set needs to include both the PointToColPK
> column (with Visible=False in your output) plus a subquery on the Tyres
> table to deliver the value of ColR as a read-only value in the output set.
>
> Keysource table:
>
> SELECT Cars.P, Cars.Q, Cars.R,
> Cars....,
> Cars.PointToColPK,
> (SELECT Tyres.ColR from Tyres
> where Tyres.ColPK=Cars.PointToColPK) as TyreCode
> from Cars
>
> Remember to set the Computed attribute of TyreCode to True.
>
> In the Lookup table, you need both the primary key and ColR. You would

set

> KeySource (obviously) plus KeyLinks (Tyres.ColPK=Cars.PointToColPK) and
> KeyDescLinks (Tyres.ColR=TyreCode).
>
> You still have to resolve the ambiguities in your drill-down, which has to
> arrive at a single Tyres record (ColPK) that is a unique combination of
> ColA, ColB and ColC. If you don't constrain this group for uniqueness,
> then you still have ambiguity, since it will be possible to have the same
> group pointing to more than one ColR value. If this is a requirement,

then

> ColR needs to be included in the unique constraint.
>
> Nested master-detail linking looks attractive as a way to achieve the
> drill-down but you have bumped into two problems:
>
> 1) unless you use DISTINCT, your sets will contain duplicate values.
> 2) your output sets currently don't contain enough keys to avoid

ambiguity.

>
> It will be OK to use DISTINCT on the topmost set (ColA). But you *want*
> the duplicate occurrences in the lower sets. You would need to revisit

the

> structure and the UI. Don't use DISTINCT for these. You would include the
> keys so far in the hierarchy but set all of the columns invisible except
> the one you wanted to display for selection. The lowest level set (the

one

> that is hooked by the keysource set) would need all of the columns.
>
> But..but..but...although all of this resolves the ambiguities, all of this
> linking is still going to cause synch problems at the times when the
> keysource is polling out to the lookup set, i.e. whenever you scroll the
> parent set. Your AfterScroll event will need to use some jiggery-pokery
> with the keys on the bottom-level dataset to reach out and "fix on" the
> right records in the other two sets. Without it, your lockup problem

won't

> go away.
>
> It could be done; but it might make more sense to decide between
> masterlinking the selection structure and keylinking the lookup. You can
> retain the keylinking and use a non-linked mechanism to drill down to the
> bottom set; or you can decide that you want to stick with the

masterlinked

> structure for your selection UI and not use keylinking for editing the

Cars

> set's tyre pointer code...
>
> Or...if the number of rows in Tyres is quite small and not likely to grow
> very quickly, you could turn the whole thing upside down, so that your
> current bottom level set (after the structure changes, of course) displays
> ColA, ColB and ColC for selection as a horizontal set in a
> hierarchically-ordered output set. This way, if the embedded lookup is
> important, you can do it without complications. The user selects the
> combination she wants for the tyre and the Keysource takes the (invisible)
> primary key from the selected combination.
>
> Helen
>
>
>
>
>
>
>

___________________________________________________________________________

> IB Objects - direct, complete, custom connectivity to Firebird or

InterBase

> without the need for BDE, ODBC or any other layer.
>

___________________________________________________________________________

> http://www.ibobjects.com - your IBO community resource for Tech Info

papers,

> keyword-searchable FAQ, community code contributions and more !
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
>
>
>