Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
SOLVED

Creating a new record by combining multiple rows into a single record based on a unique identifier

Copper Contributor

 

Lokimon1_1-1700988629508.png

Hello,  I hope that you can help me with something that seems like it should be simple, but has me stumped!  I am trying to create a new table from the table above.  Each row is a vaccination record for a single patient. I would like to combine all records for a patient (in multiple rows) into a single record with additional columns, using the Hospital ID (patient ID) as the unique identifier for the new record.  The only difference between the rows is the 'Vaccination Date' with each row representing a new dose of vaccine.  So, for example, for the patient with Hospital ID# 37757,  the new table would have 3 date columns.  The maximum # of doses for any patient is 3.  I also have the table in Excel.  

The intended output is below.  I have several x 1000 records, so I can't do it manually.  

 

Lokimon1_1-1700990425835.png

 

thanks All

4 Replies
Please do NOT do this. It's a bad plan.
If you want to DISPLAY the data in the format described for reporting purposes ONLY, you can do that, but please don't mess up the data in a table that way.

A Crosstab query should give you the results you want for reporting purposes in a display.
best response confirmed by Lokimon1 (Copper Contributor)
Solution

@Lokimon1 

 

It turned out that a crosstab will not produce the exact layout shown in your screenshot. However, a query with appropriate subqueries should do so. Note that this requires no more than 3 vaccinations in a series for one individual. More extended series would require more complicated logic.

This sample accdb and queries should show you how to prepare the display for the data in the layout illustrated. 

 

 

@George Hepworth   

Dear George,   Thank you for your time and effort to assist me.  It is very much appreciated.  Your solution has worked very well. 

Congratulations on resolving the problem. Continued success with your project/
1 best response

Accepted Solutions
best response confirmed by Lokimon1 (Copper Contributor)
Solution

@Lokimon1 

 

It turned out that a crosstab will not produce the exact layout shown in your screenshot. However, a query with appropriate subqueries should do so. Note that this requires no more than 3 vaccinations in a series for one individual. More extended series would require more complicated logic.

This sample accdb and queries should show you how to prepare the display for the data in the layout illustrated. 

 

 

View solution in original post