Windows XP 
Access 2000
I have been researching this on the web, and have seen a bunch of stuff on union queries and I am familiar with crosstab queries.
Basically I want to do within Access exactly what I can do in Excel as far as copying and transposing a data selection. Below is the table I am trying to transpose (first table). I want the Audit 1, Audit 2, Audit 3, Audit 4... to be in the rows with the Question being each column header (see the second table I posted). I will have hundreds of Audits in a file, it is not limited to 5. It's making me crazy I can't figure it out. Ultimately I will add a "question" to the transposed dataset that will be a value of 1 so that for each audit (which is representative of a patient event) I will be able to quantify queries which are question based. I did try adding a value of one and working through this as a crosstab in Access but I didn't get anywhere with it.
So here is my untransposed data table:
Question Audit 1 Audit 2 Audit 3 Audit 4 Audit 5 Date of event 1/1/12 1/2/12 1/3/12 1/4/12 1/5/12 Time of event 6:51 AM 11:40 AM 6:26 AM 8:18 AM 9:46 PM Nursing unit a b c d e POCT BG value 1 2 3 4 3 Type 1 2 other 1 2 Provider Bob Mike Sue Ann Joe Endocrine no no no no no Diabetes Medications given within 24 hours of event Levemir no no no no no Novolog scheduled meal dose no no no no no Novolog correction scale yes yes yes yes yes U-500 no no no no no NPH no no no no no Mixed Insulin no no no no no Insulin Pump no no no no no Sulfonylureas (Giipizide, Glucotrol, Glimepiride, Glyburide): no no no no no Meglitinides (Nateglinide, Repaglinide) no no no no no Other yes no no no no If other, please describe Trulicity 7/29/17 no no no no no no no no no Nutritional intake prior to hypoglycemia NPO PO diet PO diet PO diet PO diet Patient's last documented weight in pounds 0.00 0.00 0.00 0.00 0.00 no no no no no Associated risks no no no no no eGFR <30 no no no no no Dialysis no no no no no Corticosteroids (Methylprednisolone, Solu-Medrol, Solu-Cortef, Prednisone) yes no no no no Were home insulin doses continued in the hospital? no no no no no no no no no no Treatment provided no no no no no 4-oz. juice yes no no no no Glucose gel no yes yes yes yes D50% IVP no yes yes yes yes Glucagon no no no no no Other no no no no no If other, please describe no no no no no Not documented no no no no no Follow up POCT done in 15-30 minutes? no yes yes yes no no no no no no Provider notified of hypoglycemic event yes yes yes yes yes no no no no no Event documented by nurse yes yes yes yes yes no no no no no Causative factor no no no no no Home insulin continued no no no no no High basal dose no no no no no Sulfonylurea no no no no no High correction dose no no no no no Insulin not synchronized with food no no no no no Correction not admin. within 30 minutes of BG test no no no no no Med error no no no no no Other/Comments had long agent name pancreatic cancer creative
(Here is what I would like the final access output (table) to look like (via the access query I am hoping to compose - I did the below transpose in Excel):
Question Date of event Time of event Nursing unit POCT BG value Type Provider Endocrine Diabetes Medications given within 24 hours of event Levemir Novolog scheduled meal dose Novolog correction scale U-500 NPH Mixed Insulin Insulin Pump Sulfonylureas (Giipizide, Glucotrol, Glimepiride, Glyburide): Meglitinides (Nateglinide, Repaglinide) Other If other, please describe Nutritional intake prior to hypoglycemia Patient's last documented weight in pounds Associated risks eGFR <30 Dialysis Corticosteroids (Methylprednisolone, Solu-Medrol, Solu-Cortef, Prednisone) Were home insulin doses continued in the hospital? Treatment provided 4-oz. juice Glucose gel D50% IVP Glucagon Other If other, please describe Not documented Follow up POCT done in 15-30 minutes? Provider notified of hypoglycemic event Event documented by nurse Causative factor Home insulin continued High basal dose Sulfonylurea High correction dose Insulin not synchronized with food Correction not admin. within 30 minutes of BG test Med error Other/Comments Audit 1 1/1/12 6:51 AM a 1 1 Bob no no no yes no no no no no no yes Trulicity 7/29/17 no NPO 0.00 no no no no yes no no no yes no no no no no no no no yes no yes no no no no no no no no no had long agent name Audit 2 1/2/12 11:40 AM b 2 2 Mike no no no yes no no no no no no no no no PO diet 0.00 no no no no no no no no no yes yes no no no no yes no yes no yes no no no no no no no no no pancreatic Audit 3 1/3/12 6:26 AM c 3 other Sue no no no yes no no no no no no no no no PO diet 0.00 no no no no no no no no no yes yes no no no no yes no yes no yes no no no no no no no no no cancer Audit 4 1/4/12 8:18 AM d 4 1 Ann no no no yes no no no no no no no no no PO diet 0.00 no no no no no no no no no yes yes no no no no yes no yes no yes no no no no no no no no no creative Audit 5 1/5/12 9:46 PM e 3 2 Joe no no no yes no no no no no no no no no PO diet 0.00 no no no no no no no no no yes yes no no no no no no yes no yes no no no no no no no no no
Worse comes to worse I can just transpose it in Excel, but I would love to write it into the access database I am creating.
Thank you for any help, it is appreciated more than you know!!! It is making me crazy I can't get my mind around this. I believe I will have to normalize my data via a union query, but I am not quite understanding that process and I am not familiar with union queries.
Reply With Quote
Originally Posted by June7
