 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / November 2007Select distinct does not group rows
The query below is expected to return 'distinct' fields from a text field. Does distinct work within MS Access 2003 databases? Any ideas? SQL SELECT DISTINCT [Sales by Customer (Cube)].CompanyName,[Sales by Customer (Cube)].Order_Year,[Sales by Customer (Cube)].Extended_Amount FROM [Sales by Customer (Cube)] The query that you posted will provide the unique combinations of the three fields: CompanyName, Order_Year, and Extended_Amount. What are you expecting? What are you seeing? Show us some samples from the data.  SignatureKen Snell <MS ACCESS MVP> > The query below is expected to return 'distinct' fields from a text field. > [quoted text clipped - 5 lines] > by > Customer (Cube)] That should work. Distinct should return one row for each combination of CompanyName, Order_Year, and Extended_Amount. Can you post an example of two or three records that you think are duplicates? The only thing I can think of is that Extended_Amount may LOOK unique due to formatting that is being applied, but may not be unique due to the fact that Extended_Amount is a number field of type double (floating point number) and has been calculated. That can cause very small differences in the value that is in the field. You might try forcing the number to currency type. See if this query returns "duplicates" SELECT DISTINCT [Sales by Customer (Cube)].CompanyName , [Sales by Customer (Cube)].Order_Year , CCUR(NZ([Sales by Customer (Cube)].Extended_Amount ,0)) FROM [Sales by Customer (Cube)] SignatureJohn Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County . > The query below is expected to return 'distinct' fields from a text field. > [quoted text clipped - 5 lines] > by > Customer (Cube)] Given the SQL below I am seeing the Company name more than once SQL SELECT Distinct Customers.CompanyName, Orders.OrderDate, YEAR(Orders.OrderDate) AS Order_Year, Day(Orders.OrderDate) AS Order_Day, Month(Orders.OrderDate) AS Order_Month, Customers.City, Customers.Region, Customers.PostalCode, Customers.Country, [Order Details].UnitPrice, [Order Details].Quantity, [Order Details].Discount, [Order Details].UnitPrice*[Order Details].Quantity*(1-[Order Details].Discount) AS Extended_Amount FROM (Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID) INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID; Data Company Name Order Date Order_Year Order_Day Order_Month City Region Postal Code Country Alfreds Futterkiste 25-Aug-1997 1997 25 8 Berlin 12209 Germany Alfreds Futterkiste 25-Aug-1997 1997 25 8 Berlin 12209 Germany Alfreds Futterkiste 25-Aug-1997 1997 25 8 Berlin 12209 Germany Alfreds Futterkiste 03-Oct-1997 1997 3 10 Berlin 12209 Germany Alfreds Futterkiste 13-Oct-1997 1997 13 10 Berlin 12209 Germany > The query below is expected to return 'distinct' fields from a text field. > [quoted text clipped - 4 lines] > (Cube)].Order_Year,[Sales by Customer (Cube)].Extended_Amount FROM [Sales by > Customer (Cube)] You didn't pated all the SELECTED fields. If you do, you will see that each ROW of the result is, indeed, "different" (watch the last computed expression, Extended_Amount)... The DISTINCT is applicable on ALL the SELECTED fields of the RESULT, and only if all the selected fields are equal, only one row (of the two ones equal) would be kept. Vanderghast, Access MVP > Given the SQL below I am seeing the Company name more than once > [quoted text clipped - 31 lines] >> by >> Customer (Cube)] Yes you may be but you are not looking at ALL the fields that you are returning in the SELECT clause. What about UnitPrice, Discount, Quantity, etc,? Are all those the same also? I would guess not. If you drop all the Orders and Order Details fields from the query's SELECT clause I would venture to say that you would see Alfred Futterkiste listed only once SignatureJohn Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County . > Given the SQL below I am seeing the Company name more than once > [quoted text clipped - 31 lines] >> by >> Customer (Cube)] If you want a list of unique CustomerNames from a SELECT DISTINCT query, then CustomerName should be the only SELECTED field, unless you apply an aggregate function (First, Last, Max, Min, Sum, Count, Average, etc.) to additional fields. SignatureHTH, George > Given the SQL below I am seeing the Company name more than once > [quoted text clipped - 31 lines] >> by >> Customer (Cube)] Is it possible to do the following: 1. Create a function that returns the same field name i.e. so that SQL thinks it is an aggregate function. Does MS Access provide this functionality? 2. SQL Statement that reads something like: SELECT DISTINCT CustomerName, myFunction(field1), myFunction(field2) FROM table [GROUP BY CustomerName] > If you want a list of unique CustomerNames from a SELECT DISTINCT query, > then CustomerName should be the only SELECTED field, unless you apply an [quoted text clipped - 36 lines] > >> by > >> Customer (Cube)] I am not sure what you are asking here. SELECT CustomerName, myFunction(field1), myFunction(field2) FROM table GROUP BY [CustomerName], myFunction(field1), myFunction(field2) In an aggregate (totals) query you MUST either GROUP BY each field in the SELECT clause or use on of the aggregate functions (First, Last, Sum, Avg, etc.) on the field (column) in the SELECT clause. Perhaps what you want is to use the FIRST function to return the value of the field. First returns the value from the first record it finds in the group when it is grouping. The first record found is not necessarily the first record entered or the first record in some sort order - it is best to think of First (and Last) as just giving you a random value from the grouped records. One good thing about First and Last is that for multiple fields in a group you will get back values from the same record in the group. So perhaps what you want is SELECT CustomerName, First(field1), First(field2) FROM table GROUP BY [CustomerName] SignatureJohn Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County . > Is it possible to do the following: > [quoted text clipped - 53 lines] >> >> by >> >> Customer (Cube)] |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.