Aggregate Data by first, second, third ...

[Mbmohamed]

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:

[pbaldy]

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.

[Mbmohamed]

Thanks for your reply and and appreciate your help
Best regards

[June7]

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?

[Mbmohamed]

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?

Thank you for your reply
Sometimes the number of visits is less than 3 and sometimes are more