Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2008
    Posts
    1

    very difficult (for me!) SELECT query

    Hi all,
    I have created a table in my DB to make a poll, and now I have the necessity to count the votes using an IP filter.
    This is my table:

    ID (auto-increment colum)
    DESTINATION (1 = Italy, 2 = Franch, 3 = Usa)
    DATE (votation date)
    IP (IP of the votants)

    Now, I have to count how many votes receive the destination 1 (Italy), destination 2 (Franch) and destination 3 (Usa) COUNTING THE IP ADDRESS ONE TIME PER DATE. That is to say that I want to eliminate all the people (IP) that have voted more than one time in the same day.

    This is an example of my data in the table:

    (ID, DESTINATION, DATE, IP)
    1 - 1 - 29/10/2008 18.29.41 - 87.16.176.3
    2 - 2 - 30/10/2008 18.29.41 - 87.16.176.3
    3 - 3 - 30/10/2008 18.30.08 - 87.16.176.3
    4 - 3 - 30/10/2008 18.30.13 - 87.16.176.3
    5 - 3 - 30/10/2008 18.30.19 - 87.16.176.3
    6 - 3 - 30/10/2008 18.30.28 - 87.16.176.3

    The IP = "87.16.176.2" in the day 30/10/2008 has voted for 5 times, I would like to count ONLY his first vote:

    DESTINATION = 1 IN THE DAY 29
    (ONLY) DESTINATION = 2 IN THE DAY 30

    I have written this SQL query:

    SELECT
    IP, Data, COUNT(Destination) AS Dest
    FROM
    Statistiche
    GROUP BY IP, Data

    but this is the result:

    (IP, Data, Dest)
    87.16.176.3 - 30/10/2008 18.29.41 - 1
    87.16.176.3 - 30/10/2008 18.30.08 - 1
    87.16.176.3 - 30/10/2008 18.30.13 - 1
    87.16.176.3 - 30/10/2008 18.30.19 - 1
    87.16.176.3 - 30/10/2008 18.30.28 - 1
    87.16.176.3 - 31/10/2008 10.31.53 - 1
    87.16.176.3 - 31/10/2008 10.31.58 - 1


    87.16.176.3 - 31/10/2008 10.32.04 - 1
    87.16.176.3 - 31/10/2008 10.32.42 - 1


    I've tried to use the DISTINCT(Data) command but it doesen't work...

    Is it possible to do what I need to do or I have to use some algorithm to do it programmatically and not using an SQL instruction?

    Some help?
    Last edited by igorbaldacci; 12-01-2008 at 03:48 AM.

  2. #2
    jya is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2007
    Location
    Chicagoland
    Posts
    109
    Not sure if this helps, but have you tried the Min() function?

    SELECT
    IP, Min(Data) AS FirstVote_Date, COUNT(Destination) AS Dest
    FROM
    Statistiche
    GROUP BY IP

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Difficult problem
    By francisca_carv in forum Access
    Replies: 0
    Last Post: 11-19-2008, 05:50 PM
  2. Combo box select from two fields
    By cnestg8r in forum Access
    Replies: 0
    Last Post: 10-31-2008, 10:05 AM
  3. Multi-select listbox and update flag in table
    By Suresh in forum Programming
    Replies: 0
    Last Post: 12-19-2007, 01:04 AM
  4. Using SELECT query within a VBA code in Access
    By championss in forum Programming
    Replies: 4
    Last Post: 10-23-2006, 05:50 PM
  5. SELECT FUNCTION help please
    By scott munkirs in forum Queries
    Replies: 0
    Last Post: 10-17-2006, 07:44 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums