Thanks John,
Yes that solves my problem by giving an explicit example of the results.
I need 'Unique Values' for this application. It
is yet another where I am using SQL made by
Access and I apply it to my code generator which
produces the web pages. Update is not required.
Regards,
Robin Chapple
At 10/04/2012 03:31 PM, you wrote:
>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
>
>
>
>
>------------------------------------
>
>Yahoo! Groups Links
>
>
>
Tidak ada komentar:
Posting Komentar