Crosstab query - Please help. it has been a few days without sleep

Question

Crosstab query - Please help. it has been a few days without sleep

Viewing 15 posts - 16 through 30 (of 51 total)

You must be logged in to reply to this topic. Login to reply

RandMan-585256 Old Hand Points: 310 More actions · Answer 1

Since this is Access VBA why don't you use the cross-tab query type in Access?

"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 2

This is gonna be a monster SUM(CASE ...) query, but definitely doable as long as the column values are known. Have fun Jeff. Something like this is billable time where I come from! 😎

I suppose the OP could upgrade to SQL 2008 R2 and throw this at PowerPivot and VOILA! 😀

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 3

Test-170228 (12/2/2009)
Man, you get this right, and I am going to send you a cheque for some pizza money for sure or steak 😉

Hopefully, this will be the final question. When you run the proc, you pass parameters for the SQLPeriodIndex, theMarket and the Segment. What do you get if you use 2 of those parameter values as replacements in the obvious spots in the following code? Be sure to post the code you end up using as well as the results. Thanks.

SELECT Data_3.Tag, Data_3.Fact,

FROM Data_3

INNER JOIN SKU_CONTENTS

ON Data_3.Tag = SKU_CONTENTS.TAG

WHERE (((Data_3.Market)=[theMarket]) AND ((SKU_CONTENTS.SEGMENT)=[theSegment]))

GROUP BY Data_3.Tag,Data_3.Fact

The reason for the question is I believe the ACCESS query may be using the Data_3.Fact data as column headers in the output and this experiment will prove that one way or the other. Since I don't have your data, I need you to be my hands and eyes.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 4

OP: this would be SOOOO much easier if you would just give Jeff VPN access! 🙂 I also think you should be offering him at least $100/hour for his time on this one too - it goes wayyyyy beyond the norms of forum assistance ... 😎

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 5

Heh... it's alright, Kevin. I really don't want VPN access because if something goes wrong, then the "new guy" always gets the blame. Besides... there might be a couple of steaks involved. 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Test-170228 SSC Enthusiast Points: 136 More actions · Answer 6

Hi Jeff,

Attached is the screen capture for DATA_3, SKU_Content, and the query after I plugged in a replacement values for what you requested. I have at the bottom the actual values I used to open in notepad.

Let me know if you need anything else. Also, I really think you should hire Kevin as your agent. He is putting up a good fight for you ...

lol

Sam

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 7

Heh... I was right. And, I looked up what PIVOT and CHOOSE do (I don't even know how to spell "ACCESS") and I believe we can do this. By the way... I like New York Strip. 😛

I'll be back... I've got a little figuring to do.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 8

TheSQLGuru (12/3/2009)
This is gonna be a monster SUM(CASE ...) query, but definitely doable as long as the column values are known. Have fun Jeff. Something like this is billable time where I come from! 😎
I suppose the OP could upgrade to SQL 2008 R2 and throw this at PowerPivot and VOILA! 😀

Heh... I just realized... the OP does in fact have 2008. I wonder what rev he's at?

Hey Sam!!! What rev is your SQL 2008 installation at? Ask your DBA if it's at R2 or better....

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 9

Jeff, you silly person! My big smiley indicated that my pushing R2 was a JOKE. 🙂 I think it is in CTP3 stage or so ...

Clearly you have been too busy with real life to keep up with the machinations by the SQL dev team! hehehe

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Test-170228 SSC Enthusiast Points: 136 More actions · Answer 10

Hi Jeff,

This is what I have when I check for the version value on my server

"Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64) Mar 29 2009 10:11:52 Copyright (c) 1988-2008 Microsoft Corporation Express Edition with Advanced Services (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2) (VM) "

I didn't follow your last post. Why would the version of the server matter?

Thanks

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 11

TheSQLGuru (12/3/2009)
Jeff, you silly person! My big smiley indicated that my pushing R2 was a JOKE. 🙂 I think it is in CTP3 stage or so ...
Clearly you have been too busy with real life to keep up with the machinations by the SQL dev team! hehehe

Heh... joke's on me and for the very reason you stated. :blush: Guess there's no such thing as a "PowerPivot" (until I build one) either, huh? {walking out of room, sucking thumb, twiddling hair, looking for tall glass of Scotch} :pinch:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 12

Test-170228 (12/3/2009)
Hi Jeff,
This is what I have when I check for the version value on my server
"Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64) Mar 29 2009 10:11:52 Copyright (c) 1988-2008 Microsoft Corporation Express Edition with Advanced Services (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2) (VM) "
I didn't follow your last post. Why would the version of the server matter?
Thanks

Heh... not to worry... I'm the victim of my own ignorance (ouch!). I don't believe it will matter. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 13

Jeff Moden (12/3/2009)
TheSQLGuru (12/3/2009)
Jeff, you silly person! My big smiley indicated that my pushing R2 was a JOKE. 🙂 I think it is in CTP3 stage or so ...
Clearly you have been too busy with real life to keep up with the machinations by the SQL dev team! hehehe
Heh... joke's on me and for the very reason you stated. :blush: Guess there's no such thing as a "PowerPivot" (until I build one) either, huh? {walking out of room, sucking thumb, twiddling hair, looking for tall glass of Scotch} :pinch:

Hey, don't complain to me! I tried to get you $100/hour for your efforts on this but NOOOOOOO - you wanted to be the Martyr!! :-D:-P:w00t:

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 14

Heh... nope... I want the steaks. 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 15

Jeff Moden (12/3/2009)
Heh... nope... I want the steaks. 😛

Thought Pork Chops were your thing?? :hehe:

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service