Solved

Oh, DISTINCTROW, again.

Posted on 2013-06-23
23
364 Views
Last Modified: 2013-06-29
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
0
Comment
Question by:midfde
  • 11
  • 6
  • 4
  • +1
23 Comments
 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 200 total points
ID: 39270081
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
 
LVL 1

Accepted Solution

by:
midfde earned 0 total points
ID: 39270134
>>...duplicate have been deleted...
Can you see any "duplicates" in my example please?
0
 
LVL 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 150 total points
ID: 39270565
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
 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 150 total points
ID: 39270596
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
 
LVL 1

Author Comment

by:midfde
ID: 39271002
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
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 39271046
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
 
LVL 1

Author Comment

by:midfde
ID: 39271097
>>... 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
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39271134
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
 
LVL 77

Expert Comment

by:peter57r
ID: 39271176
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
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 39271180
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
 
LVL 1

Author Comment

by:midfde
ID: 39271197
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 1

Author Comment

by:midfde
ID: 39271272
To: /gustav
"Desired" is a correct one, i.e. the one that returns all 6 apparently distinct rows.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39271332
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
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 39271383
> "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
 
LVL 1

Author Comment

by:midfde
ID: 39271486
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
 
LVL 1

Author Comment

by:midfde
ID: 39271582
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
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39271583
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
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39271639
@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
 
LVL 1

Author Comment

by:midfde
ID: 39271679
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
 
LVL 1

Author Comment

by:midfde
ID: 39271709
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
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39271727
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
 
LVL 1

Author Comment

by:midfde
ID: 39271755
DISTINCT returns 6 rows as expected.
0
 
LVL 1

Author Closing Comment

by:midfde
ID: 39286397
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

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Lync meeting or Lync conferencing is what many organizations would like to deploy to allow them save money. But companies are now giving up for various reasons, one of which is that they cannot join external meetings (non-federated company meetings)…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now