Sorting in Master-Detail detail table...

How can I (is there a way to...) sort on a field in the detail table of a master-detail combination that is NOT the field used to define the master-detail relationship?!?!

Specifically, I'm using a Guests table (each record identified by a unique Guest ID), and an Orders table (each record containing the Guest ID of the Guest who ordered something as well as the Item # of the item ordered).  I want to display the items ordered by a specific guest, but want to SORT them based on the Item # field (of the Orders table).

Oh... D4 with Access tables, all fields mentioned above have indices.

HELP!!!!!!
Raven1155Asked:
Who is Participating?
 
kretzschmarConnect With a Mentor Commented:
well jim,

thanks for the points :-)
good luck again
will take a look

meikl ;-)

0
 
kretzschmarCommented:
hi raven

what about to select the index of
the field on which you will sort.
use the propertys indexfieldname or
indexname on your detailtable for this

meikl
0
 
Raven1155Author Commented:
Meikl,

Nope.  Changing the indexfieldname or the indexname ruins the master-detail relationship.

NEXT!!  ;^P
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
kretzschmarCommented:
yup raven,

you're right, well then you must do it with a query by usage of an order by clause. if the resultset must be editable, then it could be, that you must use a TUpdateSQL-Component as UpdateObject-Property for this sorted query.

if you leave your email-address here,
then i can send you a sample(paradox-based)
(because it is not much code (<10 lines)
but some adjustments on the propertys)

meikl
0
 
Raven1155Author Commented:
Meikl,

First, thanks for following up.

Second, you've already sent me and example of UpdateSQL code (Thanks).

Third, I tried creating a Master-Detail relationship using Queries (from scratch, I didn't want to lose code I'd already written), and I can get the Master-Detail relationship using the WHERE clause, or I can sort the results using ORDER BY, but I can't get the BDE to accept BOTH clauses in the same SQL statement!

I am sure that this do-able, truly, and would love to hear from other Experts too!!!
0
 
kretzschmarCommented:
hi jim,

yup, i remember me,
(after visiting your history) ;-)

must be access, tell me do you use odbc or
native drivers to access access.

if it not solved until tomorrow,
will i try , the same with access

good night

meikl ;-)
0
 
Raven1155Author Commented:
Meikl,

"native" drivers, 'cuz odbc screws up the field sizes when looking at the Access tables (and the native driver seems to work pretty well otherwise)!

Thanks
0
 
Raven1155Author Commented:
Okay, I've figured out the SQL code that I >could< use to solve this using TQueries (well, TUpdateSQL's), but I >>REALLY<< would appreciate hearing if there is ANY way to sort by (in this case) Item # in a Master-Detail relationship that doesn't USE Item #!?!?!?

{Because I don't really want to use UpdateSQL's because I'm caching my transactions and don't know how this would work UpdateSQL's would work in that case!}
0
 
kretzschmarCommented:
hi raven,

have another solution ;-)
tested with access97 and native-driver.

you must create combined indexes on your detailtable like
masterid,fieldname

masterid must be the first field of the combined-index, because over this field is done the m-d relationship

a sample

procedure TForm1.DBGrid2TitleClick(Column: TColumn);
begin
  If Column.FieldName = 'a' then
    Table2.IndexName := 'a_id' else //index is combined mid,a
  If Column.FieldName = 'b' then
    Table2.IndexName := 'b_id' else //index is combined mid,b
  If Column.FieldName = 'c' then
    Table2.IndexName := 'c_id'; //index is combined mid,c
end;

try it out

meikl
0
 
Raven1155Author Commented:
Meikl,

Please send something as an answer so I can award points.

I found out the basic problem... I'm an idiot! ;^}

I'd tried the dual-field index like you've just suggested, and the item numbers looked messed up.  Turns out that this was my fault (surprise) because of how I was storing Item # (as a string... necessary due to possible values).  I fixed how I stored the Item #, using GetText and SetText, and now it works!

Thanks a LOT for your help!

Jim Sky 8^o

PS- Please see the question list for my NEXT problem!
0
All Courses

From novice to tech pro — start learning today.