Pain in my Access! How to get unique values!!!!

[Guest]

Here is the problem.

1. I am trying to write a query that will display unique values for "Field
1". HOWEVER, I already tried using the built-in limiter (in the properties
page, in the query). Essentially, I am trying to find the first instance of
"Field1". This seems to be a problem for Access, because it looks at the
other Fields and treats the different rows as unique from each other. But
what I want it to think of as unique is just "Field1".

2. This is how my table looks now (how I want it to look is displayed in #3)

The "PROBLEM" Database

Field1 Field2 Field3
03071 Company A 55
03071 Company B 55
03071 Company C 55
01000 Company X 55
01000 Company X 95

3. The "DESIRED" Database

Field1 Field2 Field3
03071 Company A 55
01000 Company X 55

 

[DebbieG]

How can you want to display

03071 Company A

when you have

03071 Company A
03071 Company B
03071 Company C

in your tables?

I don't see anything unique about Field1.

Guess you could try Top Values in the properties of the query.


| Here is the problem.
|
| 1. I am trying to write a query that will display unique values for "Field
| 1". HOWEVER, I already tried using the built-in limiter (in the properties
| page, in the query). Essentially, I am trying to find the first instance of
| "Field1". This seems to be a problem for Access, because it looks at the
| other Fields and treats the different rows as unique from each other. But
| what I want it to think of as unique is just "Field1".
|
| 2. This is how my table looks now (how I want it to look is displayed in #3)
|
| The "PROBLEM" Database
|
| Field1 Field2 Field3
| 03071 Company A 55
| 03071 Company B 55
| 03071 Company C 55
| 01000 Company X 55
| 01000 Company X 95
|
| 3. The "DESIRED" Database
|
| Field1 Field2 Field3
| 03071 Company A 55
| 01000 Company X 55
|
| --
| THANK YOU!!!
|
| accessbabe

 

[Sergey Poberezovskiy]

The easiest way is to use some code:

Private Sub insertUnique()
On Error GoTo sub_Err
Dim db As DAO.Database
Dim prevValue As String
Dim sSql As String
DoCmd.Hourglass True
Set db = CurrentDb
' clear the destination table
db.Execute "Delete From myDestTable", dbFailOnError
' order the fields in the source table
sSql = "Select Field1, Field2, Field3 From myTable
Order By 1, 2, 3"
With db.OpenRecordset(sSql, dbOpenForwardOnly)
Do While Not .EOF
' if new field1 differs from the previous,
insert the record
If Nz(.Fields(0), "") <> prevValue Then
sSql = "Insert Into myDestTable Values ('"
& .Fields(0) & "', '" & .Fields(1) & "', '" & .Fields(2)
& "')"
db.Execute sSql, dbFailOnError
End If
.MoveNext
Loop
.Close
End With
sub_Exit:
DoCmd.Hourglass False
Exit Sub
sub_Err:
MsgBox Err.Description
Resume sub_Exit
End Sub


This code uses DAO, but you should have no problems
converting it to ADO if needed. You may even wrap it up in
a Transaction. It is relatively hard to achieve the same
result in a(n Access) query, though not impossible.

HTH

 

[Albert D. Kallal]

The easy way here is to use two queries. I have to assume you have a
autonubmer id (or, at least some type of primary key).

So, just fire up the query builder. Drop in the field1, and drop in the
"ID" field. So a group by on the field1, and MIN(id) for the 2nd field.

This is just few mouse clicks in the query builder. The resulting query
will look like:

SELECT Field1, Min(id) AS MinOfid
FROM Dt
GROUP BY Field1
ORDER BY Min(id);

So, the above query will reutrn ONE field1, and also the first (uniqite) id
for the 2nd field. If we run the query, we get:

Query10
Field1 MinOfid
3071 1
1000 4

Now, save this (query10).

Now, just fire up the query builder again, and drop in the above query, and
then
drop in the table...draw a join line from minOfID to the idfield, and then
just drop in the fields you need....you are done....

The query builder will produce something like:

SELECT MinOfid, Dt.*
FROM Query10 INNER JOIN Dt ON Query10.MinOfid = Dt.id;

No code...or anything really funny here is needed. You do however need a
primary key for the table...but that is going to be a given here...