DISTINCT predicate

[ajetrumpet]

All,

For some reason, I cannot seem to get a result set with unique records using the DISTINCTROW predicate in Access 2007. However, if I simply use the DISTINCT word (even if I am querying more than one field in a table), the result set gives unique records. This seems backwards....it's certainly not right. I know that the DISTINCTROW predicate does what it's supposed to do in earlier versions of the program, because I use them as well. Does anyone have any ideas on this??

Has anyone else experienced this?? Thanks!!

 

[EMP]

I don't have Access 2007. In all the versions of Access that I have, it seems Distinct filters query results whereas DistinctRow filters records in linked tables. I needed DistinctRow only in delete queries that were joined with another table e.g.

DELETE DISTINCTROW Table1.*
FROM Table1 INNER JOIN Table2 ON Table1.FldA = Table2.FldA;

^

 

[ajetrumpet]

In all the versions of Access that I have, it seems Distinct filters query results whereas DistinctRow filters records in linked tables.

You would think this should happen. I just tried to filter records using DISTINCTROW on an INNER JOIN and it still returned duplicate records. I also tried selecting all fields in both a linked and stand-alone table. I get the same result there, it's BACKWARDS!! I know this doesn't happen in 2003.

I wish I could get someone else to say that they have run into this problem....I wonder what it is....maybe DISTINCTROW no longer has a purpose?? I don't know though, because it's still listed in the HELP menu...

 

[EMP]

I wish I could get someone else to say that they have run into this problem...

It may take a while because not many people have Access 2007 and less still who have used DistinctRow in it.


Quoted from Access 2003 Help:
-------------------------------------------------
The following SQL statement shows how you can use DISTINCTROW to produce a list of companies that have at least one order but without any details about those orders:

SELECT DISTINCTROW CompanyName
FROM Customers INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
ORDER BY CompanyName;

If you omit DISTINCTROW, this query produces multiple rows for each company that has more than one order.

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.
-------------------------------------------------

In the meantime, you may try the above query on Northwind in Access 2007. If it returns duplicates, then it shows that DistinctRow in Access 2007 no longer works like its predecessors.

^

 

[ajetrumpet]

This is not an accident, it happens by default. Take a look at the last few lines of each BOLD section in the attached help article from the 2007 help menu. I didn't know this, until I looked it up!!

 

[Jon K]

The A2007 help in the attached doc is the same as my A2003 help
(except for the spelling of one word: updateable vs updatable.)

It's odd -- both words are displayed as correct in Word 2003 but both are underlined in red when typing this message in Firefox.
.

 

[ajetrumpet]

Do you like Firefox Jon??

I am running Vista now and I have talked to some people that run an Exchange Server I use, and they claim that I can't view the body text box of a new message because I am using Vista. If that makes sense, I'll give someone a million dollars!! I tell them that the problem is on their end. I use two different MS exchange servers, and the other one I use has no problem displaying the message box with IE7. I don't really see the connection there, but I can get it to display with Firefox!!

What I find frustrating about the DISTINCTROW word is that I have used it in 2003 and it actually applied the filter to entire rows (as it should), and now in 2007 it does not, no matter what situation I am in (joins, stand-alone tables, etc...)

 

[Jon K]

I have both IE6 and Firefox on my system, which is rather old. I have installed the IE Tab extension (add-on?) in FF for going to M$ sites or sites that specifically require IE. Though FF has become my favourite because of its speed and tab windows, I still have to keep IE6 because it's part of M$ Windows and because IE Tab in FF still needs the IE core.

As for DISTINCTROW in Access, like EMP, I used it only in delete queries that were joined with other tables. To retrieve unique lists in select queries, I only use DISTINCT. In fact, when I tried the query in the help, it returned the same results whether I used DISTINCT or DISTINCTROW.
.

 

[wazz]

i didn't read this in detail but this seems important:

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.

just thought i'd highlight it. no need to reply if it's irrelevant.

 

[ajetrumpet]

Jon,

There are still some problems with IE7 (I beleve), but I upgraded to it with minimal fuss on a system that was running the same system that you have. Tab extension?? How much of an extension is it?? I don't think I even know what the max is without it. Forgive me, but I think Microsoft didn't do too bad with that new piece of software....

By the way WAZZ, that little snippet of yours IS relevant, because I experimented with those instructions and they are not right!! For all of my experiments, DISTINCTROW was ignored no matter what I did. How much have you looked at the help menu?? I am surprised with the amount of misspelled words you can find in there!!

I wonder if Gates wrote those....??