count records within 3 days

[vtselios]

Hello guys,

I need some help please with a database. I have a table with many records (400.000) and i have a field [date] , a field [cname] and a field [cphone]. I want to count duplicate phone numbers in a range of three days. Is that possible?

Thanks a lot

 

[Joe4]

Is your date range a pre-determined or static range?

 

[Camranjaber]

Do you know at least some information to narrow down the list, such as area code, prefix, or last 4 digits? Does the amount of data become reduced if you isolate that time frame (the 3 days)? Are you looking for a particular pattern of phone numbers, or certain numbers in particular? Please provide more information.

 

[vtselios]

Thanks guys for your replies. What i want is this. Users to enter a start date and a number in a form (i have made two text boxes in a form) and the query result to be the dublicate phone numbers between the given start date minus the given range. Does that make sence? Propably not. Let me gine you an example. If the user enter as start date 18/12/2015 and as range the number 3. The result sould be all duplicate phone numbers between 15/12/2015 and 18/12/2015. I don't want specific phone numbers. I want all duplicate phone numbers between the given range.

 

[Micron]

Better you should supply two dates in the textboxes, and maybe even allow user to use built in calendar. Or you can work with what I post and replace the date part with a DateAdd function to add 3 (or whatever) days to the start date. Better let us/me know if your date includes time or not. Surprised no one mentioned yet that Date is a lousy name for a field. Perhaps you should review "reserved words in Access". Obviously, you will have to use your own field and table names instead of mine - except for the Tmp aliases. You must include them.

SELECT DISTINCT tblDupePhone.CallDate, tblDupePhone.cname
FROM tblDupePhone
GROUP BY tblDupePhone.cphone, tblDupePhone.CallDate, tblDupePhone.cname
HAVING (((tblDupePhone.cphone) In (SELECT Tmp.cphone FROM tblDupePhone AS Tmp GROUP BY Tmp.cphone, Tmp.CallDate HAVING (((Tmp.CallDate) Between [BeginDate] And [EndDate]) AND ((Count(*))>1)) ORDER BY Tmp.cphone)))
ORDER BY tblDupePhone.cphone;

 

[vtselios]

Hello again. I followed the above instructions and i now have the duplicate phones between a given date range. I have also changed the date field name as suggested. So thank you guys for all your comments. One last thing that may be the most difficult to achieve. I have sorted the duplicate phones by date and as a result i have something like that : (i forgot to mention that the field calldate is a date followed by time, example 01/01/2015 18:25 pm)

calldate cphone

1/1/2015 10:00 pm 55566666
1/1/2015 10:35 pm 55566666
1/1/2015 11:50 pm 55566666
1/1/2015 17:00 am 22222222
1/1/2015 19:20 am 22222222
2/1/2015 15:00 pm 44441598
2/1/2015 16:40 pm 44441598
2/1/2015 18:00 pm 44441598
2/1/2015 19:10 pm 44441598
2/1/2015 19:24 pm 44441598


What i need, is to mark only the first contact with an x or a number or anything. I need an extra column.The format should be like this one:

calldate cphone FirstCall

1/1/2015 10:00 pm 55566666 x
1/1/2015 10:35 pm 55566666
1/1/2015 11:50 pm 55566666
1/1/2015 17:00 am 22222222 x
1/1/2015 19:20 am 22222222
2/1/2015 15:00 pm 44441598 x
2/1/2015 16:40 pm 44441598
2/1/2015 18:00 pm 44441598
2/1/2015 19:10 pm 44441598
2/1/2015 19:24 pm 44441598

My first thought was that i could achieve this, by comparing the current record with the previous one. Something like : if current_record.calldata.value <> previous_record.calldate.value then current_record.firstcall.value="x". I have googled how to compare current record with previous record but i didn't manage to find something helpful.

Thanks again for your help.

 

[Micron]

i forgot to mention that the field calldate is a date followed by time

You will likely notice that the BETWEEN operator will cut off calls after 00:00:00 on the end date. So, 1:00 AM would not be included, for instance. That's just the way it works if the date field contains time. You will have to use the DateAdd function to add either 86399 seconds or 1439 minutes to include the rest of the day. See here DateAdd (interval, number, date)
Between [BeginDate] And DateAdd(Interval, number,[EndDate]) AND ((Count(*))>1)) ORDER BY Tmp.cphone)))
My approach would be to loop through a recordset because I have done this before, but it was more complicated. So I think you will have to create an append query using the query you built and dump the results into a temp table. That table needs an extra field for FirstCall; no PK required. I don't even want to think of how to add this field to a query and compare, even if it can be done. You'd have to loop through that table and compare records 1-2, 2-3, 3-4 and so on. Not too difficult. If you create the table, append query and get it to populate the table, I'll create some code. I'd need the table and field names to start with.

 

[Micron]

PS: not sure your data example is accurate.
1/1/2015 17:00 am. 17:00 AM? How is this possible? I don't think AM or PM are even used in a 24 hour format, so which are you using, 24 or 12, or am I wrong about that? I was looking to ensure that a date down in the row count could be earlier than a date higher up in the row count since they are supposed to be sorted by phone number when I noticed these odd times.

 

[xenou]

I guess the question is why specifically the format with the x in the other column? You can get the phone number and the dates, even a count, plus the max or min. What use is it having the first one starred? I'm questioning that because I don't see the value of the format here. That said, you can just union the result with the min date (and a star) to the results greater than the min date (without a star), grouping on phone number (and possibly date, because your sample data has all phone numbers with the same date, but that seems coincidental given the description of the problem).

 

[Micron]

I tend not to ask why, because my mission is to do or die

union the result with the min date (and a star) to the results greater than the min date (without a star),
My question regarding the odd time values aside, I'm afraid you lost me because the x (star) is not there until it's recognized as the first instance of the phone number. I must be missing something? But I do agree the data seems a bit off the mark considering how the calls might come in.