Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 240
  • Last Modified:

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?
0
Anthony6890
Asked:
Anthony6890
  • 5
  • 3
  • 3
1 Solution
 
akajohnCommented:
If you are using SQL to create your query: disticnt is your friend.

Select distinct column from table


A>
0
 
Anthony6890Author Commented:
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

0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
mbizupCommented:
< but it doesn't work for because I have multiple columns in my query>

If you want all columns displayed, with a distinct MBFUND, you need a 'totals' query, and you need to decide how to handle the other three fields.

eg:

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

Open in new window


Will give you a disinct BILMAC_MBRMSTR.MBFUND, and Max values of the other fields
0
 
akajohnCommented:
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>
0
 
Anthony6890Author Commented:
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
0
 
Anthony6890Author Commented:
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'.
0
 
Anthony6890Author Commented:
MB I got it.

I needed another Max statement.  Worked like a charm.

Thanks!
0
 
mbizupCommented:
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

0
 
mbizupCommented:
<<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.
0
 
Anthony6890Author Commented:
Thanks for the tip mb, I'll do that going forward...

-Anthony
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now