Oh, DISTINCTROW, again.

Dear Experts, I am very experirnced MS Access user, and my XP / Access 2007 workstation serves me well since 2007. Nevertheless please help me to understand output of the following two SELECT statements that I launch from Immediate WIndow (they only differ with one keyword):
?CurrentProject.Connection.execute("Select              * FROM [Space Load by Hour/Daytype]").GetString
B14_FCU_14  B14_FCU_14  3/13    Weekend 16  -0.229166666666667  -0.229166666666667  -0.229166666666667
B14_FCU_14  B14_FCU_14  3/13    Weekend 17  -0.229166666666667  -0.259722222222222  -0.275
B14_FCU_14  B14_FCU_14  3/13    Weekend 18  -0.229166666666667  -0.267361111111111  -0.275
B14_FCU_14  B14_FCU_14  3/13    Weekend 19  -0.229166666666667  -0.252083333333333  -0.320833333333333
B14_FCU_14  B14_FCU_14  3/13    Weekend 20  -0.229166666666667  -0.236805555555556  -0.275
B14_FCU_14  B14_FCU_14  3/13    Weekend 21  -0.229166666666667  -0.229166666666667  -0.229166666666667

?CurrentProject.Connection.execute("Select  distinctrow * FROM [Space Load by Hour/Daytype]").GetString
B14_FCU_14  B14_FCU_14  3/13    Weekend 18  -0.275  -0.275  -0.275


?syscmd(acSysCmdAccessVer)
12.0

Open in new window

(see also the attached image)Amazing effect of DISTINCTROW
LVL 1
midfdeAsked:
Who is Participating?
 
midfdeAuthor Commented:
>>...duplicate have been deleted...
Can you see any "duplicates" in my example please?
0
 
Dale FyeCommented:
Select * FROM [Space Load by Hour/Daytype]

will return all of the records from your [Space Load by Hour/DayType] table (or query).

As soon as you add either the DISTINCT or DISTINCTROW, the query will start checking for duplicates.  With the syntax you have listed above, either Distinct or DistinctRow should return the same result set, which is a subset of the original table where all of the duplicate have been deleted.  The is a difference between the DISTINCT and DISTINCTROW is that the DISTINCT predicate causes Access to look at only those fields returned by the query, while the definition of DISTINCTROW in the online MSDN help is:

 DISTINCTROW has an effect only when you select fields from some, but not all, of the tables used in the query. DISTINCTROW is ignored if your query includes only one table, or if you output fields from all tables.

I very rarely use DISTINCTROW.
0
 
Gustav BrockCIOCommented:
No, I see no duplicates, but I think the use of distinctrow here is a misuse.
It should only be used when you join a table with one or more other tables.

I guess you wish all records to be returned, so just a straight select query.

Neither do I use it much - perhaps not at all. It has always (and that is since Access 1.0) been a bit weird to me.

/gustav
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
peter57rCommented:
I can't replicate your problem here, and I also agree that use of distinctrow in this case should make absolutely no difference to the results.

So it looks like there is a problem somewhere in your Access installation or in the specific database or there is something about your data that Access can't handle.

Try the same expression using a different table and see if you still get the problem.

In terms of why you might be using Distinctrow, if you are trying to remove entire duplicates from the output then use Distinct rather than Distinctrow.
0
 
midfdeAuthor Commented:
Here is the story. Two copies of a large(!, with hundreds of DISTINCTROWs) Access application running on 2003 (returns 6 rows as expected) and 2007 (returns absurd one row) gave different results. I am asking experts why. Is there anything other than "Who knows?" that I might hope to get, in order to achieve 2007's decent behavior? (I assume experts are aware of DISTINCT's side effect -- truncation of long texts).
0
 
Gustav BrockCIOCommented:
First, A2007 is known for many bugs, if this is one of them, however, I don't know.
Are you running with the latest service packs and updates?
Any chance you could run it A2010 or A2103? You can download a 30 day trial for free.

Second, I can't see how DISTINCT would help. Your last three fields - the doubles - look not at all identical.
Or they the result of adding/subtracting values from child records? Except for the last field, none of the others contains the value -0.275 which is the single value in all three fields of the bottom result.

/gustav
0
 
midfdeAuthor Commented:
>>... I don't know...
I am asking if there is somebody who does, please. If not, it's an answer too. That I have to (1) re-install OS, then (2) Access, then (3) try another Access, and wait until my customer simply forgets about this mishap, until... next one happens with hopefully another customer with my "Windows 'is known for many bugs' (you'd be surprised how true this is!) " explanation.
0
 
Dale FyeCommented:
midfde,

I've never run into this problem.

As I stated and was stated by others.  Don't use DistinctRow, especially when using a single table.  Did you open up the MSDN help link and read the descriptions provided for DISTINCT and DISTINCTROW?

Have you tried the query using "DISTINCT" instead of "DISTINCTROW"?
0
 
peter57rCommented:
I don't think anyone here knows the answer to your question- which in a nutshell is 'why is A2007 different to A2003'.
It sounds to me that you don't have any issues of understanding of what should be happening , it's just case of why is gong wrong.

As I said earlier , I can't replicate this problem in A2007 here, so that must mean that the circumstances are specific to your setup.  You need to be sure that A2007 is up-to-date (sp3, I think) and if you can , try a different machine to see if the problem persists.  If you can extract a non-confidential bit of your database into a new file and that exhibits the same problem, then post it here and we'll try it.
0
 
Gustav BrockCIOCommented:
Also, I still don't see how this single record - distinct, distinctrow or all:

B14_FCU_14  B14_FCU_14  3/13    Weekend 18  -0.275  -0.275  -0.275

relate to the six others?

Is the single row or the six rows the desired output, or are they all wrong?

/gustav
0
 
midfdeAuthor Commented:
The answer to both of fyed questions is "yes". About "Don't..." Our developers favor  (just-in-case) DISTINCTROW because of aforementioned DISTINCT's side effect (Customer's complaint: "'Anomaly details' field is truncated in the middle of the text..."). And, yes, the applications ~10 years old. (I personally hate DISTINCTROW because this devilish invention is alien to all other SQL dialects, but customers do not care. Do they?)
0
 
midfdeAuthor Commented:
To: /gustav
"Desired" is a correct one, i.e. the one that returns all 6 apparently distinct rows.
0
 
Dale FyeCommented:
There are work-arounds to the memo field truncation effects of DISTINCT, but I believe that DISTINCTROW has that same truncation issue.  like I said, I never use the latter.
0
 
Gustav BrockCIOCommented:
> "Desired" is a correct one, i.e. the one that returns all 6 apparently distinct rows.

If so, something strange is going on.

Have you tried (is it possible) to rewrite the query using Group By?
In most (all?) cases it works exactly like DISTINCT (but not like distinctrow).

/gustav
0
 
midfdeAuthor Commented:
to fyed:
>>I believe that DISTINCTROW has that same truncation issue
You are wrong. "I never..." is not an argument. A guy was proud to "never use" regular expressions. Is it about RE or about the guy?

to /gustav:
>>If so, something strange is going on.
Me too.
0
 
midfdeAuthor Commented:
To: peter57r
You are right. In fact I am almost done with his issue. Long and ounerous investigations form my job after all. I just wanted to be sure I did not miss something easy like "Hey, you, Click on... then uncheck such-n-such and you are done with your stupid question."
0
 
Dale FyeCommented:
OK, I'm wrong, happens more often than I'd like.  Among other things, I don't use DISTINCTROW for the same reason you don't like it, it is not transportable to other RDBs.

What results did you get from the "DISTINCT" clause?  Did you get the correct records, but with the truncation?  If so, you could modify your query something like:

SELECT T1.Field1, T1.Field2, T1.Field3, T1.Field4, T2.MemoField
FROM (SELECT DISTINCT Field1, Field2, Field3, Field4 FROM yourTable) as T1
INNER JOIN yourTable as T2
ON T1.Field1 = T2.Field1
AND T1.Field2 = T2.Field2
AND T1.Field3 = T2.Field3
AND T1.Field4 = T2.Field4

This will give you all the unique combinations of fields (Field1-Field4) and the entire memo field associated with each of those.
0
 
Dale FyeCommented:
@midfde,

BTW, I never use regular expressions, either.  I know they are useful, I've just never taken the time to sit down and figure out how they work.  Every time I try, it makes my head hurt.

;-)
0
 
midfdeAuthor Commented:
to fyed:
Perhaps I was not clear enough in my explanations. In my example there are no memo fields. I've been mentioning it to explain why they are omnipresent throughout the application: who (of devs) wants to "un-nest" complicated queries to find out that there is (not) / will (not) later be memo fields. DISTICTROW is OK for this purpose unless it returns a crude lie(despite of all manuals).
0
 
midfdeAuthor Commented:
to fyed:
>>I've just never taken the time
Programming is about knowledge.I am not against a person who does not know, I just do not like when he is "proud' of his unawareness of, say, grep.
0
 
Dale FyeCommented:
I understand your point.  I generally try to avoid free-form text or memo fields in my DISTINCT select statements.  If I need the text/memo field later, then I use the technique mentioned above (or possibly the fconcat() function to concatenate all of the text comments/memo data into a single field for each DISTINCT record, based upon the other fields.

I cannot for the life of me understand how those two queries would return such different results (both records and values), that was why I asked about the DISTINCT predicate and what results you got from it (you never said).

Have you tried creating a new database and importing all of your objects into it?  Or refreshing all of your references?
0
 
midfdeAuthor Commented:
DISTINCT returns 6 rows as expected.
0
 
midfdeAuthor Commented:
I ceated a function to be used in computed SELECTs
Function distPredicate(tailClauses As String) As String
    Dim fld As field
    distPredicate = "DISTINCT"
    For Each fld In gdbCurr.OpenRecordset("select top 1 * " & tailClauses).Fields
        If fld.Type = dbMemo Then
            distPredicate = distPredicate & "ROW"
            Exit For '---->
        End If
    Next fld
    distPredicate = " " & distPredicate & " "
End Function
=================================
selectClause = " Select " & distPredicate(tailClauses) & Join(dicColumns.Items, COMMA_SPACE & vbNewLine) & tailClauses
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.