Solved: Selecting Unique Values Query

[akn112]

Hi all,

I have this query, which compares and gets matching values. What i want but the problem is that in the second table the same [Name] can be in a few entries. I just want it to pick the first entry for that specific name and then move on to the next. Basically, i just want to check if that name exists in the list and show it once. I tried the select top 1 statement instead of distinct but that just gave me 1 entry, whereas im looking for around 80. :help


SELECT DISTINCT

To.ID,

To.[Name],

Toronto.Fed,

BKR.OP,

To.[Date],

Bkr.Date2

FROM

Bkr, To

WHERE

(((To.[Name])=[OPER])

AND ((To.Fed) Like "*"+[OP])

AND ((To.[Date]) Like Mid([Date2],1,15)+"*"

AND (To.[Date]) Like "*"+Right([Date2],2))

AND ((Bkr.OPER) Is Not Null))

ORDER BY

To.[Name],

To.[Date];

[stanl]

I think a ... WHERE EXISTS ( subquery ) will return just one row. :dunno Stan

[akn112]

Hey Stanl, i tried the where exists but it returned with a syntax error. Im not familiar with the function though, so not sure why... =S

[OBP]

A copy of the database to "play with" would be nice, have you tried DistinctRow along with setting the Table relationships to All records from table one and only matching from table two ?

[akn112]

Hey everyone, sorry for the late reply. It took me a while to make a sanitized version. So this database shows table 1 compared against table 2. but when u run the query, there are some entries from table 1 that are matched with more than one entries from table 2. One example is entry 208/209 is the same entry from table 1 when u first run the query. What i want is for it to show only one of the matches, not all of them. Thanks,

[OBP]

Try this it, it uses 3 queries. It might be possible to use their SQL to do it with one, but my SQL is not that good.

[akn112]

Hey OPG. thanks for the quick reply. the way the code works is each "object" is uniquely identified by 1 and 2 columns. for example. Kapu 27M3(column 1:Kapu, column 2: 27M3) may have moved at 12:30PM. but if table 2 shows that Kapu 27M4 has moved at 12:30, than that would produce a false match within your query. Hope that makes sense. It's even weird trying to explain it.

But theres also another twist to it. Kapu 27M3 in table 1 might be shown as Kapu M3 in table 2. Thats why i had the *+right(...). Generally speaking though, if the last 2 letters of column 2(ie: M3 in this case) match and column 1 (ie: Kapu in this case) match, it is the same "object".

[OBP]

Try this version then.

[akn112]

Hey OBP, only one more thing (which is where i was getting the problems). Each specific time in table 1 must also be matched in table 2. So if 12:30:32PM exists in table 1, then it must exist as 12:30:..PM in table 2 where ".." can be anything. The problem is that, if table 2 has the entry 12:30:40PM and 12:30:50PM, they will both be matched to that one entry producing 2 outputs.

For this query that you have made, it sees if Object Kapu27M3 in table 1 exists in table 2. But actually, i want to see if Kapu27M3 which logged at 12:30:32PM is captured as Kapu27M3 moving at 12:30:..PM, and if it is, it should show up on the query. Im not very good at explaining though :banghead:

[OBP]

akn, I am not sure that I understand, does the 12:30:32Pm in your example also include the Date, because if it does then I end up 430 matching records?

[akn112]

hey OBP, yeup, 12:30:32 PM is included in the date. for example:


1 2 Date

Kapu 27M3 01/01/03 12:30:32PM from table 1 would match with

Kapu 27M3 01/01/03 12:30:30PM from table 2 and would match with
Kapu 27M3 01/01/03 12:30:02PM from table 2


but i just want it to say,
Kapu 27M3 01/01/03 12:30:xxPM has been found in table 2 once,

but when i run it, i will get both the matches as two distinct records in the output.
In essence, if this were an ideal world and all entries found in table 1 can be found in table 2, it should get a list the same size as table 1.

[OBP]

akn, I have it doing that but most are just showing 1 match, do you want to include those in the output?

[akn112]

the output should show only 1 match if it exists. the problem is, before i sanitized it, list 1 is actually has 2000 entries while list 2 is around 500,000 records. When i run that query im getting LOTS of repeats. i would get matches like around 50 000 records, but i actually only wanted 2000 or less matches (one for each entry in list 1, if the match existed)

[OBP]

Have a look at this version and see if it does what you want.

[akn112]

hey OBP, thanks for helping me through! it works well. the only problem that i didnt notice before is that one of the tables is in mm/dd format while the other is dd/mm format. is there any way to change this?

[OBP]

The date is actually stored as a Number so you should be able to just change the Format, but it will depend on where the data came from.

[akn112]

hey OPB, i ended up using my original code while using ideas taken from your edited code. I made up a new field called test in my old query and defined it as [1]+[2]+[Date] to it. I then made another query, a find duplicates query, to find dupes from the [test] field since it groups all the duplicates together. I only had to remove the count>1 condition. Thanks again for the help~