Distinct rows

Hi,

I'm a bit new to databases, hope someone can help.

I have a query which joins two tables showing applications (defined by a
reference number) that have had mail merge letters sent. I want to modify
this query as some applications have had more than one mail merge letter sent
to more than one recipient (Consultee field).

The two tables are joined on year, ref and district. I would like year, ref
and district to be DISTINCT whilst still displaying the rest of the fields on
the DISTINCT row.

To elaborate, the output from the query is currently: -

Year Ref District Consultee DecType App_Type Cat
2006 43 S EA APST PN 3
2006 44 S AP APRM RMM 1
2006 44 S FS APRM RMM 2
2006 44 S ST APRM RMM 2
2006 44 S AP APRM RMM 5
2006 44 S BW APRM RMM 6
2006 44 S GU APRM RMM 7
2006 45 S NULL APPM FULL 8

I would like the output to list all of the fields but only one row for each
application i.e.

Year Ref District Consultee DecType App_Type Cat
2006 43 S EA APST PN 3
2006 44 S AP APRM RMM 1
2006 45 S NULL APPM FULL 8

There are other fileds in the tables but I have removed them for ease.

Thanks in advance,
Murdoc.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/200908/1

Probably all you need to do is to add the DISTINCT key word to your query.

Since you are new, you are probably not working in the SQL view.

Open your query in Design view
Right click on the gray area that is displaying the tables (not on one of the
tables) and show properties.
In the QUERY's property list set Unique Values property to Yes

Or open the query in SQL view (View; SQL from the menu)

Insert the word DISTINCT after the word SELECT

SELECT Distinct YourFieldA, YourFieldB, ....

If that does not work, copy the SQL statement and post it so someone can
suggest alternatives.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

MurdocUK via AccessMonster.com wrote:
Hi,

I'm a bit new to databases, hope someone can help.

I have a query which joins two tables showing applications (defined by a
reference number) that have had mail merge letters sent. I want to modify
this query as some applications have had more than one mail merge letter sent
to more than one recipient (Consultee field).

The two tables are joined on year, ref and district. I would like year, ref
and district to be DISTINCT whilst still displaying the rest of the fields on
the DISTINCT row.

To elaborate, the output from the query is currently: -

Year Ref District Consultee DecType App_Type Cat
2006 43 S EA APST PN 3
2006 44 S AP APRM RMM 1
2006 44 S FS APRM RMM 2
2006 44 S ST APRM RMM 2
2006 44 S AP APRM RMM 5
2006 44 S BW APRM RMM 6
2006 44 S GU APRM RMM 7
2006 45 S NULL APPM FULL 8

I would like the output to list all of the fields but only one row for each
application i.e.

Year Ref District Consultee DecType App_Type Cat
2006 43 S EA APST PN 3
2006 44 S AP APRM RMM 1
2006 45 S NULL APPM FULL 8

There are other fileds in the tables but I have removed them for ease.

Thanks in advance,
Murdoc.

In looking at the data you posted a totals query might do you.
Open your query in design view and click on the icon that looks like an 'M'
on its side - ∑ to change to a totals query.
Then change the Group By for all fields except Year, Ref, and District to
First.
Run the query and see if the results are what you want.

--
Build a little, test a little.


"MurdocUK via AccessMonster.com" wrote:

Hi,

I'm a bit new to databases, hope someone can help.

I have a query which joins two tables showing applications (defined by a
reference number) that have had mail merge letters sent. I want to modify
this query as some applications have had more than one mail merge letter sent
to more than one recipient (Consultee field).

The two tables are joined on year, ref and district. I would like year, ref
and district to be DISTINCT whilst still displaying the rest of the fields on
the DISTINCT row.

To elaborate, the output from the query is currently: -

Year Ref District Consultee DecType App_Type Cat
2006 43 S EA APST PN 3
2006 44 S AP APRM RMM 1
2006 44 S FS APRM RMM 2
2006 44 S ST APRM RMM 2
2006 44 S AP APRM RMM 5
2006 44 S BW APRM RMM 6
2006 44 S GU APRM RMM 7
2006 45 S NULL APPM FULL 8

I would like the output to list all of the fields but only one row for each
application i.e.

Year Ref District Consultee DecType App_Type Cat
2006 43 S EA APST PN 3
2006 44 S AP APRM RMM 1
2006 45 S NULL APPM FULL 8

There are other fileds in the tables but I have removed them for ease.

Thanks in advance,
Murdoc.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/200908/1