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

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
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
 
Upvote 0
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,
 
Upvote 0
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...
 
Upvote 0
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:
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,214,285
Messages
6,118,674
Members
448,845
Latest member
MrEbzz

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
Back
Top