Can you please help?
I want a query or SQL code to aggregate data by first_date, Second_date, third_date to be like this:
Can you please help?
I want a query or SQL code to aggregate data by first_date, Second_date, third_date to be like this:
Welcome to the site. If you attached an image, it's didn't come through. Aggregation is typically done with a totals query, so you may want to search on that.
Consider:
TRANSFORM First(MyTable.Visit_Date) AS FirstOfVisit_Date
SELECT MyTable.MRN
FROM MyTable
GROUP BY MyTable.MRN
PIVOT "Visit_Date_" & DCount("*","MyTable","MRN='" & [MRN] & "' AND Visit_Date<#" & [Visit_Date] & "#")+1;
Is data multi-year? No more than 3 visits per MRN?
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Thank you for your replyConsider:
TRANSFORM First(MyTable.Visit_Date) AS FirstOfVisit_Date
SELECT MyTable.MRN
FROM MyTable
GROUP BY MyTable.MRN
PIVOT "Visit_Date_" & DCount("*","MyTable","MRN='" & [MRN] & "' AND Visit_Date<#" & [Visit_Date] & "#")+1;
Is data multi-year? No more than 3 visits per MRN?
Sometimes the number of visits is less than 3 and sometimes are more