duplicate data using SQL

[pezroyal]

I have this report with left outer join links manually set up. It gives me data I want, no duplicates. However, I copied SQL from this report to check if i get same output. unfortunately the output is duplicate data. how is that possible?cheers

 

[BettyJ]

Under Database menu, see if you have Select Distinct Records checked.

 

[pezroyal]

Cheers BettyJ i have it grayed out...

 

[kray4660]

Odd, normally it is grayed out when you are using SQL (i.e, a command) as a data source or a data source such as Excel, text file, etc. where a server normally is not involved. If you are using a command, you could put the word distinct right after the Select statement. Otherwise an close examination of the SQL (and sometimes using groups) to figure out ways to eliminate the duplication issue.

 

[BettyJ]

In Design View, check if your Details Section is suppressed or hidden. If so, check in which section are the records being displayed.

 

[pezroyal]

hi all
many thanks for all your help.
report header , page footer-suppressed. page header, details are not suppressed. i have sub report(same SQL) in report footer.that is my SQL
SELECT "xdailyissues"."issue_date", "xdailyissues"."patient_name", "xdailyissues"."patient_hospitalno", "xdailyissues"."drugfull", "xdailyissues"."drug_packsize", "xdailyissues"."quantity", "xdailyissues"."return", "xdailyissues"."issue_type", "xdailyissues"."location_code", "xdailyissues"."issue_value", "xdailyissues"."lnkfy", "xdailyissues"."lnkmon", "xdailyissues"."comment", "xdailyissues"."patient_natno", "xdailyissues"."costcentre", "costcentre_groups"."group_name", "costcentre_groups"."group_code", "drug_speccon"."speccon_description"
FROM ("JAC"."costcentre_groups" "costcentre_groups" LEFT OUTER JOIN "JAC_Super"."xdailyissues" "xdailyissues" ON "costcentre_groups"."lnkacb"="xdailyissues"."lnkacb") LEFT OUTER JOIN "JAC"."drug_speccon" "drug_speccon" ON "xdailyissues"."lnkdid"="drug_speccon"."lnkdid"
WHERE ("xdailyissues"."lnkfy"=36 AND "xdailyissues"."lnkmon"='14' OR "xdailyissues"."lnkfy"=36 AND "xdailyissues"."lnkmon"='13') AND "costcentre_groups"."group_code"<>'STH27'
ORDER BY "costcentre_groups"."group_name", "xdailyissues"."issue_date"