Opening recordset within Loop - better method?

fujih
10-26-2004, 11:57 AM
I have an Access table containing records containing the following fields:

an ip address
a date field
a time field

and other fields

I need to extract into another table records distinct records for the ip address and date and where the time is an hour apart.

eg.

192.168.0.1 10/01/04 11:00:00
192.168.0.1 10/01/04 11:30:00
192.154.0.2 10/01/04 12:01:00
192.168.0.1 10/01/04 12:30:00
192.154.0.2 10/01/04 13:30:00

I would like in the second table

192.168.0.1 10/01/04 11:00:00
192.154.0.1 10/01/04 12:01:00
192.168.0.1 10/01/04 12:30:00
192.154.0.2 10/01/04 13:30:00

I am currently doing this by selecting everything from the first table into a recordset and then using a Do While Not rs.EOF... Loop. Within this loop I am then doing a select on the second table to see if there is a record in there with the same ip address, same date and the time is less than the time field + 1 hour. If there is then I move on to the next record otherwise I insert a record into the second table.

This is taking a long time though and I'm sure there must be a quicker way. Any help would be appreciated.

Dennis DVR
10-27-2004, 09:13 AM
It's not clear to me, can you explain this

I am then doing a select on the second table to see if there is a record in there with the same ip address, same date and the time is less than the time field + 1 hour. If there is then I move on to the next record otherwise I insert a record into the second table.

If the record is extracted from first table to second table, why don't you just do the validation while extracting the record from first table to second table instead of extracting the data and validate it afterwards? or you mean "I am then doing a select on the first table"?

if this is the case, can you explain "if there is a record in there with the same ip address, same date and the time is less than the time field + 1 hour."? coz your example above is not clear. I don't know where you get the ip "192.154.0.1". Maybe we can come-up with a faster approach using SELECT statement instead of looping thorugh the records.

fujih
10-27-2004, 09:27 AM
Sorry I didn't really explain it that well.

I have a table which contains records of user access to a website and so the fields stores information on the ip address and the date and time that the site was accessed. I want to get a distinct list of ip addresses where the time is an hour apart.

e.g. Ip 192.168.0.1 first accesses the site at 9:00am on 27th October 2004. I want to ignore all records for ip 192.168.0.1 where the time is before 10:00am on 27th October 2004. Say for instance the next selected record for Ip 192.168.0.1 is at 11:30am on 27th October 2004. I would then ignore all records before 12:30pm etc.

Thus I couldn't see a way I could get the information I wanted in a SELECT statement using a GROUP BY.

I have managed to speed things up by getting my recordset in order of Ip address, date and then time. I then insert a record into a new table where the ip address changes in which case I set a date variable and a time variable, or where the time of the current record is one hour past the time of my time variable and the date and ip variables are the same.

strIp = ""
Do While Not rs.EOF
If strIP <> rs!Ip Then
strSQL = "INSERT INTO Download (Ip, dDate, cTime) VALUES ('" & rs!Ip & "', #" & _
Format$(rs!dDate, "mm/dd/yy") & "#, '" & rs!ctime & "')"

gconnDLoad.Execute strSQL

strIP = rs!Ip
dDate = rs!dDate
strTime = rs!ctime

dteDateTime = DateAdd("h", 1, rs!ctime)
Else
If rs!ctime > dteDateTime Then
strSQL = "INSERT INTO Download (Ip, dDate, cTime) VALUES ('" & rs!Ip & "', #" & _
Format$(rs!dDate, "mm/dd/yy") & "#, '" & rs!ctime & "')"

gconnDLoad.Execute strSQL

dDate = rs!dDate
strTime = rs!ctime

dteDateTime = DateAdd("h", 1, rs!ctime)
End If
End If

rs.MoveNext
Loop

Dennis DVR
10-27-2004, 09:45 AM
Ok, I think you can do that with SELECT statement using LEFT JOIN, what is the data type of Time field btw? does it stored as DateTime Field?



be right back :)

fujih
10-27-2004, 09:48 AM
The time field is a Text field, 10 characters long.

My head just hurts thinking about how you would be able to use a LEFT JOIN! Good luck...

Dennis DVR
10-27-2004, 10:29 AM
The time field is a Text field, 10 characters long.

My head just hurts thinking about how you would be able to use a LEFT JOIN! Good luck...

Oh...., sorry, I meant RIGHT JOIN :) maybe you are already in the right direction, I just want to add something to make it more faster, thou i didn't see your code for opening the recordset, but just in case, you should open the recordset using forwardonly cursor and readonly locktype, it will help you to speed-up the opening process of your table.

would you mine attaching the MDB file, so I can make a test if it's really possible to use the SELECT.

fujih
10-27-2004, 10:37 AM
Oh...., sorry, I meant RIGHT JOIN :) maybe you are already in the right direction, I just want to add something to make it more faster, thou i didn't see your code for opening the recordset, but just in case, you should open the recordset using forwardonly cursor and readonly locktype, it will help you to speed-up the opening process of your table.

would you mine attaching the MDB file, so I can make a test if it's really possible to use the SELECT.


Thanks. I have attached a test database with the relevant table as a zip file.

Dennis DVR
10-27-2004, 08:56 PM
Hi fujih

I Just want to ask, why don't you use a single field for dDate and cTime field? it can be stored in a single field (Date/Time) so you can easily compute the difference using the date function, and you never mentioned about the time with < 1 hour difference but have different date i.e. the user log at 13:20 on October 28, 2004 and login again at 00:10 October 29, 2004 will it also be eliminated? if yes, then storing the dDate and cTime field in a single (Date/Time) field would ease the job.

Another option would be to add a field that can group your record that would lies on the same period of time, so you can easily elimate the unnecessary record using a simple SELECT statement without having to loop in the entire table.

btw: I really think that doing a loop should be necessary, I just can't think of any fine solution to your problem at this point.

fujih
10-28-2004, 04:13 AM
Duane,

You are right I was missing records where the date was different but the time was within the one hour period. I have now changed my loop to check whether the date is different before checking the time and if it is different for that ip then I insert a record. Of course this doesn't help with the situation close to midnight where the hour difference would mean a change in date but I know that for my purposes this will not happen as access to the website would be within working hours.

It's running at an acceptable speed now. Thanks for all your help.

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum