Transform Wide to Long Format?

[shellp]

Hello,

I have data in wide format where every hospital visit is one line of data in a table entitled DAD2. The fields are: inst, chartno, acctno, disdate, dx1,dxtyp1, dxpre1, dxclus1,dx2, dxtyp2,dxpre2,dxclus2 etc. up to 25. How to I get the data to be in long format (in table Diagnoses):
inst
chartno
acctno
dx
dxtyp
dxpre
dxclus

I would also like to add occurrence number (which is the 1, 2 etc in the name above) as well as the total number of occurrences. Note that there is room to include 25 diagnoses so the "total_occur" field will represent the number of diagnoses for the record. How can I achieve this? Thanks.

Shelley

 

[Micron]

Do yourself a favour and learn db normalization before doing anything else. Access is tall, not wide like Excel. Data is kept in tables; each table is an entity and each field is an attribute of that entity. It may be a bit difficult at first, but it's necessary to grasp that.

Your info it too cryptic to give much in the way of focused guidance so I'll be general. If a table is about Purchase Orders, then the attributes (not field names) might be
- PO_ID (usually an autonumber field)
- PO number
- creator
- approver
- create date
- approve date
- complete date
Items, like binders, pencils etc. are not attributes of the PO. Those go into tblPOline (or tblPO_Line if you prefer). The "link" is made by including PO number in the line items table via PO_ID, as a foreign key. To elaborate, you don't save the po number in the child (related) table, you save the record id (PO_ID).

In short, you probably cannot just list all of the fields/columns you have in a tall format without separating the entities. I recommend you review all of the links in the following...

Best approach for multiple look ups?

So, I want create a form that will utilise multiple datasets and am looking for some advice on the best way to do this. In as basic an explanation as possible… Step 1: I enter a postcode Step 2: It automatically looks up the LSOA from a very large dataset (too large for one sheet, spreads...

www.mrexcel.com

 

[Micron]

In case you know all that, maybe you have the correct structure but need to know how to get wide data into a tall format. In that case, queries will do it. Depends on what the data and target tables look like. It could be a mix of append, update and union queries.

 

[shellp]

Thank you for the quick response, Micron. I do know about normalization etc. and my request is an alternate to having SAS that allowed me to very quickly extract the data elements in the wide format to long in separate tables and then use the table to report from via reporting software. I was hoping/thinking that I could write code, even in SQL, to assist with this task but didn't know where to start.

In SAS, I would have created a transposed table per field i.e. diagnosis (dx1-dx25), diagnosis type (dxtyp1-dxtyp25), diagnosis cluster (dxclus1-dxclus25) and diagnosis prefix (dxpre1-dxpre25) and in each of these tables would include the inst, chartno, acctno, and disdate. SAS also allowed me to change the name of the variable, for example dx and identify the variable in question as dx1-dx25. Then I would merge all the tables together to get a table showing diagnoses in long format with each of the fields identified in the first line. Is this possible in SQL/Access? Thanks.

 

[Micron]

So, if you have 25 diagnosis type fields and want to get those into one field in tblDiagnosisType you could do that with a Union query. Not sure if the field limit is still 255, which is way below 25 so no issue there. However, if any of the fields are memo/long text (which is the Access name for fields that hold abt 2Gb of data) then you will have an issue. Likely Access will bark that the query is too complex. I think what you don't want is 25 tables, if that's what you meant, but perhaps you do for purposes of final output. In that case it would take 25 MakeTable queries to get 25 tables. For that I think I'd be using VBA to loop over the wide table fields and output each as a table. Access has a 2Gb file limit, including overhead so if you're talking millions of records in each table you might hit that - especially if any are memo fields.

Do you really want 25 tables from 25 fields in one source table?

 

[shellp]

Hello,

Thanks for sticking with me on this. No, I don't want 25 tables. Each diagnosis (and there could be up to 25 in one record - with record unique identifier being account number (acctno) has multiple components: diagnosis code, diagnosis type, diagnosis cluster, diagnosis prefix. SAS would have be first take the diagnosis code and transpose it in a table with inst, chart number, account number and then the diagnosis being long, not wide where each is now one row i.e.
inst
chartno
acctno
disdate
dx
then a table for dx type that is:
inst
chartno
acctno
disdate
dxtyp
then a table for clusters:
inst
chartno
acctno
dxclus
then a table for prefixes:
inst
chartno
acctno
dxpre

Then all 4 tables would be merged by linking chartno and acctno so I end up with:
inst
chartno
acctno
disdate
dx
dxtyp
dxclus
dxpre

Is this possible?