Crosstab Query: Column Heading problem w/ Dates

[Bob]

running access 2k;

don't know why Xtab queries seem to be SO hard to understand....
BUT...

I have a REALLY simple table:
contact_ID call_date
1 5/1/07
1 5/10/07
1 5/15/07
2 5/3/07
2 5/5/07
3 5/16/07

all I want is a query that produces:
contact_ID 1st_call_date 2nd_call_date date3
(etc...)

clearly, I need a datediff formula to create the date 1, 2 3 etc...
But I can NOT get the 1st date from 1 person, to line up in the same
column as the 1st date from another person.

Can this be done?
TIA - Bob

 

[Michel Walsh]

Yes, but you have to rank your call_date, per contact_id.


TRANSFORM Last(call_date)
SELECT contact_ID
FROM youKnowWhere
GROUP BY contact_ID
PIVOT DCount("*", "youKnowWhere", "Contact_ID=" & contact_id & " AND
CallDate>=" & Format(CallDate, "\#mm-dd-yyyy\#") )


should do. I assume the table name was youKnowWhere.



Hoping it may help,
Vanderghast, Access MVP

 

[Bob]

Yes, but you have to rank your call_date, per contact_id.

TRANSFORM Last(call_date)
SELECT contact_ID
FROM youKnowWhere
GROUP BY contact_ID
PIVOT DCount("*", "youKnowWhere", "Contact_ID=" & contact_id & " AND
CallDate>=" & Format(CallDate, "\#mm-dd-yyyy\#") )

should do. I assume the table name was youKnowWhere.

Hoping it may help,
Vanderghast, Access MVP

Michael;

Thank you very much!
that did the trick....

The solution always looks SO easy! I don't know why I get hung up on
these things.
Actually, I probably would have done better had I worked in the sql
window, rather, than trying to figure out all the junk in the query
window.... which is about as confusing as it could possibly get!

TX again, Michael...

Bob