Microsoft Access Forums

Go Back   Microsoft Access Forums > Access Forums > Queries

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 12-01-2008, 12:38 AM
Novice
 
Join Date: Dec 2008
Posts: 1
igorbaldacci is on a distinguished road
Default 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 12:48 AM.
Reply With Quote
  #2  
Old 12-02-2008, 12:30 PM
jya jya is offline Windows XP Access 2007 (version 12.0)
Competent Performer
 
Join Date: Sep 2007
Location: Chicagoland
Posts: 109
jya is an unknown quantity at this point
Default

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
Reply With Quote
Reply

Bookmarks

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Difficult problem francisca_carv Access 0 11-19-2008 02:50 PM
Combo box select from two fields cnestg8r Access 0 10-31-2008 08:05 AM
Multi-select listbox and update flag in table Suresh Programming 0 12-18-2007 10:04 PM
Using SELECT query within a VBA code in Access championss Programming 4 10-23-2006 03:50 PM
SELECT FUNCTION help please scott munkirs Queries 0 10-17-2006 05:44 AM


All times are GMT -8. The time now is 03:03 PM.


Powered by vBulletin® Version 3.8.4
Copyright ©2000 - 2010, Jelsoft Enterprises Ltd.
SEO by vBSEO 3.3.2 ©2009, Crawlability, Inc.