How to show records with multiple dates

  • Thread starter Thread starter Carol
  • Start date Start date
C

Carol

I have people in a database that have multiple dates
attached to their records. For instance, one person can
have three different "diary" dates associated to their
case. I want to write a query that shows the person's
name ONCE and the three dates in separate columns.

Right now when I run the query, it shows me the person's
name all three times along with the dates. Is there any
way to restrict this so it shows the name once and the
dates in the following columns without showing the name
all three times?

Thanks!
 
Hi,


Sure. First, rank the dates:


SELECT a.ClientID, a.DateTime, COUNT(*) As Rank
FROM myTable As a INNER JOIN myTable as b
ON (a.clientID=b.ClientID) and (a.dateTime >= b.dateTime)
GROUP BY a.ClientID, a.DateTime



Save that query. Next, run the crosstab wizard on that query. Group on
ClientID, Pivot on rank, the grid value to display is the aggregate through
LAST (or MIN, or MAX, or FIRST, or SUM) of the DateTime field.



TRANSFROM LAST(q.DateTime) As TheValue

SELECT q.ClientID

FROM (
SELECT a.ClientID, a.DateTime, COUNT(*) As Rank
FROM myTable As a INNER JOIN myTable as b
ON (a.clientID=b.ClientID)
AND (a.dateTime >= b.dateTime)
GROUP BY a.ClientID, a.DateTime
) AS q

GROUP BY q.ClientID
PIVOT q.rank



That's all.


Hoping it may help,
Vanderghast, Access MVP
 
Back
Top