Potential Duplicates

T

Tony L

I have confusing question.

I have 5 fields, how would I go about running a query or 3 of the fields,
but show all 5 in the result? I want to query the distinct values of only
selected fields, but show all of the fields in the results.

Thanks for any assistance in advanced,

Tony
 
K

KARL DEWEY

I want to query the distinct values of only selected fields, but show all
of the fields in the results.

Post some sample data to show what you mean.
 
T

Tony L

Column A, B, C, D, E

I want to do a distinct select on columns A, B and D but when i get the
results, I want to see the information for cloumns A-E.
 
J

John Spencer

Well, you can't see all the data for columns C and E if they are different
and still have a distinct record based on A, B, and D. If you are willing
to accept a more or less random choice of the values for C and E you could
use an aggregate (totals) query.

SELECT A, B, First(C) as Fc, D, First(E) as Fe
FROM YourTable
GROUP BY A, B, D



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

KARL DEWEY

But if column E has several different data then A, B, C, D will not be
distinct.
Example --
22 33 44 55 2
22 33 44 55 11
22 33 44 55 21
22 33 44 55 15
 
T

Tony L

Why am I getting the following error? You tried to execute a query tht does
not include the specific expression 'NAME/ACCOUNT' as part of an aggregate
function.

Here's the SQL

SELECT Test.[NAME/ ACCOUNT], Test.ADDRESS, Test.CITY, Test.ZIP,
Test.PHONE,First( Test.DESCRIPTION), First(Test.SERIAL), First(Test.MODEL),
First(Test.[INSTALL DATE])
FROM Test
Group By [Name/Account], Description, Serial, Model
 
K

KARL DEWEY

Maybe it is a typo in the post or the SQL but I see a space after the slash
in the SELECT statement but not in the Group BY.
--
KARL DEWEY
Build a little - Test a little


Tony L said:
Why am I getting the following error? You tried to execute a query tht does
not include the specific expression 'NAME/ACCOUNT' as part of an aggregate
function.

Here's the SQL

SELECT Test.[NAME/ ACCOUNT], Test.ADDRESS, Test.CITY, Test.ZIP,
Test.PHONE,First( Test.DESCRIPTION), First(Test.SERIAL), First(Test.MODEL),
First(Test.[INSTALL DATE])
FROM Test
Group By [Name/Account], Description, Serial, Model


John Spencer said:
Well, you can't see all the data for columns C and E if they are different
and still have a distinct record based on A, B, and D. If you are willing
to accept a more or less random choice of the values for C and E you could
use an aggregate (totals) query.

SELECT A, B, First(C) as Fc, D, First(E) as Fe
FROM YourTable
GROUP BY A, B, D



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

Tony L

I fixed that and now it says the same thing about another field.

KARL DEWEY said:
Maybe it is a typo in the post or the SQL but I see a space after the slash
in the SELECT statement but not in the Group BY.
--
KARL DEWEY
Build a little - Test a little


Tony L said:
Why am I getting the following error? You tried to execute a query tht does
not include the specific expression 'NAME/ACCOUNT' as part of an aggregate
function.

Here's the SQL

SELECT Test.[NAME/ ACCOUNT], Test.ADDRESS, Test.CITY, Test.ZIP,
Test.PHONE,First( Test.DESCRIPTION), First(Test.SERIAL), First(Test.MODEL),
First(Test.[INSTALL DATE])
FROM Test
Group By [Name/Account], Description, Serial, Model


John Spencer said:
Well, you can't see all the data for columns C and E if they are different
and still have a distinct record based on A, B, and D. If you are willing
to accept a more or less random choice of the values for C and E you could
use an aggregate (totals) query.

SELECT A, B, First(C) as Fc, D, First(E) as Fe
FROM YourTable
GROUP BY A, B, D



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

Column A, B, C, D, E

I want to do a distinct select on columns A, B and D but when i get the
results, I want to see the information for cloumns A-E.

:

I want to query the distinct values of only selected fields, but show
all
of the fields in the results.

Post some sample data to show what you mean.

--
KARL DEWEY
Build a little - Test a little


:

I have confusing question.

I have 5 fields, how would I go about running a query or 3 of the
fields,
but show all 5 in the result? I want to query the distinct values of
only
selected fields, but show all of the fields in the results.

Thanks for any assistance in advanced,

Tony
Click to expand...
Click to expand...
Click to expand...
 
K

KARL DEWEY

Try adding --- Test.ADDRESS, Test.CITY, Test.ZIP, Test.PHONE,
to your GROUP BY statement.
--
KARL DEWEY
Build a little - Test a little


Tony L said:
I fixed that and now it says the same thing about another field.

KARL DEWEY said:
Maybe it is a typo in the post or the SQL but I see a space after the slash
in the SELECT statement but not in the Group BY.
--
KARL DEWEY
Build a little - Test a little


Tony L said:
Why am I getting the following error? You tried to execute a query tht does
not include the specific expression 'NAME/ACCOUNT' as part of an aggregate
function.

Here's the SQL

SELECT Test.[NAME/ ACCOUNT], Test.ADDRESS, Test.CITY, Test.ZIP,
Test.PHONE,First( Test.DESCRIPTION), First(Test.SERIAL), First(Test.MODEL),
First(Test.[INSTALL DATE])
FROM Test
Group By [Name/Account], Description, Serial, Model


:

Well, you can't see all the data for columns C and E if they are different
and still have a distinct record based on A, B, and D. If you are willing
to accept a more or less random choice of the values for C and E you could
use an aggregate (totals) query.

SELECT A, B, First(C) as Fc, D, First(E) as Fe
FROM YourTable
GROUP BY A, B, D



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

Column A, B, C, D, E

I want to do a distinct select on columns A, B and D but when i get the
results, I want to see the information for cloumns A-E.

:

I want to query the distinct values of only selected fields, but show
all
of the fields in the results.

Post some sample data to show what you mean.

--
KARL DEWEY
Build a little - Test a little


:

I have confusing question.

I have 5 fields, how would I go about running a query or 3 of the
fields,
but show all 5 in the result? I want to query the distinct values of
only
selected fields, but show all of the fields in the results.

Thanks for any assistance in advanced,

Tony
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

SELECT Test.[NAME/ ACCOUNT], Test.ADDRESS, Test.CITY, Test.ZIP,
Test.PHONE,First( Test.DESCRIPTION), First(Test.SERIAL), First(Test.MODEL),
First(Test.[INSTALL DATE])
FROM Test
Group By [Name/Account], Description, Serial, Model

Is the field name Name/Account or is it Name/ space Account? One of the
other is wrong.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Tony L said:
Why am I getting the following error? You tried to execute a query tht
does
not include the specific expression 'NAME/ACCOUNT' as part of an aggregate
function.

Here's the SQL

SELECT Test.[NAME/ ACCOUNT], Test.ADDRESS, Test.CITY, Test.ZIP,
Test.PHONE,First( Test.DESCRIPTION), First(Test.SERIAL),
First(Test.MODEL),
First(Test.[INSTALL DATE])
FROM Test
Group By [Name/Account], Description, Serial, Model


John Spencer said:
Well, you can't see all the data for columns C and E if they are
different
and still have a distinct record based on A, B, and D. If you are
willing
to accept a more or less random choice of the values for C and E you
could
use an aggregate (totals) query.

SELECT A, B, First(C) as Fc, D, First(E) as Fe
FROM YourTable
GROUP BY A, B, D



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

John Spencer

Whoops. I missed something else. You need to have Addres, City, Zip, And
PHONE in the GROUP BY clause also.

ANd you might want to use Min(Test.[Install Date]) to get the earliest
installation date Or Max() to get the latest install date.

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

Tony L said:
Why am I getting the following error? You tried to execute a query tht
does
not include the specific expression 'NAME/ACCOUNT' as part of an aggregate
function.

Here's the SQL

SELECT Test.[NAME/ ACCOUNT], Test.ADDRESS, Test.CITY, Test.ZIP,
Test.PHONE,First( Test.DESCRIPTION), First(Test.SERIAL),
First(Test.MODEL),
First(Test.[INSTALL DATE])
FROM Test
Group By [Name/Account], Description, Serial, Model


John Spencer said:
Well, you can't see all the data for columns C and E if they are
different
and still have a distinct record based on A, B, and D. If you are
willing
to accept a more or less random choice of the values for C and E you
could
use an aggregate (totals) query.

SELECT A, B, First(C) as Fc, D, First(E) as Fe
FROM YourTable
GROUP BY A, B, D



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