Change table from "Flat" to "Wide"

[jkeyes]

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?

 

[giacomo]

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]

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]

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]

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.

 

[VicRauch]

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.