DISTINCT Records Aren't Distinct

[Genimuse]

I suspect this has something to do with how the joins are set up (I was lazy and let Access make them rather than writing them myself, but it looks ok), but for some reason I don't actually get distinct records, I still get plenty of dupes:

SELECT DISTINCT mwpax.[Date Modified], mwphy.[First Name], mwphy.[Last Name], mwphy.Code, mwtrn_1.[Procedure Code], mwpro.Description, mwtrn.[Procedure Code], mwpro_1.Description, mwpax.[Payment Amount]

FROM (((mwphy INNER JOIN (mwtrn INNER JOIN mwpax ON mwtrn.[Entry Number] = mwpax.[Payment Reference]) ON mwphy.Code = mwtrn.[Attending Provider]) INNER JOIN mwtrn AS mwtrn_1 ON mwpax.[Charge Reference] = mwtrn_1.[Entry Number]) INNER JOIN mwpro ON mwtrn_1.[Procedure Code] = mwpro.[Code 1]) INNER JOIN mwpro AS mwpro_1 ON mwtrn.[Procedure Code] = mwpro_1.[Code 1]

WHERE (((mwpax.[Date Modified])>=#9/1/2006# AND (mwpax.[Date Modified])<=#9/30/2006#)) AND (mwphy.Code <> '90' AND mwphy.Code <> '96' AND mwphy.Code <> '97' AND mwphy.Code <> '99')

ORDER BY mwphy.[Last Name], mwphy.[First Name], mwtrn_1.[Procedure Code], mwtrn.[Procedure Code] ;

Do I need a rewrite? Sorry, I have no ability to control the lousy fields.

 

[lespaul]

So every field in the result set has the same values? You get a result set like below where EVERY field is the same? Or does each record have a single piece of information that is different?
[tt]
Date Modified FirstName LastName Code procedure Code Description Procedure Code Description PaymentAmount
11/1/2006 Joe Blow ABC DEF Some Description DEF Some Other Desc 25.00
11/1/2006 Joe Blow ABC DEF Some Description DEF Some Other Desc 25.00

[/tt]
[off topic]
Not that it will help in this instance, but for future reference the phrase:

[tt]AND (mwphy.Code <> '90' AND mwphy.Code <> '96' AND mwphy.Code <> '97' AND mwphy.Code <> '99')[/tt]

can be changed to :

[tt]AND mwphy.Code NOT IN('90', '96', '97', '99')[/tt]

a little easier to read and know what's going on!
[/off topic]



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[Genimuse]

Some records are different, some the same (identical). So a subset of actual dataset result:

9/13/2006 7:48:33 PM   Jenny   Allen   38   97124   Therapeutic Massage   INS   Insurance Payment                         0

9/13/2006 7:48:33 PM   Jenny   Allen   38   97124   Therapeutic Massage   INS   Insurance Payment                         0

9/13/2006 7:48:33 PM   Jenny   Allen   38   97124   Therapeutic Massage   INS   Insurance Payment                         0

9/13/2006 8:20:41 PM   Jenny   Allen   38   97124   Therapeutic Massage   INS   Insurance Payment   -8.19

9/13/2006 8:20:41 PM   Jenny   Allen   38   97124   Therapeutic Massage   INS   Insurance Payment   0

The first three are identical. The last two are not, as the amounts are different.

I do love the "NOT IN" construct, but unfortunately this won't be in Access in the end, and the SQL in the actual app doesn't support "IN." Or frankly, a whole ton of other useful SQL. Fortunately basic selects and joins work fine.

 

[lespaul]

Does the actual app support DISTINCT? Have you tried using a BETWEEN instead of the date <= and date >= format? have you tried doing the joins yourself and seeing if you get different results? Maybe the SQL in the app doesn't like the join structure?

leslie

 

[Genimuse]

I'm concerned that it's because I have two tables referenced twice in the join (mwtrn and mwpro), but unfortunately becase a single mwpax record connects to mwtrn two different and exclusive ways, I'm not sure how to get the data. I guess maybe a correlated subquery, but I'm concerned that would further slow an already slow query.

 

[Genimuse]

This is in Access that this is happening, and I know it supports distinct. If I can get it to work in Access then I'll deal with the actual app (which does support DISTINCT). But just so it's not an issue in the question, this is all in Access.

Haven't tried BETWEEN but I'm pretty sure that's not going to affect it. Still, I will.

 

[SkipVought]

Hi,

Chances are that the TIME values are each slightly different, but all FORMAT (round) to the PRECISION of SECONDS.

Do you need the time? If not select the INT of your dates.

Skip,
[sub]
[red][/red]
[/sub]

 

[SkipVought]

ie...

SELECT DISTINCT Int(mwpax.[Date Modified]),...

and if necessary

SELECT DISTINCT CDate(Int(mwpax.[Date Modified])),...

Skip,
[sub]
[red][/red]
[/sub]

 

[Genimuse]

SkipVoight,

Unfortunately the differing times do make them different records, so I need them all. INTing or CDate(Int())ing the datetimes will throw away a bunch of data.

However, CDBLing them does include the microseconds, it seems, and so it may be that I don't have actual dupes.

That, or the CDBL conversion doesn't convert the dates the same every time and so now they seem different.

Still, I'll mess with it a bit and try to figure out if I'm getting dupes after all. Very interesting.

 

[SkipVought]

The difference between seconds is .0000116 of a day.

you could round to the nearest second and then get DISTINCT to the second.

Skip,
[sub]
[red][/red]
[/sub]

 

[SkipVought]

Try this example in VBA

   n1 = Date + 0.090042345678
   n2 = n1 + 0.00000012345
   MsgBox n2 - n1
   MsgBox CDate(Format(n2, "#.000000")) - CDate(Format(n1, "#.000000"))
   MsgBox CDate(Format(n1, "#.000000"))
   MsgBox CDate(Format(n2, "#.000000"))

then code your SQL

SELECT DISTINCT CDate(Format(mwpax.[Date Modified]),"#.000000"))

Skip,
[sub]
[red][/red]
[/sub]