Use dcount with time criteria

[mafhobb]

Hi,

I use the line of code below to get data in unbound textboxes on my form.

Me.txttotalcustomers = DCount("ContactID", "Contacts")

This works well, but now I would like to go a step further and extract this same info on the same table, but only for the last 30 days, then 60 days, then 90 days.

Basically I am trying to see if something along the lines of this can work:

Me.txtTotalIssue30 = DCount("callid", "calls", "[Call Date]>(Now-30)")

Can this be done or am I going to have to resort to queries?

Thanks

mafhobb

 

[Kiwiman]

Howzit

Try somehting like

Me.txttotalcustomers = DCount("ContactID", "Contacts","yourdate>=Date()-30")

 

[mafhobb]

Hi Kiwiman.

Below is my line adapted to your comment:

Me.txtTotalIssue30 = DCount("callid", "calls", "[Call Date]>=(Date()-30)")

I get RunTime error 2471: The expression you entered as a query parameter produced this error: '[Call Date]'

Any ideas?

mafhobb

 

[Kiwiman]

Howzit

Is [Call Date] the corrct fieldname in the table Calls? I would recommend removing any spaces from field and table names should you have them, as they more often than not add complications to coding where none should exist.

 

[mafhobb]

You are right. I updated this and it now works.

Thanks!

mafhobb

 

[mafhobb]

OK, now a harder question...

I got this working just fine on one table, but I need to try to make it work on another one. This second table does not have a date field but it has a numeric automaticID field which is listed in ascending order (older call is lower number and newer call is higher number). Since the tables that have a date field are related to those that do not, then how can I go to the table that has dates to figure out which ID from the table that has no dates is the one that corresponds to a specific date?

Table 1 with no dates is "Contacts" with ContactID being the unique ID field and what I need to count is the number of "CustomerID" from the "x" ContactID that was created on Date()-30.
Table 2 with dates is "Calls" with the date field being "CallDate" and the common field "ContactID"

Mafhobb

 

[Kiwiman]

Howzit

This will count the number of calls for the current contact in the last 30 days. You will need to correct the [Call Date} section to what worked previously as i do not know what correction you made.

Me.txtTotalIssue30 = DCount("callid", "calls", "[Call Date]>=(Date()-30) AND [ContactiD]=" & me.contactid)

 

[mafhobb]

Thanks, but...what I want to count is the number of contactID whithin those dates, not the number of calls..

 

[Kiwiman]

Howzit

I would have a query linking the two tables (on Contactid) and do a Dcount on the query.

Your query will look something like

SELECT Contacts.ContactID
FROM Contacts INNER JOIN Calls ON Contacts.ContactID = Calls.ContactID
WHERE Calls.CallDate >=Date()-30
GROUP BY Contacts.ContactID

You could also use this SQL to apply to a recordset and count the recordset

 

[mafhobb]

OK, the following query returns the list of Contact IDs, along with the call dates. Now I need to figure out how to count this instead of listing the list.

SELECT Contacts.ContactID, Calls.CallDate
FROM Contacts INNER JOIN Calls ON Contacts.ContactID = Calls.ContactID
GROUP BY Contacts.ContactID, Calls.CallDate
HAVING (((Calls.CallDate)>Date()-30));

How to actually count the results? How to do a dcount on the query?

However, I have just realized that the logic I though of to find the number of CustomerID (above) is going to return the number of unique customers that called, but not the NEW customers within that period of time. I can't think of the right logic to do that...

mafhobb

 

[Kiwiman]

Howzit

The dcount on a query is the same as a dcoount on a table - you just specify the query instead of the table. If you add the DateCalled you may find Contacts appearing multiple times if they call on different dates within that timeframe. If you only want the unique number of contacts then remove the CallDate.

To get new customer calls you will need a creation_date in your contact table so you can then build that into the query - where the creation_date >= date()-30

SELECT Contacts.ContactID, Calls.CallDate
FROM Contacts INNER JOIN Calls ON Contacts.ContactID = Calls.ContactID
GROUP BY Contacts.ContactID, Calls.CallDate
HAVING (((Calls.CallDate)>Date()-30) AND ((Contacts.CreatedDate)>=Date()-30));

 

[mafhobb]

Yes, you are right...I was trying to figure out a way to do it without having a "customer added date" field in the Contacts table as the db has been in place for a while and has over 44000 customers already.

I have another piece of logic that might work...

-First run a query that returns the CustomerIDs that have called within the "X" period of time by using call date on the 2nd table.
-Then run a query that returns all the CustomerIDs that called anytime before the same "X" period of time.
-The next step would be to compare the results from those two queries and eliminate the CustomerIDs that match on the shorter query (within "X" time)
-Finally count how many CustomerIDs are left in the query.

Complex, but possible, right?

mafhobb

 

[Kiwiman]

Howzit

How about. Sorry not sure about the correct syntax so I amy have too many ")" - so please play around. Part of the criteria looks for Contact Id's that do not appear in calls prior to the 30 days and uses the NOT IN

SELECT Contacts.ContactID, Calls.CallDate
FROM Contacts INNER JOIN Calls ON Contacts.ContactID = Calls.ContactID
GROUP BY Contacts.ContactID, Calls.CallDate
HAVING (((Calls.CallDate)>Date()-30) AND ((Contacts.Contactid) NOT IN (SELECT Calls.Contactid FROM Calls WHERE (((Calls.CallDate)<Date()-30)))) ;

 

[mafhobb]

Well, that query seems to work fine but it takes over 7 minutes to go through the 77000 customers or so that the db has in it right now. It also lists the customers and the call they made. I wonder if there is any way to get simply a number instead of the list. Would this speed up the process?

mafhobb

 

[Kiwiman]

Howzit

It lists the call they made as you put this criteria in the select stmt in a few posts back, so I just modified your last solution. I did raise this subsequently.

What you can do is check that you have the correct indexes in place.

The code below will count the number of new contacts.

SELECT Count(Contacts.ContactID) As 'Contacts'
FROM Contacts INNER JOIN Calls ON Contacts.ContactID = Calls.ContactID
HAVING (((Calls.CallDate)>Date()-30) AND ((Contacts.Contactid) NOT IN (SELECT Distinct Calls.Contactid FROM Calls WHERE (((Calls.CallDate)<Date()-30)))) ;

 

[Kiwiman]

Howzit

I'm not sure if this syntax will work any faster.

SELECT Count(Contacts.ContactID) As Contaxts
FROM (Contacts INNER JOIN Calls ON Contacts.ContactID = Calls.ContactID) LEFT JOIN (SELECT Distinct Calls.[ContactID] FROM Calls WHERE Calls.[DateCreated] <Date()-30) AS T2 ON Calls.ContactID = T2.ContactID
WHERE (((Calls.DateCreated)>=Date()-30) AND ((T2.ContactID) Is Null));

 

[mafhobb]

let me try...