distinctrow

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi All,

I am using below qry in a sub form to return distinct records. When I run
the query it does not return distinct records. This query was working fine
when I had the db in access itself. Since, I have moved backend to sql I am
getting duplicate records for the "comment" field. I am thinking to rewrite
the query in sql and create a stored procedure.

Thanks in Advance,
BB

SELECT DISTINCTROW dbo_CaseReviews.Key, dbo_CaseReviews.Reviewer,
dbo_CaseReviews.MissIncDoc, dbo_CaseReviews.Comment, dbo_CaseReviews.ETRec,
dbo_CaseReviews.CassRec, dbo_CaseReviews.LgPrtRec, dbo_CaseReviews.ETLess25,
dbo_CaseReviews.ET25thru50, dbo_CaseReviews.ET50thru100,
dbo_CaseReviews.ETWriting, dbo_CaseReviews.LrgBlk, dbo_CaseReviews.Computer,
dbo_CaseReviews.ReadRec, dbo_CaseReviews.WriterRec,
dbo_CaseReviews.BrailleRec, dbo_CaseReviews.OtherRec, IIf([ETRec]="Y",1,0) AS
ETCount, IIf([ETRec]="N",1,0) AS ETDenied, IIf([CassRec]="Y",1,0) AS
CassCount, IIf([CassRec]="N",1,0) AS CassDenied, IIf([LgPrtRec]="Y",1,0) AS
LgPrtCount, IIf([LgPrtRec]="N",1,0) AS LPDenied, IIf([ETLess25]="Y",1,0) AS
ETLess25Count, IIf([ETLess25]="N",1,0) AS ETLess25Denied,
IIf([ET25thru50]="Y",1,0) AS ET25thru50Count, IIf([ET25thru50]="N",1,0) AS
ET25thru50Denied, IIf([ET50thru100]="Y",1,0) AS ET50thru100Count,
IIf([ET50thru100]="N",1,0) AS ET50thru100Denied, IIf([ETWriting]="Y",1,0) AS
ETWritingCount, IIf([ETWriting]="N",1,0) AS ETWritingDenied,
IIf([LrgBlk]="Y",1,0) AS LrgBlkCount, IIf([LrgBlk]="N",1,0) AS LrgBlkDenied,
IIf([Computer]="Y",1,0) AS ComputerCount, IIf([Computer]="N",1,0) AS
ComputerDenied, IIf([ReadRec]="Y",1,0) AS ReadCount, IIf([ReadRec]="N",1,0)
AS ReadDenied, IIf([writerRec]="Y",1,0) AS WCount, IIf([writerRec]="N",1,0)
AS WDenied, IIf([brailleRec]="Y",1,0) AS BCount, IIf([brailleRec]="N",1,0) AS
BDenied, IIf([OtherRec]="Y",1,0) AS OtherCount, IIf([OtherRec]="N",1,0) AS
ODenied
FROM dbo_CaseReviews;
 
If you simply want non-duplicate records in your results, try:

SELECT DISTINCT dbo_...

Hi All,

I am using below qry in a sub form to return distinct records. When I run
the query it does not return distinct records. This query was working fine
when I had the db in access itself. Since, I have moved backend to sql I am
getting duplicate records for the "comment" field. I am thinking to rewrite
the query in sql and create a stored procedure.

Thanks in Advance,
BB

SELECT DISTINCTROW dbo_CaseReviews.Key, dbo_CaseReviews.Reviewer,
dbo_CaseReviews.MissIncDoc, dbo_CaseReviews.Comment, dbo_CaseReviews.ETRec,
dbo_CaseReviews.CassRec, dbo_CaseReviews.LgPrtRec, dbo_CaseReviews.ETLess25,
dbo_CaseReviews.ET25thru50, dbo_CaseReviews.ET50thru100,
dbo_CaseReviews.ETWriting, dbo_CaseReviews.LrgBlk, dbo_CaseReviews.Computer,
dbo_CaseReviews.ReadRec, dbo_CaseReviews.WriterRec,
dbo_CaseReviews.BrailleRec, dbo_CaseReviews.OtherRec, IIf([ETRec]="Y",1,0) AS
ETCount, IIf([ETRec]="N",1,0) AS ETDenied, IIf([CassRec]="Y",1,0) AS
CassCount, IIf([CassRec]="N",1,0) AS CassDenied, IIf([LgPrtRec]="Y",1,0) AS
LgPrtCount, IIf([LgPrtRec]="N",1,0) AS LPDenied, IIf([ETLess25]="Y",1,0) AS
ETLess25Count, IIf([ETLess25]="N",1,0) AS ETLess25Denied,
IIf([ET25thru50]="Y",1,0) AS ET25thru50Count, IIf([ET25thru50]="N",1,0) AS
ET25thru50Denied, IIf([ET50thru100]="Y",1,0) AS ET50thru100Count,
IIf([ET50thru100]="N",1,0) AS ET50thru100Denied, IIf([ETWriting]="Y",1,0) AS
ETWritingCount, IIf([ETWriting]="N",1,0) AS ETWritingDenied,
IIf([LrgBlk]="Y",1,0) AS LrgBlkCount, IIf([LrgBlk]="N",1,0) AS LrgBlkDenied,
IIf([Computer]="Y",1,0) AS ComputerCount, IIf([Computer]="N",1,0) AS
ComputerDenied, IIf([ReadRec]="Y",1,0) AS ReadCount, IIf([ReadRec]="N",1,0)
AS ReadDenied, IIf([writerRec]="Y",1,0) AS WCount, IIf([writerRec]="N",1,0)
AS WDenied, IIf([brailleRec]="Y",1,0) AS BCount, IIf([brailleRec]="N",1,0) AS
BDenied, IIf([OtherRec]="Y",1,0) AS OtherCount, IIf([OtherRec]="N",1,0) AS
ODenied
FROM dbo_CaseReviews;
Click to expand...
 
I did try using Distinct instead of distinct row it retrieves the data very
slowly plus every time I move from one page to another, it calculates the
query again which makes scrolling the page extremely slow.

Thanks,
BB

kingston via AccessMonster.com said:
If you simply want non-duplicate records in your results, try:

SELECT DISTINCT dbo_...

Hi All,

I am using below qry in a sub form to return distinct records. When I run
the query it does not return distinct records. This query was working fine
when I had the db in access itself. Since, I have moved backend to sql I am
getting duplicate records for the "comment" field. I am thinking to rewrite
the query in sql and create a stored procedure.

Thanks in Advance,
BB

SELECT DISTINCTROW dbo_CaseReviews.Key, dbo_CaseReviews.Reviewer,
dbo_CaseReviews.MissIncDoc, dbo_CaseReviews.Comment, dbo_CaseReviews.ETRec,
dbo_CaseReviews.CassRec, dbo_CaseReviews.LgPrtRec, dbo_CaseReviews.ETLess25,
dbo_CaseReviews.ET25thru50, dbo_CaseReviews.ET50thru100,
dbo_CaseReviews.ETWriting, dbo_CaseReviews.LrgBlk, dbo_CaseReviews.Computer,
dbo_CaseReviews.ReadRec, dbo_CaseReviews.WriterRec,
dbo_CaseReviews.BrailleRec, dbo_CaseReviews.OtherRec, IIf([ETRec]="Y",1,0) AS
ETCount, IIf([ETRec]="N",1,0) AS ETDenied, IIf([CassRec]="Y",1,0) AS
CassCount, IIf([CassRec]="N",1,0) AS CassDenied, IIf([LgPrtRec]="Y",1,0) AS
LgPrtCount, IIf([LgPrtRec]="N",1,0) AS LPDenied, IIf([ETLess25]="Y",1,0) AS
ETLess25Count, IIf([ETLess25]="N",1,0) AS ETLess25Denied,
IIf([ET25thru50]="Y",1,0) AS ET25thru50Count, IIf([ET25thru50]="N",1,0) AS
ET25thru50Denied, IIf([ET50thru100]="Y",1,0) AS ET50thru100Count,
IIf([ET50thru100]="N",1,0) AS ET50thru100Denied, IIf([ETWriting]="Y",1,0) AS
ETWritingCount, IIf([ETWriting]="N",1,0) AS ETWritingDenied,
IIf([LrgBlk]="Y",1,0) AS LrgBlkCount, IIf([LrgBlk]="N",1,0) AS LrgBlkDenied,
IIf([Computer]="Y",1,0) AS ComputerCount, IIf([Computer]="N",1,0) AS
ComputerDenied, IIf([ReadRec]="Y",1,0) AS ReadCount, IIf([ReadRec]="N",1,0)
AS ReadDenied, IIf([writerRec]="Y",1,0) AS WCount, IIf([writerRec]="N",1,0)
AS WDenied, IIf([brailleRec]="Y",1,0) AS BCount, IIf([brailleRec]="N",1,0) AS
BDenied, IIf([OtherRec]="Y",1,0) AS OtherCount, IIf([OtherRec]="N",1,0) AS
ODenied
FROM dbo_CaseReviews;
Click to expand...
Click to expand...
 
Back
Top