Using the Results from Qry as Columns IN new table

[Jimgarry]

Hi, thanks in advance. I would like to use the rows of a tabel to be the columns of a table. I have looked up this question and find normalized views and unions. I do not understand where you build these unions at.

A cross tab file may work for what I am looking for but I will never know how many rows to convert to columns. so if you could please point me to a working sample of normalized data I would be greatful. I found post from dhookom but I did not fully understand it. Thanks again

my data looks like this at the present

empnum hrs othrs weekending
00001 40.00 5.0 01/10/2003
00002 40.00 4.5 01/10/2003
00001 40.00 4.0 01/17/2003
00002 40.00 4.0 01/17/2003
...

what I want to do with it is

epnum r01/10/2003 O01/10/2003 r01/17/2003 O01/17/2003
00001 40.00 5.0 40.00 4.0
00002 40.00 4.0 40.00 4.0


Thanks again
Jim

 

[JeremyNYC]

Jim,

Especially when you don't know how many rows to convert to columns, the crosstab query is the tool you want. But you have a wrinkle, assuming that column that's always 40.00 in your example won't _always_ be 40.

In a crosstab query you assign as many fields as you want as row headings, one field as a column heading, and one field as the "value", which will get placed at each intersection of the row values and the column value. In your example, you've got two "value" fields, "hrs" and "other".

If you can live with just displaying one of those, say "other", this won't be a problem.

If you need them both in one query, it will be a lot more complex.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[Jimgarry]

Thanks Jeremy yes it is going to get a lot more complex. I think I will use 2 cross tabs to get my results one for regular and one for over time.

Thanks for your assistance

Jim