Select distinct does not group rows

J

Jon

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)]
 
K

Ken Snell \(MVP\)

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

John Spencer

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)]


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
J

Jon

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
 
M

Michel Walsh

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



Jon said:
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

Jon said:
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)]
Click to expand...
Click to expand...
 
J

John Spencer

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

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Jon said:
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

Jon said:
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)]
Click to expand...
Click to expand...
 
G

George Nicholson

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.

--
HTH,
George


Jon said:
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

Jon said:
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)]
Click to expand...
Click to expand...
 
J

Jon

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]

George Nicholson said:
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.

--
HTH,
George


Jon said:
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

Jon said:
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)]
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

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]

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Jon said:
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]

George Nicholson said:
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.

--
HTH,
George


Jon said:
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.

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)]
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SELECT DISTINCT query 8
perform INNER JOIN on query results 2
distinct DCount - so close! or so close? 3
Syntax error in union query 2
Create a distinct Join 3
can't find the right query 0
Add custom field into OLAP cube 3
Grouping Distinct 3

Top