Master Detail question

rbuser

D5, FlashFiler 2.06X, RB 6.0X

If trying to create an Avery mailing label report in the form:

To the parents of: Child.firstname Child.Lastname
Parent.address1
Parent.address2
Parent.city, parent.state parent.zip


With an query setup for two separate tables linked by a parent ID,
(integer field) in common to both, and the datasource for the report set
to the Parent table, the results are that the child table does not
follow the parent table. That is, whatever child table record the report
is sitting on, is displayed for multiple families throughout the report.
The parent records are traversed as expected...but, the child name does
not change.

The parent table is sorted by parent.lastname, the child table is sorted
by parent ID, then grade-descending, (since the oldest child only should
be listed on the label).

The parent table is the master because I only want to print one label
per family, even though there could be multiple children per family. I
can't do a subreport because it is a mailing label, and I need a mixture
of both table's fields in the same label.

I must be missing something very simple. Thanks for any advice on this.

John

digitalmetaphors

How about using a Inner Join in the query to get data from both tables,
instead of master detail linking?


Cheers,

Jim Bennett
Digital Metaphors

rbuser

Jim,

Yes, I tried that after writing my initial message...and it looks
right...However, I get a record for each child, and thus want to "filter" out
all redundant child records...If I had a boolean field "Oldest child" to use
in eliminating the redundant child records it would be great...I am missing
something here...

I have the inner join in place, sorted by parent.lastname + child.grade
descending...So, I'm close...just need to get rid of simblings!!!

Thanks for the help,

John

rbuser

That;s "siblings".
John

digitalmetaphors

There is a DISTINCT in the sql syntax. Grab a good book on sql, such as LAN
TIMES GUIDE TO SQL...

http://www.amazon.com/exec/obidos/ASIN/007882026X/qid=1014133552/sr=1-1/ref=
sr_1_1/103-2660375-5288637


Cheers,

Jim Bennett
Digital Metaphors

rbuser

Thanks Jim...

I initiated a "DISTINCT", (checkbox) for the child table from the RB Query
Designer- here are the results

SELECT DISTINCT child.PARENTID, child.FIRSTNAME,
child.GRADE, child.LASTNAME,
(Child.Firstname + ' ' + Child.Lastname)
Child_Firstname_Child_La
FROM child child
WHERE ( child.GRADE IS NOT NULL )
AND ( child.PARENTID IS NOT NULL )
AND ( child.PARENTID = child.parentid )
ORDER BY child.GRADE DESC

I am definitely going to take up your suggestion on the SQL book...However, I am
designing from the user's perspective from the Query Designer, and just want to
confirm that DISTINCT is working for the FlashFiler plugin...It seems not to
make any difference in the report whether its checked or not. I don't allow the
users to edit the SQL in this particular application.

Thanks very much for the help and the link!!!

John

rbuser

FF 2.06, Inner join of parent to child...
Jim,

Sorry...This is a quick question related also to the Query Designer. Grouping
shows all fields as selected from the field selector, with the ability to move
the fields' position relative to each other.

SELECT Parent.PARENTID, child.FIRSTNAME,
child.GRADE, Parent.FATHER_LAS,
Parent.FATHER_ZIP, Parent.FATHER_CIT,
Parent.FATHER_STA, Parent.FATHER_ADD,
child.PARENTID PARENTID_2,
MIN(child.LASTNAME) MIN_child_LASTNAME
FROM Parent Parent, child child
WHERE (child.PARENTID = Parent.PARENTID)
GROUP BY Parent.PARENTID, child.FIRSTNAME,
child.GRADE, Parent.FATHER_LAS,
Parent.FATHER_ZIP, Parent.FATHER_CIT,
Parent.FATHER_STA, Parent.FATHER_ADD,
child.PARENTID
ORDER BY Parent.FATHER_LAS, child.PARENTID,
child.GRADE DESC


However, they can't be removed. Thus, the selected fields are identical to the
original fields. Is this proper behavior? Grouping works fine on single tables.



Thanks,

John

rbuser

Jim,

A bit more info on this...After experimenting. I see DISTINCT works for the crucial
fields. However, how to you eliminate the spurious fields used with DISTINCT, yet
have access to the remaining fields for use in reports?

Purchased an SQL book. LAN Times book was not readily available.

Thanks,

John

digitalmetaphors

You can't remove the GROUP BY fields in the query designer when the query
includes a calculated field. You are seeing the designed behavior.


Cheers,

Jim Bennett
Digital Metaphors

rbuser

Jim,

Thanks, thought so!
Thanks again,
John