Duplicate entries arn't shown when SQL contains an ORDER BY statment

rbuser

I'm using Delphi 7 and RB 9.02.

I am selecting my data via the report designer's DADE connection. Here is
the SQL that it created:

SELECT Cards.Surname,
Cards."First Name" First_Name,
Cards.Title,
Cards."Emp Number" Emp_Number,
Cards."Card Number" Card_Number,
Access.Mode,
Access."Reader Group" Reader_Group,
Access."Time Period" Time_Period,
Access."Card Group" Card_Group,
Cards."Card Status" Card_Status
FROM "Cards.DB" Cards
RIGHT OUTER JOIN "CardsCG.DB" CardsCG ON
(CardsCG."Card Number" = Cards."Card Number")
INNER JOIN "Access.DB" Access ON
(Access."Card Group" = CardsCG."Card Group")
WHERE ( Cards.Surname <> "null" )
ORDER BY Access.Mode, Cards.Surname,
Access."Reader Group"

This creates the records that I need and I have confirmed it by checking the
returned dataset. In the report I have a group that shows all the "Reader
Groups" for each user. The problem is that I can see in the dataset that a
particular user has 4 "Reader Groups" but only 2 are shown. If I remove the
"ORDER BY" entry for the Reader Group then all 4 are shown. In the data set
two of the Reader Groups are the same, EG:

Rdr Grp 1
Rdr Grp 2
Rdr Grp 1
Rdr Grp 2

But the report shows just "Rdr Grp 1" and "Rdr Grp 2" when the "ORDER BY"
entry for the Reader Group is present. It looks as if the report is hiding
or discarding what it sees as duplicate entries. I am using this "ORDER BY"
entry because I want the Reader Groups to be sorted alphabetically, EG:

Rdr Grp 1
Rdr Grp 1
Rdr Grp 2
Rdr Grp 2

As these group names can be quite long (but not in my test data) I am
displaying them in a memo control that is populated via a variable's OnCalc
event.

Ian Munro

nicocizik

Hi Ian,

I would suggest using a DBMemo object rather than manually populating a memo
object in the OnCalc of a variable. This will simply traverse the data it
is given.

--
Regards,

Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com