convert vertical report to horizontal

[joebloeonthego]

Not the best description I know, but the problem is this:

I have a report that comes out basically like:

PN: 123456
puchase Jan 1 blah
sale Feb 23 blah
order Jan 19 blah
purchase Mar 02 blah

PN: 123457
etc. etc.


And would like to have it read out like:

PN Current next week week afer that week after that
------------------------------------------------------------
1234 blah blah blah blah
1235 blah blah blah blah

basically convert a vertical to horizontal. I would prefer to go from the cursor used to do the original vertical report, because I don't really know how they came up with it - if I had to I could probably build it up myself, but it'd be easier not to. or maybe it wouldn't be.. that's what I'm asking I guess.

Do I make a temp table with a bunch of extra fields for each week and then do a group by on pn putting the data in the new fields? I want to convert a bunch of lines into one line - I've never done this before, but I feel it's some sort of standard DB excercise you learn in school or something...

 

[R17]

hi,

i had the same problem like you.. what i did was to make a cursor for that table.. i had 24 fields.. guess there's no other way to have the report that way..

HTH

 

[torturedmind]

have you tried experimenting with crosstab queries?

kilroy

 

[jimstarr]

"Do I make a temp table with a bunch of extra fields for each week and then do a group by on pn putting the data in the new fields?"

I'd say yes, this may be the easiest way.

CREATE newcursor (pn c(4), wk1 c(20), wk2 c(20), etc.)
SELECT oldcursor && assume in pn order
prevpn = ''
SCAN
IF oldcursor.pn != prevpn
SELECT newcursor
APPEND BLANK
SELECT oldcursor
ENDIF
* here determine which date slot to put data into
REPLACE newcursor.wk3 WITH blah && for example
ENDSCAN


Jim

 

[joebloeonthego]

tortured mind - I'm checking those out in access right now.
jimstarr - I'll be trying that out after I finish investigating the crosstab stuff.

thanx!

 

[joebloeonthego]

I checked out crosstab queries.. funny I never knew about those.
But it seems as though you can only total one field? am I right for thinking that? unfortunately my data will be either in a supply field or a demand field - not a simple pos/neg value.

How I get it:

pn  ord_num      date     supply     demand    company
----------------------------------------------------
123 On Hand        -        10
123 Sal_Ord876   jan 1                 8         acme
123 Pur_Ord345   Jan 8      20
123 Sal_Ord346   Jan 9                 1         acme
123 Sal_Ord675   Jan 16                20        acme

How I'd like it:
                week          week          week
      curr      end Jan3      end jan10     end jan17
pn   stock    sup dem bal   sup dem bal   sup dem bal
------------------------------------------------------------
123   10           8  -2     20  1  17         20  -3

So is crosstab not possible? I'm guessing for this case it's easier to build a new temp table the way I'd like it and report from there.
Thanx much for the help!

 

[joebloeonthego]

jimstarr: I'm using your example and it's working out great. thanx!