Finding pairs of data

[Crystalyzer]

I have a table of data in Access 2003 with columns "Company" and "Tag" as follows:

Company      Tag
ABC          media
ABC          showbiz
ABC          TV
ABC          entertainment
DEF          media
DEF          entertainment
DEF          radio
GHI          software
GHI          enterprise
GHI          programming

What I want to do is find the top ten most often paired tags. In the simple example above "media" and "entertainment" have been paired (with the same company) twice. Can anyone suggest a way to do this?

Thanks and best regards,
-Lloyd

 

[CautionMP]

Crystalyzer,
If your using the Jet database engine you can use the [tt]TOP[/tt] keyword.
[tt]SELECT TOP 10 Count(Tag) AS Rank FROM [table of data]
GROUP BY Company
ORDER BY Count(Tag) DESC;[/tt]

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[Crystalyzer]

CautionMP,

Thanks for the suggestion but I think that will only give me the top 10 SINGLE tags. I am looking for the tag pairings which I was able to get by creating a query and joining the table to itself with and expresion that took the tag from table1 and concatenated it (with a space in the middle) with the tag from table2. This gave me a single data item for each pairing by company in the query. I was then able to copy that data out to excel (where I am more familiar) and with some sorting and a few formulas I got what I needed.

Thanks again!

Thanks and best regards,
-Lloyd

 

[CautionMP]

Crystalyzer,
Glad to hear you got it worked out. I ran across your other post and realize the query I gave you wouldn't work.

Just for grins and giggles, this might do what your looking for:

[tt]SELECT TOP 10 Tag, TagD, Count(Company) AS Rank
FROM (SELECT Company, Tag FROM tblPairing ORDER BY Tag) AS Original
INNER JOIN (SELECT Company AS CompanyD, Tag AS TagD FROM tblPairing ORDER BY Tag) AS Duplicate
ON Original.Company = Duplicate.CompanyD
GROUP BY Tag, TagD
HAVING Tag>TagD
ORDER BY Count(Company) DESC;[/tt]

CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[PHV]

Something like this (SQL code) ?
SELECT TOP 10 A.Tag, B.Tag, Count(*) As CountOfPair
FROM tblCompany AS A INNER JOIN tblCompany AS B ON A.Company = B.Company
WHERE B.Tag > A.Tag
GROUP BY A.Tag, B.Tag
ORDER BY 3 DESC

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886