asked on

How to show unique records from a specific column in Access 2007 query?

Hi all,

I'm trying to create a query in Microsoft Access which has about 4 columns. I'm trying to show only those records that are unique for a specific column, but I'm having trouble doing so.

My query has a column with unique ID Numbers along with unique codes. The ID numbers do not duplicate, but the codes do- so I'm basically trying to generate a query that shows me all the possible unique codes. Can anyone help me?

Anil

If you are using SQL to create your query: disticnt is your friend.

Select distinct column from table

A>

Anil

http://www.databasedev.co.uk/eliminate_duplicates.html

A>

Anthony

ASKER

I understand the Distinct statement, but it doesn't work for because I have multiple columns in my query. Attached is my code.

I'm trying to find distinct values for MBFUND.

SELECT DISTINCT BILMAC_MBRMSTR.MBFUND, BILMAC_MBRMSTR.MBDEPT, BILMAC_MBRMSTR.[MBSS#], BILMAC_MBRMSTR.MBSTUS, BILMAC_MBRMSTR.MBSTOR
FROM BILMAC_MBRMSTR
WHERE (((BILMAC_MBRMSTR.MBDEPT)=3) AND ((BILMAC_MBRMSTR.MBSTUS)="A"));

Open in new window

SELECT DISTINCT BILMAC_MBRMSTR.MBFUND, BILMAC_MBRMSTR.MBDEPT, BILMAC_MBRMSTR.[MBSS#], BILMAC_MBRMSTR.MBSTUS, BILMAC_MBRMSTR.MBSTOR
FROM BILMAC_MBRMSTR
WHERE (((BILMAC_MBRMSTR.MBDEPT)=3) AND ((BILMAC_MBRMSTR.MBSTUS)="A"));

Open in new window

ASKER CERTIFIED SOLUTION

mbizup

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Anil

Just to make sure that I understand your question.

If you want only the list of unique codes for that particular query

SELECT DISTINCT BILMAC_MBRMSTR.MBFUND FROM BILMAC_MBRMSTR
WHERE (((BILMAC_MBRMSTR.MBDEPT)=3) AND ((BILMAC_MBRMSTR.MBSTUS)="A"));

should do it or do you want to know all the unique combinations of

BILMAC_MBRMSTR.MBFUND, BILMAC_MBRMSTR.MBDEPT, BILMAC_MBRMSTR.[MBSS#], BILMAC_MBRMSTR.MBSTUS, BILMAC_MBRMSTR.MBSTOR

that is remove duplicate rows?

A>

Anthony

ASKER

A>

I don't want to know all the unique combinations. I just want to know how many unique values sit in the MBFUND column given the criteria I specified...

-Anthony

Anthony

ASKER

MB,

When I try your code, I get an error- "Wrong number of arguments used with function in query expression 'Max(BILMAC_MBRMSTR.MBSTUS,BILMAC_MBRMSTR.MBSTOR'.

Anthony

ASKER

MB I got it.

I needed another Max statement. Worked like a charm.

Thanks!

mbizup

Sorry about that... Try this:

SELECT MBFUND, Max(MBDEPT), Max(MBSS#]), Max(MBSTUS), Max(MBSTOR)
FROM BILMAC_MBRMSTR
WHERE (((BILMAC_MBRMSTR.MBDEPT)=3) AND ((BILMAC_MBRMSTR.MBSTUS)="A"))
GROUP BY MBFUND

Open in new window

mbizup

<<I got it.>>
Excellent!

Just FYI, you can clean your queries up quite a bit by dropping the prefixes in simple queries like this one. Those prefixes are only needed in JOIN clauses and if you need to distinguish between fields with the same names in different tables. No difference in functionality - just easier to read.

Anthony

ASKER

Thanks for the tip mb, I'll do that going forward...

-Anthony