Date quesion

[elamranii]

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.

[June7]

Denormalizing data can be tricky. Since a CROSSTAB won't get you the headers you want, only options I know involve VBA code.

One is to concatenate the data into a single string. http://allenbrowne.com/func-concat.html

Another is to write data to a temp table: http://forums.aspfree.com/microsoft-...ry-322123.html

[Dal Jeanis]

Assuming this table structure:

MyTable
  MyPT
  MyID
  MyDate

And this data:



Then using this Crosstab query:

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;

Produces this output:


I believe it will work for any number of dates per MyPT/MyID combination.

[June7]

WOW, Dal, you are a magician! I NEVER would have seen that. And works for any number of dates, as long as won't result in more than 255 columns.

[Dal Jeanis]

Thanks. I didn't see it myself, actually.

The reason it's not aircode - I actually had to build the table and the queries - is because I know I'm fuzzy on Crosstabs, and I really do want to understand their potential. It bugs me that I can't just type them in and have them work, yet.

Here's how the magic got made:

Since I wasn't confident I could just type in a correct solution, I had to prove my syntax was right. I thought I'd seen something like that on Allen Browne's site. http://allenbrowne.com/ser-67.html

Browne's examples were not quite detailed enough, but the way he formed the Transform statement on the final example showed me that it could be done. So I knew that if I could somehow get the count onto the record, then I could PIVOT on count to spread out the dates. Luckily, the SQL for getting the COUNT wasn't too tricky - I've had to use that sub-select trick a bunch of times, and choosing between comparison operators (>, >=, <=, <) is the only testy bit there.

After the inner select was returning the desired records, I used a MakeTable to turn that query's results into a table. The purpose of this was that Access can be REALLY finicky about crosstabs, and I didn't want to be getting unneeded (and highly confusing) errors because the crosstab was rejecting the query for arbitrary syntactic reasons.

I used that table, literally by trial-and-error, to figure out where each of the fields needed to be slotted into the crosstab, to produce the desired results.

Then I pasted the query itself back into the crosstab structure in place of the table, and made sure the code all lined up and still produced the desired results.

Voila, magic.

Yeah, I can get kicked out of the magician's union for explaining this stuff, but I figure that on this forum, the "how" is much more important than the resulting code. Follow the above steps, and anyone who can read SQL can do this particular trick.

[June7]

Oh yeah, I've seen that Allen Browne article, just didn't make the connection you did. I don't know I will have ever be able to apply that method. I have one situation that seems perfect for it but there is risk of exceeding 255 fields, hasn't happened yet (133 is the highest I could find), but I know if I modified code, it would.

[Dal Jeanis]

Heh heh.
If you don't mind losing the excess columns, then ...

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
   HAVING Count(T2.MyDate) < 255)
GROUP BY MyPT, MyID
PIVOT "Date" & MyOrder;

[elamranii]

Thank you so much. the above solution worked.