filter unique and exclude duplicates

[ninrulz]

I have been working on a database to perform some of the tasks that I
have been doing manually in Excel. One of those tasks is to sort and
exclude duplicate data. The fields are as follows

Building
Name or letter
Floor
MAC
Channel
SSID
RSSI
WEP

I would like to perorm the same actions I did in excel but use an
Access query. The actions I performed in Excel are below.

Select ALL and click data->sort->header row, MAC-ascending,
RSSI-descending
Select the "MAC" column and click Data->filter->Advanced
Filter->unique recorders

The sorting part seems pretty easy, but I can't figure out how to
remove all but the first record of each MAC.

The purpose of the query is to display the floor with the highest RSSI
number. This would tell me where to find each Wireless AP.

Any help is appreciated

Thanks,
Mark

[pietlinden]

ninrulz wrote:

I have been working on a database to perform some of the tasks that I
have been doing manually in Excel. One of those tasks is to sort and
exclude duplicate data. The fields are as follows

Building
Name or letter
Floor
MAC
Channel
SSID
RSSI
WEP

I would like to perorm the same actions I did in excel but use an
Access query. The actions I performed in Excel are below.

Select ALL and click data->sort->header row, MAC-ascending,
RSSI-descending
Select the "MAC" column and click Data->filter->Advanced
Filter->unique recorders

The sorting part seems pretty easy, but I can't figure out how to
remove all but the first record of each MAC.

The purpose of the query is to display the floor with the highest RSSI
number. This would tell me where to find each Wireless AP.

Any help is appreciated

Thanks,
Mark

Use a SELECT query. Add the DISTINCT clause and you're good to go.

SELECT DISTINCT Field1, Field2,...
FROM MyTable
WHERE Field1='Whatever'
ORDER BY Field1, Field2...

[ninrulz]

Ok, so here is my code. I am not sure what I would put after the WHERE=
I have numerous MAC addresses with different RSSI values. I would like
to display only the rows with unique MAC adresses and the lowest RSSI
value.

SELECT DISTINCT Scan1.MAC, Scan1.RSSI, Scan1.Building, Scan1.[Name or
Letter], Scan1.Floor, Scan1.Channel, Scan1.SSID, Scan1.WEP
FROM [Scan - sort test] AS Scan1
GROUP BY Scan1.MAC, Scan1.RSSI, Scan1.Building, Scan1.[Name or Letter],
Scan1.Floor, Scan1.Channel, Scan1.SSID, Scan1.WEP
ORDER BY Scan1.MAC, Scan1.RSSI DESC;

Thanks for your help
Mark

pi********@hotmail.com wrote:

ninrulz wrote:

I have been working on a database to perform some of the tasks that I
have been doing manually in Excel. One of those tasks is to sort and
exclude duplicate data. The fields are as follows

Building
Name or letter
Floor
MAC
Channel
SSID
RSSI
WEP

I would like to perorm the same actions I did in excel but use an
Access query. The actions I performed in Excel are below.

Select ALL and click data->sort->header row, MAC-ascending,
RSSI-descending
Select the "MAC" column and click Data->filter->Advanced
Filter->unique recorders

The sorting part seems pretty easy, but I can't figure out how to
remove all but the first record of each MAC.

The purpose of the query is to display the floor with the highest RSSI
number. This would tell me where to find each Wireless AP.

Any help is appreciated

Thanks,
Mark

Use a SELECT query. Add the DISTINCT clause and you're good to go.

SELECT DISTINCT Field1, Field2,...
FROM MyTable
WHERE Field1='Whatever'
ORDER BY Field1, Field2...

[ninrulz]

I'm probably barking up the wrong tree here, but I have not idea what
to do.

I created a Make Query and used MAX to display the highest RSSI for
each MAC address. I could not display the floor in the query because
the results would come out wrong. I was able to display the correct
results with the code below.

SELECT Scan.Building, Scan.[Name or Letter], Scan.MAC, Scan.Channel,
Scan.SSID, Max(Scan.RSSI) AS MaxOfRSSI, Scan.WEP INTO [Scan - sort
test]
FROM Scan
GROUP BY Scan.Building, Scan.[Name or Letter], Scan.MAC, Scan.Channel,
Scan.SSID, Scan.WEP
ORDER BY Scan.MAC, Max(Scan.RSSI) DESC;

However, the floor is important and I can't figure out how to add that
back into the query. I tried joining the 2 tables and throwing the
floor back in. I end up with 2 identical results for each of the rows.
The code for the second query is below.

SELECT [Scan - sort test].Building, [Scan - sort test].[Name or
Letter], Scan.Floor, [Scan - sort test].MAC, [Scan - sort
test].Channel, [Scan - sort test].SSID, [Scan - sort test].MaxOfRSSI,
[Scan - sort test].WEP
FROM [Scan - sort test] INNER JOIN Scan ON [Scan - sort test].MaxOfRSSI
= Scan.RSSI;

Any advice?

Thanks,
Mark

[breadon]

Try using a subquery. Something like:

SELECT Scan.Building, Scan.[Name or Letter], Scan.MAC, Scan.Channel,
Scan.Floor,
Scan.SSID, Scan.WEP FROM Scan
where Scan.SSID = (select max(b.ssid) from Scan b where b.MAC =
Scan.MAC)