How to make a query not show repeats

[sirantonycartwr]

Hello, I have a system where a person may have several posts.

Ive made a query which shows everyone who has a post.

The problem is that if a person has three posts, the record shows three times, how do i make tell a query to do this:

Get all records, look at the name field, show only 1 record per name.

How do i do it? Whats this called? Where do i find out more?

Ant...

 

[ColinEssex]

use the GroupBy function in the query.

Col

 

[sirantonycartwr]

it doesnt work, ive already tried. says something about aggregate function or something.

 

[rainman89]

you cannot use unique identifiers ( such as dates of posts etc) in your query. your query should just contain the name and userid

 

[neileg]

OK

Depends what you want. If you only want to have the name, then as rainman says, just select the name field. Change the query properties to Unique Values.

Alternatively, use an aggregate query. Click the sigma (backwards E) button. You need to choose an aggregate function that will return the particular record that you want. If you're not fussy, choose First or Last. Or, decide on a field that allows you to choose the latest record (a date or an ID number perhaps) and use Max.

 

[sirantonycartwr]

aggregate query is still showing repeating data.

Attached is the query (pic) i want a member to be displayed once. (if they have a post then display them)



Ant...

 

[neileg]

PersonID as first column in the query set to GroupBy, Role as the second column set to First

 

[boblarson]

aggregate query is still showing repeating data.

Attached is the query (pic) i want a member to be displayed once. (if they have a post then display them)

View attachment 17537

Ant...

The reason that the person is showing up more than once is that, according to your data, they are assigned more than one role, thus they will show up more than once. If you only want them one time then remove the role from the query and then group and they will only show up once.

 

[sirantonycartwr]

i need to have role and person in the query because thats what makes the query useful.

neileg - Spot on, thats what i want. Thanks...

Please explain though!
How did you know that?
Why is it working that way?
what does first do?

 

[neileg]

In an aggregate query, Access works from left to right in applying the constraints. Since you want to report by person, you make that the first column and group on that. Everything else is then aggregated by person. You said you only want one role returned, so you chose a function that returns one value. I could have used Last, First, Max or Min. Exactly which record that returns differs. First and Last return the first or last value in the dataset. This will be different depending on how the data is sorted. Max and Min apply their own sort before returning their value, so this is not dependant on the sort order.

I suggest you have a look in Access help for more information.

 

[sirantonycartwr]

Oooo, I didnt know that. I can think of lots of uses for that facility. Thanks for sparing your time to help guys.

 

[boblarson]

Also, one for my mental filing system. Thanks Neil!