Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2009

    Unanswered: Help with Access queries


    Hi Guys,

    This is the first time after school that I am trying to use ms access at work and i need ur help in creating a query.
    Any help will be highly appreciated!!

    Here is what I need...

    I have relatively small ms acces database with about 1000

    I have 3 colums

    date ipaddress sitename

    12/09 A
    12/09 A
    12/09 A
    12/09 B
    12/09 C

    What i need is if an ipadress is recorded more than 2 times in a single month for instance ( put those ipaddress with the Site-Name in a seperate table.

    Any ideas?

  2. #2
    Join Date
    Jun 2007
    Maitland NSW,Australia
    You will need two queries
    First query a Group By to count the number of times the IP address is recorded for the month.

    Something like this
    SELECT tbl_test_data.period, tbl_test_data.isp_add, Count( AS isp_count
    FROM tbl_test_data
    GROUP BY tbl_test_data.period, tbl_test_data.isp_add
    HAVING (((Count(>2));

    period is your date e.g. 12/09 do not use Date as a field name as it is a reserved word.
    isp_add is your ipaddress e.g.
    isp_count is the count for the number of times that your ipaddress occurs during the month.
    The Having clause will only show where the number of times is > 2

    Second Query - To append the site names we now need to join the first query to your table with the following
    INSERT INTO tbl_sites ( period, isp_add, site )
    SELECT tbl_test_data.period, tbl_test_data.isp_add,
    FROM tbl_test_data INNER JOIN qry_totals ON (tbl_test_data.isp_add = qry_totals.isp_add) AND (tbl_test_data.period = qry_totals.period);

    I have attached a sample database for you. note, I have period as text you will need to change it to the correct format.
    Attached Files Attached Files

  3. #3
    Join Date
    Nov 2009
    Hi Poppa!!!

    Bro I dont know how to thank u .. you are the greatest I mean to take time off and help me out.. you are a GEM and thanks for attaching that database too ur the KING of access as far as I am concerned!!!


  4. #4
    Join Date
    Oct 2009
    as helpful as PS was - - and it was very helpful - - nonetheless you should at least be aware that what you ask is a database no no. One does not write data from one table to another ( at least in terms of database best practices ...)

    why? .... lets say the data changes in your first starting table......and what was a duplicate before is no longer a duplicate...then you have to delete your data from the second table....

    in your case this may never ever be the case - since it looks like an autostamp log file of some sort... but readers should know the pitfall of your request....

    the db-correct reply is simply to do a date range bound duplicates query of the first table any time you need to see duplicates within the month.....

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts