Change table from "Flat" to "Wide"

jkeyes

Active Member
Joined
Apr 15, 2003
Messages
343
This is a bit of a continuation of this post, but I'm referring to a different procedure and I didn't want this to get lost in the shuffle...

Using Norie's code I got a "Wide" table into a "Flat" format to follow the rules of normalization. I've run my query on my flat table, but the results are also "Flat"... and I need them back to the Wide format...

The resulting query fields are arranged as follows:

Directory, Demographic, Value

with Directory being a 6 digit number, Demographic being the name of the demographic being measured (e.g. population, number of households, etc.), and Value being that measurement.

So, what I'm looking to do is have Directory remain in Field 1, but have the various Demographics be the field headers going out to the right ("Wide"), and the values in the appropriate cells. For example:

Directory, Population, Households, Distribution
123456 , 150000 , 80000 , 90000
456789 , 100000 , 75000 , 80000


Any thoughts on how to get there?
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

giacomo

Well-known Member
Joined
Feb 20, 2002
Messages
1,796
jkeyes,

You need to create a crosstab query. Directory should be your row data, Demographic your column data, and [Value] your value data.

Try using the crosstab wizard and post back if you have any problems.

hth,
Giacomo
 

VicRauch

Well-known Member
Joined
Mar 23, 2006
Messages
2,032
Just a clarification for using words. Your "wide" file is normally called a "flat" file because it has ALL the data in one file, therefore "flat." This "flat" comes from the fact there is no depth or various "levels" that are present in a relational database that has normalized tables.
HTH,
 

jkeyes

Active Member
Joined
Apr 15, 2003
Messages
343
That worked... thanks giacomo!

Though, it did arrange the deomographic columns in alpha-numeric order, and I didn't necessarily want that, but I can live with it.

One last question (I think)... I would like access/run the "AddToFlat" sub-routine that Norie provided without having to go into the VB editor. But, when I choose Tools/Macros from the Access menu bar, it comes up blank (doesn't show AddToFlat as an option). Am I missing something?

Thanks again for all the help! I have learned a lot in the last day that I can continue to build Access knowledge on...
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
Just a clarification for using words. Your "wide" file is normally called a "flat" file because it has ALL the data in one file, therefore "flat." This "flat" comes from the fact there is no depth or various "levels" that are present in a relational database that has normalized tables.
HTH,
Vic

The naming issues my fault.:oops:
 

VicRauch

Well-known Member
Joined
Mar 23, 2006
Messages
2,032
Norie,
Thanks! I'm still laughing. The little icon was best. I have done stuff like that I sure wished later that everyone could only forget it. Anyway, I really like sharing on this board because I see everyone being very helpful.
 

Forum statistics

Threads
1,137,335
Messages
5,680,887
Members
419,937
Latest member
Talic

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top