oldest date criteria 1

[patrichek]

Hi,
I'm trying to build an update query to change my received date in my contacts table to the oldest(or first) date in my call log table for each particular record taken from a website lead

I'm unsure what to specify for criteria to get oldest date in my query.

here's my code so far:

SELECT Calls.ContactID, Calls.CallDate, Contacts.Source
FROM Contacts INNER JOIN Calls ON Contacts.ContactID = Calls.ContactID
WHERE (((Contacts.Source)="website"));

thanks for any help provided!

 

[Melagan]

Take a look at the Min() and Max() functions. You should be able to do a Min([YourDateField]) to get the lowest date in your recordset. You can use an update query to update [YourUpdateField] to Min([YourDateField]).


~Melagan
______
"It's never too late to become what you might have been.

 

[patrichek]

hi,
i need a little more help, i'm a beginner. can you offer more?
like how to insert it into the code above?

thank you!

 

[Melagan]

In case you needed, here is an SQL example:

UPDATE Contacts
INNER JOIN Calls on Contacts.ContactID = Calls.ContactID
SET Contacts.[Received_Date] = Calls.[CallDate]
WHERE Contacts.[Source]="Website"

Typed, not tested. Be careful running update queries too; once you run them, you can't go back!


~Melagan
______
"It's never too late to become what you might have been.

 

[Melagan]

RED FLAG I did forget something; this is more complete, but actually doesn't work. Someone else is gonna need to jump in here; I got an error when trying to use the Min() Function in the SET clause. I believe this is what we need, but it's not executed quite property. I'm a novice myself =P

UPDATE Contacts
INNER JOIN Calls on Contacts.ContactID = Calls.ContactID
SET Contacts.[Received_Date] = Min(Calls.[CallDate])
WHERE Contacts.[Source]="Website"

I know Min() is an aggregate function and is therefore causing the problem in the SET clause. I'll play with it a bit more but hopefully one of the pros here will have the answer shortly.


~Melagan
______
"It's never too late to become what you might have been.

 

[patrichek]

Hi,
thanks for trying for me. I really don't need the update part yet, I'm still trying to get the min date first then i'll apply the update once everything else is set. I guess i should have just said i'm trying to build a query.

 

[patrichek]

ok, i've been messing with it a little and this gives me the date 1/1/05 for every record.....hmmm...any ideas?

SELECT Calls.ContactID, (SELECT Min(calldate) FROM calls) AS [date], Contacts.Source
FROM Contacts INNER JOIN Calls ON Contacts.ContactID = Calls.ContactID
WHERE (((Contacts.Source)="website"));

 

[PHV]

SELECT Calls.ContactID, Min(Calls.CallDate) AS [date], Contacts.Source
FROM Contacts INNER JOIN Calls ON Contacts.ContactID = Calls.ContactID
WHERE Contacts.Source = 'website'
GROUP BY Calls.ContactID], Contacts.Source;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[patrichek]

Awesome! thank you PHV! (again!)

 

[patrichek]

ok, one last question. Everything is working, but is it possible to set criteria where it would skip any calldate that is newer than the recieved date?

The problem i may run into when i do the update query is if the calldate is newer than the received date, it will change received date to the newer calldate which i do not want.

Thanks!

 

[Melagan]

SELECT ...
FROM ...
WHERE Contacts.Source = 'website' AND int([calldate]) > int([received_date])
GROUP BY ...

Just an idea.



~Melagan
______
"It's never too late to become what you might have been.

 

[patrichek]

PHV,
maybe that didn't work, when i do the update the fields don't change. here's my code:

UPDATE Contacts INNER JOIN Calls ON Contacts.ContactID = Calls.ContactID SET Contacts.[Date received] = [calls].[calldate]
WHERE (((Contacts.Source)='website'));

what's wrong?

thank you

 

[patrichek]

ok, my bad, its working
i was looking at a record that wasn't a website lead.
thanks again for all your help!