Help with a Top 15 by two different dimensions

[accessmatt]

Hi, it's been a while since I've used Access so I'm a bit out of practice what I'm trying to do I think should be pretty easy. Basically I have two dimensions in my table (Landing Page, Keyword) and I'm trying to group each of them by the top 15 # of Sessions. Kind of a two step query that first finds top the Top 15 Landing Pages by Sessions and then finds the Top 15 Keywords by Sessions just for those Landing Pages.

I'm able to create the first query and get the top 15 Landing Pages by Sesssions. It looks like this:

SELECT TOP 15 Table1.[Landing Page],Table1.Sessions From Table1 
GROUP BY Table1.[Landing Page], Table1.Sessions
ORDER By Table1.Sessions DESC

Can anyone help me run the second step and get the top 15 keywords just for these Landing Pages?

[ranman256]

Q1 pull random top15 recs

so you want Q2 to count Landing Page, in Q1
and Q3 to count Keywords in Q1
?

[kd2017]

So you have a table, [Table1], with the fields [Landing page], [Sessions], [Keyword], and presumable an [ID]. You want to query the Top 15 keywords for EACH of the top 15 Landing pages.

Do I understand you correctly?

If so could you explain more in general what this is and what the table describes because something seems off... denormalized.

[accessmatt]

@kd2017,

That's right. The table looks like this:

[ID],[Landing page], [Sessions], [Keyword]

and what I want is the top 15 keywords for each of the top 15 Landing Pages

Any help would be appreciated.
Thanks!

[accessmatt]

@ranman256, Q1 pulls top 15 Landing Pages then from there I want to pull the Top 15 Keywords for those Landing Pages. Sorted by # of Sessions (highest to least)

[orange]

matt,
Do you have a few sample records you could post?

Suggestions:

'TopLandingPagesBySessionCount 

SELECT TOP 15 MattFinalT.LandingPage
, Count(MattFinalT.SessionID) AS CountOfSessionID
FROM MattFinalT
GROUP BY MattFinalT.LandingPage
ORDER BY Count(MattFinalT.SessionID) DESC;

SELECT TOP 15 MattFinalT.Keyword
, MattFinalT.LandingPage
, Count(MattFinalT.SessionID) AS CountOfSessionID1
FROM MattFinalT INNER JOIN TopLandingPagesBySessionCount 
ON MattFinalT.LandingPage = TopLandingPagesBySessionCount.LandingPage
GROUP BY MattFinalT.Keyword, MattFinalT.LandingPage
ORDER BY Count(MattFinalT.SessionID) DESC;

 table_name        field_name        ordinal_position    data_type    length    
MattFinalT    ID                        0              Long    4    
            LandingPage                1              Text            255    
            SessionID                2              Long    4    
            Keyword                    3              Text            255

These queries do not deal with ties.

[accessmatt]

@orange. Thanks but that doesn't quite solve it. I'm still trying to get the top 15 keywords per each of those landing pages (top LPs identified in my first query). Here's an example of the data and the two query's I'm using.

ID	Landing Page	Keyword	Sessions
1	LP 1	Keyword 1	20
2	LP 1	Keyword 2	5
3	LP 1	Keyword 3	500
4	LP 1	Keyword 4	10

Query 1:

SELECT TOP 15 Table1.[Landing Page], Sum(Table1.Sessions) AS SumOfSessionsFROM Table1
GROUP BY Table1.[Landing Page]
ORDER BY Sum(Table1.Sessions) DESC;

Query 2:

SELECT Query1.[Landing Page], Query1.SumOfSessions, Table1.Keyword, Sum(Table1.Sessions) AS SumOfSessions1FROM Table1 INNER JOIN Query1 ON Table1.[Landing Page] = Query1.[Landing Page]
GROUP BY Query1.[Landing Page], Query1.SumOfSessions, Table1.Keyword;

[orange]

??Not sure why you use Sum vs Count re Sessions??

I mocked up some data, so I'm not sure if we're measuring the same thing.

Initially I had LandingPage then the number of sessions and then built a final table with LandingPage and record for each sessionID, and one keyword per LandingPage, SessionID.

I had 100 LandingPages, each with its Sessions and sessionID and I selected a random Keyword from an array of 100 unique keywords and assigned that keyword to that sessionID.

[accessmatt]

@Orange, I'm using Sum because I ultimately want to Sum the Sessions (by Landing Page in Step 1 and by Keyword in Step 2). The more I poke on this one, the more I think the solution is to write a sub-query.

[orange]

I'm sure the structure I set up does not mirror yours. Do you want to post a copy of your database with enough records to do some tests or at least send the table structure with some sample data.

[accessmatt]

@orange, sure here's an example database: https://drive.google.com/file/d/1Zm8...ew?usp=sharing

[orange]

Can you post in the forum? Your link says Access Denied.

[accessmatt]

ExampleDatabase.accdbExampleDatabase.accdb

[orange]

Try (I removed the space from "Landing Page")

SELECT table1.LandingPage
    ,table1.Keyword
    ,sum(table1.sessions) AS CountSessions
FROM table1
WHERE table1.keyword IN (
        SELECT TOP 15 xx.keyword
        FROM table1 AS xx
        WHERE xx.landingpage = table1.landingpage
        GROUP BY xx.landingpage
            ,xx.keyword
        ORDER BY xx.landingpage ASC
            ,sum(xx.sessions) DESC
        )
GROUP BY landingpage
    ,keyword
ORDER BY table1.LandingPage
    ,SUM(table1.Sessions) DESC
    ,table1.Keyword;

[accessmatt]

Hi @orange, thanks for trying but it's not working as expected. I'm getting prompted to enter the parameter for xx.landingpage. Am I missing something?