showing single values- cant remember how

[CALV]

Hi all,

Im having a major brain lapse here! This is what I need to do:

table consists of

ID - Autonumber
UID - number
other- text
several other text values

Now the UID field will contain duplicate values, if this is the case, I need to list only the FIRST one of them, i.e.

if the table contained:

ID - 1
UID- 5
other - some text

ID - 2
UID -6
other - some text

ID - 3
UID 5
other some more text

What I need to do, is have the query display only ID's 1 and 2 because ID 3 record has the same UID (5) as record 1

Hope that makes sense and hope someone can help!!

TIA

CALV

UID

 

[Exodus]

Have you tried grouping on the UID?

 

[CALV]

Have you tried grouping on the UID?

Yes I tried putting GROUP BY but then it applies to them all and doesnt make any difference, or am I missing something here?

Thanks

CALV

 

[Exodus]

What information do need from the Querry?

 

[CALV]

What information do need from the Querry?

well really I need quite a bit, I need the UID, ID and several other text fields, but no duplicates on the UID, so it just lists the one if there are more than one with the same UID.

Thanks

 

[Exodus]

Are you running reports from this querry? If So you can do the grouping there.

For the Querry.
Do you need the text for the duplicates?
Because there are probably differrent values for the text field the grouping won't help.
Try putting UID field first when grouping. Access groups in field order.
Or you can try a crosstab querry not sure if it will suite your needs though.

Can you post your DB I can try working with it.

 

[CALV]

Are you running reports from this querry? If So you can do the grouping there.

For the Querry.
Do you need the text for the duplicates?
Because there are probably differrent values for the text field the grouping won't help.
Try putting UID field first when grouping. Access groups in field order.
Or you can try a crosstab querry not sure if it will suite your needs though.

Can you post your DB I can try working with it.

Many thanks,

there is actually nothing to the db at the moment, what I need to do is filter at the query stage as this data will be used later on (actually it will be accessed from an ASP page) I cant post the actual db as its at work but I have put together a small one with the problem. What I need it to display is records 1,2,3, 4, and 7 as the others have duplicate UID's

Thanks again

CALV

 

[Mike375]

What about making a copy of your table, delete the records and set the UID field for no duplicates. Then use that table for a report or form and everytime the form or report is opened a delete and append query is run to the copy of your table.

 

[CALV]

What about making a copy of your table, delete the records and set the UID field for no duplicates. Then use that table for a report or form and everytime the form or report is opened a delete and append query is run to the copy of your table.

Hi,

The problem is, it NEEDS the duplicates, though they also need to be filtered out at times.

Thanks

CALV

 

[Exodus]

Hey Calv,

Played around a bit check out with what I came up with.
Check out the problem querry. It uses a crosstab to exclude the dups.
You may have to play with the crosstab so it doesn't look like a pile of junk.

Other than this I don't know what to tell ya. Hope it works for you. I'll keep playing though.

 

[Jon K]

In the Totals query, you can Group By UID and select First for the rest of the fields.

 

[Exodus]

Thats a lot easier than what I did I'll have to remember that one.

 

[CALV]

Thats brilliant, thanks very much indeed.

CALV