How to transpose data ; make one record to 9 records and copy to excel template starting from A3

[stalk]

How can I convert my table 1 to table 2 format using VBA code?



It's like converting one line of record into 9 records. Each test will have numeric result (~Ct) and text result like positive (>0)or negative( =0)
If all the 4 ~Ct values final rRT should be 0.


Thank you in advance

[June7]

You want to do this with data from an Access table? A UNION query can rearrange data then can use Export tool or VBA with TransferSpreadsheet to export query.

[stalk]

its access table and I need to process at least 5 files each day and export to excel template. Number of records vary each time between 3 and 12. That means out put might have 27 to 108 records.
can you give me an example of how the union query work?

[June7]

SELECT ID, "t1_rRT_ct" AS Test, t1 AS Result, qc FROM tablename
UNION SELECT ID, "p1_rRT_ct", p1, qc FROM tablename
UNION SELECT ID, "h1_rRT_ct", h1, qc FROM tablename
UNION SELECT ID, "rp_rRT_ct", rp, qc FROM tablename
UNION SELECT ID, "t1_rRT, IIf(t1=0,"Negative","Positive"), qc FROM tablename

UNION SELECT ID, "p1_rRT, IIf(p1=0,"Negative","Positive"), qc FROM tablename
UNION SELECT ID, "h1_rRT, IIf(h1=0,"Negative","Positive"), qc FROM tablename
UNION SELECT ID, "rp_rRT, IIf(rp,"Negative","Positive"), qc FROM tablename
UNION SELECT ID, "rRT", IIf(t1+p1+h1+rp=0,"Negative","Positive"), qc FROM tablename;

This is essentially normalized structure data should have been in.

There is no query builder/designer for UNION - must type or copy/paste in SQL View.

[stalk]

You ARE GENIUS. and you made my day today.
Thank you so much for simple and effective solution. Will play with real data an update you

[stalk]

Thank yo so much again. It's working like a charm and using it at least 10 times a day.I could not stop appreciating you all the times I use it for your self less service.