Max Date Across several columns

[holgi123]

Hi Everyone,

I keep track of campaigns(C1, C2, C3) in a a dbase and I want to retrieve the last contact date for telemarketing agents. My structure (only partially dispayed) is like this:

C1FirstContactDate, C1SecondContactDate, C1ThirdContactDate
10/11/2005 12/11/2005 15/11/2005

The query should throw out under a new field called "LastContactDate" the date 15/11/2005. If the agent needs to call customer 4th time, they would overwrite the C1FirstContactDate.
Once I know the solution for this, I hope I can utilize the answer to do the same across all 3 campaigns (each having three contact dates [C1FirstContactDate, C1SecondContactDate, C1ThirdContactDate, C2FirstContactDate, C2SecondContactDate, C2ThirdcontactDate,.....etc.].

I read a lot of posts but the Max-Fucntion only relates to basically one column and not across several columns.

Thank You to everyone who can help me on this.

 

[cmmrfrds]

If you are still developing the database, it is best to go back and normalize the data so that you don't run into the type of problem you are encountering. The way to handle this problem in SQL is to Union each of the fields and then do a Group By.

Generalized example.
SELECT Max(ut.fld) AS MaxOffld, ut.ID
FROM testtable AS A INNER JOIN
[SELECT TestTable.ID, TestTable.field1 as fld
FROM TestTable
Union
SELECT TestTable.ID, TestTable.field2
FROM TestTable
Union
SELECT TestTable.ID, TestTable.field3
FROM TestTable
Union
SELECT TestTable.ID,TestTable.field4
FROM TestTable
]. AS ut ON A.ID = ut.ID
GROUP BY ut.ID;

 

[SeeThru]

[addition to cmmrfrds]

and once you have the union query (for the current record) select the highest date - I personally would use a recordset sort and select i.e. sort descending, check if the first record exits and has a value and return it.

 

[PHV]

Anyway, have a look here:

http://r937.com/relational.html

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

 

[holgi123]

Hi Everyone,

thank you for all your suggestions. It seems that I have to do a lot of work to set it better up in the future.

However, with Michael's first suggestion about using a Variant and the approach outlined by cmmrfrds I could get two alternatives working and the results were the same; so for the time being I have solved the problem.
Nevertheless I will take all advice on board and see if I can improve the db design.

Again, thank you for helping me solving this problem.

Cheers

 

[holgi123]

Hi,

hopefully someone can help me again.

I mentioned before everything is working and it is with one problem. I can not sort the results, which are dates.

The suggested Union Query from cmmrfrds works, but it is a bit slower.

MichaelRed suggested using this pub function and it works great by putting it into my query field like

LastContactDate: MaxVal1([field1], [field2] [field3]).

=========================
Public Function MaxVal1(ParamArray Vals() As Variant) As Variant
Dim x As Variant
Dim MX As Variant
MX = Vals(0)
For Each x In Vals
If x > MX Then MX = x
Next
MaxVal1 = MX

End Function
========================

The problem is the dates get sorted by first digit, i.e. instead of

22/11/2005
12/11/2005
13/06/2005
the result is

12/11/2005
13/06/2005
22/11/2005


I tried so many things with format() in query field, property in table fields etc. All are set to "short date" but it somehow does not work. I understand that variant type eliminates this, but how do I get it back so when I later choose this field for sorting and the date order shows properly?

Many thanks again for any help.

 

[MichaelRed]

The conversion (query results) are placing STING results into your recordset. Not that the function returns a Variant type, which is indeterminiant for the query calling the procedure. You can wrap the call to the function in CDate, to have the return value as a daty type, which should sort properly.




MichaelRed

 

[holgi123]

Hi Michael,

thank you again, now it works all okay with using CVDate() and I can sort the way I want.

Much appreciated you help.

Regards