How to prevent duplicates

[Hell-fire]

Hi,

Probably something very simple, but it eludes me.

I have query that sorts via field named [Reportorder] and [IDs], it has 15
other fields, but the first two I mentioned does the sorting.

When I do my report, it will first sort via [Reportorder] then by [IDs].
The problem is I don't want duplicates of any [IDs] to show up, just the
first [IDs]. Each report has its unique identifier which is field name
[Identifier].

At the moment when I do my query it will look like this:

Reportorder IDs
2 I0070
2 I0070
3 I0056
3 I0054

I don't need the second I0070 to show up. Is this possible? probably is so
simple, but I'm not experienced with making queries. So if you can help me,
please do and remember I'm still a newbie. Thank you.

 

[Guest]

You can do this two ways.

Open your query and in the querymenu select "SQL" (most left button, arrow).
After the word Select type Distinct. Save and run the query. You should see
one value for every ID (as long as all fieldvalues are identical).

Second way: In your report. Put the report in designview. Choose properties
and set the option "show duplicates" to no.

hth

 

[tina]

you can't do it at the query level, but you can hide duplicate values at the
*report* level. open the report in Design view, click on the control that
you want hidden when the value is duplicate, and open the Properties box.
change the HideDuplicates property to Yes.

hth

 

[Guest]

Thanks Tina,

Unfortunately, it won't work for what I am doing. I have a word document
template that uses mail merge and I can only link it to the tables or
queries, not to reports. The report output is in RTF, that is why I'm using
the mail merge in Word, so the data is inputted into a table. Thanks again
for responding to my post.

you can't do it at the query level, but you can hide duplicate values at the
*report* level. open the report in Design view, click on the control that
you want hidden when the value is duplicate, and open the Properties box.
change the HideDuplicates property to Yes.

hth

Hi,

Probably something very simple, but it eludes me.

I have query that sorts via field named [Reportorder] and [IDs], it has 15
other fields, but the first two I mentioned does the sorting.

When I do my report, it will first sort via [Reportorder] then by [IDs].
The problem is I don't want duplicates of any [IDs] to show up, just the
first [IDs]. Each report has its unique identifier which is field name
[Identifier].

At the moment when I do my query it will look like this:

Reportorder IDs
2 I0070
2 I0070
3 I0056
3 I0054

I don't need the second I0070 to show up. Is this possible? probably is so
simple, but I'm not experienced with making queries. So if you can help me,
please do and remember I'm still a newbie. Thank you.

 

[Guest]

Hi Maurice,

Thank you for responding to my post. Unfortunately your suggestion didn't
work. Thank you for trying to help.

I know I can do it in report section, but I use Word mail merger to pull the
data from the query into a word template. Thank you again.

You can do this two ways.

Open your query and in the querymenu select "SQL" (most left button, arrow).
After the word Select type Distinct. Save and run the query. You should see
one value for every ID (as long as all fieldvalues are identical).

Second way: In your report. Put the report in designview. Choose properties
and set the option "show duplicates" to no.

hth
--
Maurice Ausum

Hi,

Probably something very simple, but it eludes me.

I have query that sorts via field named [Reportorder] and [IDs], it has 15
other fields, but the first two I mentioned does the sorting.

When I do my report, it will first sort via [Reportorder] then by [IDs].
The problem is I don't want duplicates of any [IDs] to show up, just the
first [IDs]. Each report has its unique identifier which is field name
[Identifier].

At the moment when I do my query it will look like this:

Reportorder IDs
2 I0070
2 I0070
3 I0056
3 I0054

I don't need the second I0070 to show up. Is this possible? probably is so
simple, but I'm not experienced with making queries. So if you can help me,
please do and remember I'm still a newbie. Thank you.

 

[John Spencer]

Try a totals or aggregate query.

SELECT IDs, First(TableName.ReportOrder) as ReportOrder
, First(TableName.Feild3) as Field3, First(TableName.Field4) as Field4
FROM TableName
GROUP BY IDs

In design view (query grid)
-- Select all the fields you want in your query
-- Select View: Totals from the menu
-- Change Group By to FIRST under all the fields except IDs
-- If you want to retain the same field names, you can probably do that by
entering
FieldName3: FieldName in the field.

That will query will return one record for every IDs value and the
associated fields from one record that has that specific IDs value.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..