Pivot Table View Query as Report?

[Therese G. Maddox]

Hi All!

I'm creating a Report and hitting a 3000 mile tall wall. I've been pointed
many directions, specifically Cross Tab queries and Multiple Column reports,
neither of which have worked for me (I've tried every which way until my eyes
and fingers are bleeding). But, I got somewhere today when I was able make
query look approximately what I wanted it to look for a report using the
Pivot Table view. If I could just pop this into a report and clean it up,
that would be grand. But alas, my life is difficult.

My question is, if I have created a query (any type of query, even a simple
one) and can view it the way that I want in the Pivot Table view, how can I
get this into a report???? I've TRIED using a Cross Tab Query, and my data is
not applicable (i.e. I have no data to TRANSFORM or aggregate, its all text).
All I'm trying to do is basically do what Excel does when you paste special
and TRANSPOSE.

Also, if I get lucky enough to be successful and get to the point where I
can use the PIVOT line in the SQL statement, it pulls from God only knows
and I get #'s instead of my field names. I have no idea what I'm doing
wrong.

HELP!!!

Therese

 

[KARL DEWEY]

Post example of your data and what you want the results to look like.

 

[Therese G. Maddox]

See below:

Table consists of 7 columns. I'll put my field names in so I don't get
confused ;-)

Value_ chain_ order: number
Value_ chain_ name: text
Sub_ value_ chain_ order: number
Sub_ value_chain_name: text
Demand_Characteristic_order: #
Demand_Characteristic_name: text
Input: text


When I set it up as a pivot table query and it shows how I want, I put the
Value_chain_name and Value_chain_order up top sorted by the value chain order.

I put the Demand_Characteristics_Order and Demand_Characteristic_name fields
in the row section, sorted by Demand_Characteristic_order.

I put the Sub_Value_Chain_Order and Sub_Value_Chain_Name fields in the
Column section.

I put the input in the detail section.

Essentially, I get something that looks like this:

Value_ chain_ order: number A
Value_ chain_ name: B
Sub_ value_ chain_ order: C
Sub_ value_chain_name: D
Demand_Characteristic_order: E
Demand_Characteristic_name: F
Input: G


A B
C1 C2 C3
C4 C5 C6 ect.
E1 F1 G1 G1 G1 G1
G1 G1
E2 E2 G2 G2 G2 G2
G2 G2
E3 E3 G3 G3 G3 G3
G4 G3

etc. to 27 rows.

Basically, putting the query into the Pivot table view gives me what I want,
but I need that view into a report so I can format it the way my company
wants. So, I've been told over and over to use a Cross Tab Query and then
build the report on the cross tab query. I've tried using the cross tab
query in access 2007 and I think my fields exceed the limit so I need to do
it in sql. But I don't have enough experience in sql to do this, and I get
errors.

Any insight would be helpful.

Therese