Results 1 to 3 of 3

Thread: Dates question

  1. #1
    Join Date
    Aug 2013
    Posts
    5

    Unanswered: Dates question

    I have a table with records that showing 2 and sometimes 3 times.

    Pt Id Date
    X 1 12/1/13 12:30
    X 1 12/1/13 12:35
    Y 4 12/2/13 12:35
    Y 4 12/3/13 12:00
    Y 4 12/2/13 10:00

    I am looking to show the date across like this:

    Pt Id Date1 Date2 Date2
    X 1 12/1/13 12:30 12/1/13 12:35
    Y 4 12/2/13 12:35 12/3/13 12:00 12/2/13 10:00

    I tried using min and max but it only works where there is 2 records.

  2. #2
    Join Date
    Nov 2010
    Posts
    84
    Example "ConcatenatedField.zip" can be downloaded on the website Zoran Eremija CV

  3. #3
    Join Date
    Aug 2013
    Posts
    5

    Date

    Thank you.

    I found this code that worked.

    If we have a table with 3 columns: MyID, MyPT and MyDate

    TRANSFORM First(MyDate) AS TheDate
    SELECT MyPT, MyID
    FROM(
    SELECT T1.MyPT, T1.MyID, T1.MyDate, Count(T2.MyDate) AS MyOrder
    FROM MyTable AS T1 INNER JOIN MyTable AS T2 ON (T1.MyID = T2.MyID) AND (T1.MyPT = T2.MyPt)
    WHERE T1.MyDate >= T2.MyDate
    GROUP BY T1.MyPt, T1.MyID, T1.MyDate)
    GROUP BY MyPT, MyID
    PIVOT "Date" & MyOrder;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •