#1
  1. No Profile Picture
    Contributing User
    ASP Adventurer (500 - 999 posts)

    Join Date
    Feb 2008
    Posts
    790
    Rep Power
    30

    Tracking websites with 2 different fields


    I have a link that I provide in an email, once click on it to goes to a landing page and then redirects to another site. The landing page is logged in a field called "site" and it also logs the IP in field name "IP". Once redirects it continues to log a different site name in the "site" field, as well as the "IP field.

    That being said, how can I write a sql statement that pulls up all of the "IP" fields and "site" fields with the IP that first hits the original landing page?

    I suppose you would have to loop through the data base to pick up the name of the landing page that is logged under "site" and then bring up the associated "IP" field regardless of the name of the "site" field.

    I am stumped with this one and any help would be appreciated.

    Thanks!
  2. #2
  3. 0x800A0C93
    ASP Super Hero (2500 - 2999 posts)

    Join Date
    Jan 2009
    Location
    Ridin' through this world...
    Posts
    2,975
    Rep Power
    1310
    Your question makes no sense.
    Brinkster- free development account. Not affiliated, but I sure like free. Brinkster.com
  4. #3
  5. No Profile Picture
    Contributing User
    ASP Adventurer (500 - 999 posts)

    Join Date
    Feb 2008
    Posts
    790
    Rep Power
    30
    Let me see if I can better explain. I suppose it's like a 2 level search.

    sql = "select fieldIP from table where field1 = 'abc'"

    That's only going to occur when the user hits the index page. But the user will then continue to click thru other pages.

    I want to select * where that IP occurred in the above statement. (field1 will no longer = 'abc'

    I hope that makes more sense.

    Thanks for your response.
  6. #4
  7. Contributing User
    ASP Skiller (1500 - 1999 posts)

    Join Date
    Mar 2005
    Location
    Columbus, OH
    Posts
    1,592
    Rep Power
    278
    do you record a time when page was requested?
    If no how you would know which page was requested first.....
    GK
    __________________________________________________ _____
    if you found this post is useful click Give Rep button (bottom side on this reply ) and agree
  8. #5
  9. No Profile Picture
    Contributing User
    ASP Adventurer (500 - 999 posts)

    Join Date
    Feb 2008
    Posts
    790
    Rep Power
    30
    Yes, I have a date/time which matches the page that was requested first.
  10. #6
  11. Contributing User
    ASP Skiller (1500 - 1999 posts)

    Join Date
    Mar 2005
    Location
    Columbus, OH
    Posts
    1,592
    Rep Power
    278
    So, you have table like

    site IPField stamp
    'abc' 1.2.3.45 '2013-01-28 05:55.654'
    'dfg' 1.2.3.45 '2013-01-28 05:56.654'
    'abc' 1.2.3.48 '2013-01-28 06:55.654'
    'dfg' 1.2.3.48 '2013-01-28 06:55.654'
    'dfg' 1.2.4.45 '2013-01-28 07:55.654'
    'abc' 1.2.4.45 '2013-01-28 07:56.654'


    right?

    Now, what result you expect to see?
  12. #7
  13. No Profile Picture
    Contributing User
    ASP Adventurer (500 - 999 posts)

    Join Date
    Feb 2008
    Posts
    790
    Rep Power
    30
    I want to see:

    'abc' 1.2.3.45 '2013-01-28 05:55.654' (this one)
    'dfg' 1.2.3.45 '2013-01-28 05:56.654' (this one because it originated from 'abc')

    'abc' 1.2.3.48 '2013-01-28 06:55.654' (this one)
    'dfg' 1.2.3.48 '2013-01-28 06:55.654' (this one because it originated from 'abc')

    'dfg' 1.2.4.45 '2013-01-28 07:55.654' (NOT this one because it didn't originate from 'abc'

    'abc' 1.2.4.45 '2013-01-28 07:56.654' (this one)

    Sorry for the delay in response. For some reason, I am not getting the email when you replied.

    Thanks for you help.
  14. #8
  15. 0x800A0C93
    ASP Super Hero (2500 - 2999 posts)

    Join Date
    Jan 2009
    Location
    Ridin' through this world...
    Posts
    2,975
    Rep Power
    1310
    I think it would help you a lot if you would take the time to think through exactly what you want and exactly what the problem is. It certainly still isn't clear to me.

    Is this what you want? You have two sets. 1) the set of IP's that visited your landing page 2) the set of IP's that visited other pages. Do you want the intersection of those sets? i.e the list of other pages visited by IP's that also visited your landing page?

    select page
    from table
    where ip in
    (
    select ip
    from table
    where page = landing page
    )
    and page not landing page


    What are you tryng to do? Are you trying to construct the most common chains of progression through a site- e.g. people go here first and then here and then here?

    For something like that a set up like Google Analytics might actually be what you want.

    Comments on this post

    • phoenixaz disagrees : works - thanks for helping
  16. #9
  17. Contributing User
    ASP Skiller (1500 - 1999 posts)

    Join Date
    Mar 2005
    Location
    Columbus, OH
    Posts
    1,592
    Rep Power
    278
    bigmike is right if it is for analytic just use Google Analytics it is free
    but you can try
    Code:
    select t.site, t.IPField, t.stamp 
    	from #T as t
    	join #T as tt
    	on t.IPField=tt.IPField
    	and t.site<>tt.site
    where t.site='abc'
    union
    select tt.site, tt.IPField, tt.stamp 
    	from #T as t
    	join #T as tt
    	on t.IPField=tt.IPField
    	and t.site<>tt.site
    where t.site='abc'
    order by t.stamp, t.IPField

    Comments on this post

    • phoenixaz agrees : works, thanks for helping
  18. #10
  19. No Profile Picture
    Contributing User
    ASP Adventurer (500 - 999 posts)

    Join Date
    Feb 2008
    Posts
    790
    Rep Power
    30
    select t.site, t.IPField, t.stamp
    from #T as t
    join #T as tt
    on t.IPField=tt.IPField
    and t.site<>tt.site
    where t.site='abc'
    union
    select tt.site, tt.IPField, tt.stamp
    from #T as t
    join #T as tt
    on t.IPField=tt.IPField
    and t.site<>tt.site
    where t.site='abc'
    order by t.stamp, t.IPField
    I think that seems to work and will know for sure if I can limit the search of the t.stamp field. I tried this and it didn't work.

    where t.site ='abc' and t.stamp > '1/1/2013' order by .....

    where t.site='abc' and t.stamp > '1/1/2013' union select tt.site ....

    Any thoughts? Thanks!
  20. #11
  21. No Profile Picture
    Contributing User
    ASP Adventurer (500 - 999 posts)

    Join Date
    Feb 2008
    Posts
    790
    Rep Power
    30
    select page
    from table
    where ip in
    (
    select ip
    from table
    where page = landing page
    )
    and page not landing page
    That gives me the results that I was looking for, thanks. I was able to limit the search by adding a date field. I don't know anything about Google Analytics, I will look into that, but your code works.

    Thanks for helping.

Similar Threads

  1. Creating new table from form data fields/clreaing datat fields.
    By vbacook7 in forum Microsoft Access Help
    Replies: 7
    Last Post: April 15th, 2008, 11:25 AM
  2. Replies: 1
    Last Post: May 17th, 2007, 09:50 PM
  3. Replies: 1
    Last Post: August 28th, 2006, 11:33 PM
  4. Replies: 2
    Last Post: November 30th, 2005, 07:06 AM
  5. Choosing fields from combo box to make fields visible
    By mysterymouse in forum Microsoft Access Help
    Replies: 4
    Last Post: August 31st, 2004, 10:39 PM

IMN logo majestic logo threadwatch logo seochat tools logo