Query to Redistribute a Table

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

plgerh@gmail.com - 16 Jan 2008 10:27 GMT

Hi
I am struggling with how to query a table with the current structure:
Field1 Field2 ...Field14 Jan Feb Mar Apr...
x y ...z 10 15 13 16...
etc,
It represents sales per month to customers per delivery adress,
product and so forth.The numbers under each month respresents the
sales for that month.
I need to be able to query so I can see all detalis (ie fileds 1-14)
per row per month, ie redistribute that original table so I can use
month as a field and still use all the original fields. My new table
needs to be something link
Month Field 1 Field2 ... Field14 New Field=Sales
Jan X Y .... Z 10
Feb X2 Y2 .....Z2 15
etc
For simplicity I have only illustrated one value per month but there
are sales per month as Reveniue, as kgs, as margin, etc

Any help to bring me in the right direction to query this would be
appreciated,
thanks
Lars

Reply to this Message

Jeanette Cunningham - 16 Jan 2008 10:47 GMT

Lars,
here is an answer to a similar problem from the discussion group.
You can see the fields in the other person's wide flat table.
Hope this makes sense.
start of answer
-----------------------
Well... actually you don't. It's not too hard to move data from a wide-flat
table into a tall-thin one. YOu can use a "Normalizing Union Query" such as

SELECT <primary key field> "Preposition_I" AS WordType, Preposition_I AS
WordValue
FROM Declension
WHERE Preposition_I IS NOT NULL
UNION ALL
SELECT <primary key field> "Instrumental_I" AS WordType, Instrumental_I AS
WordValue
FROM Declension
WHERE Instrumental_I IS NOT NULL
UNION ALL
<etc. etc. through all the fields>

John W. Vinson [MVP]
--------------------
end of answer

Jeanette Cunningham

> Hi
> I am struggling with how to query a table with the current structure:
[quoted text clipped - 19 lines]
> thanks
> Lars

Reply to this Message

Vincent - 16 Jan 2008 10:56 GMT

Try by "grouping" yours results by months. (ie SELECT ... FROM ... GROUP
BY ... )
I 'm not it's run in this syntax but it's possible to have what you want.

> Lars,
> here is an answer to a similar problem from the discussion group.
[quoted text clipped - 49 lines]
>> thanks
>> Lars

Reply to this Message