Robin-
Unique Values gets you the DISTINCT keyword in the query. That will eliminate
all the records that match with another record on ALL the fields.
Unique Records gets you the DISTINCTROW keyword. That will eliminate all
records that have duplicate values in the Primary Key for all tables included in
the SELECT clause. In your case, it shouldn't eliminate any rows at all. That
is, you should get the same number of records with a plain SELECT as you get
with SELECT DISTINCTROW.
Note that a DISTINCT query is never updatable. DISTINCTROW queries are usually
updatable as long as all other rules for joins are followed.
DISTINCTROW was invented for Access V1 to compensate for the fact that
subqueries were not supported. Example:
"Show me all the customers who have placed an order in the month of October of
last year."
In ANSI-SQL you would do:
SELECT Customers.*
FROM Customers
WHERE Customers.CustomerID IN
(SELECT CustomerID FROM Orders WHERE Orders.OrderDate Between #10/1/2011# And
#10/31/2011#)
The above returns exactly one row for each customer regardless of the number of
orders placed.
To solve the problem with a join requires:
SELECT Customers.*
FROM Customers INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
WHERE Orders.OrderDate Between #10/1/2011# And #10/31/2011#
The SQL standard says that the query should return all the rows generated by the
FROM clause – one row for every customer and order in the month of October.
However, that would get you multiple rows per customer for any customer who
placed multiple orders in October. To get the same result as with using the
subquery, the DISTINCTROW keyword returns only the rows that are distinct based
on what you included in the SELECT clause. Thus,
SELECT DISTINCTROW Customers.*
FROM Customers INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
WHERE Orders.OrderDate Between #10/1/2011# And #10/31/2011#
.. returns exactly one row per customer, regardless of the number of orders
placed. The query is updatable, but would not be updatable if you used the
ANSI-Standard DISTINCT.
Does that help?
John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
-------------------------------------------------
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Robin Chapple
Sent: Monday, April 09, 2012 11:49 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Unique records and Unique Values
I have failed to find a definition for the difference between 'Unique
Records' and 'Unique Values' when used in a query Access 2007.
In the example that I am working on the table has 22,670 records. The
query provides 21,693 unique records or 22,601 unique values. The
requirement is to eliminate any record the is duplicated in every field.
The SQL is:
SELECT DISTINCT tClubPostHolders.ClubPostID,
tClubPostHolders.MemberID, tClubPostHolders.YearID,
tClubPostHolders.ClubID, tRotaryYear.YearStart
FROM tRotaryYear RIGHT JOIN tClubPostHolders ON tRotaryYear.YearID =
tClubPostHolders.YearID
WHERE (((tClubPostHolders.ClubPostID) Is Not Null) AND
((tClubPostHolders.MemberID) Is Not Null));
It is far beyond my limited ability.
Many thanks,
Robin Chapple
Tidak ada komentar:
Posting Komentar