Need second field in the Cross Tab 2

[molly]

I am trying to do a crosstab query but i need 2 fields to appear as the "first value". so does not work with 2 fields, only for 1. Any tricks?

I put a miniture file onto Savefile with only 24 rows of phony data.

i am trying to get the SIZE field to be next to the PRICE field with both of them associated for a particular PackTypeID. EG packtypeID 701 for the Dodgers is Price "10.75" but i need the Size next to the "10.75" which is "16 OZ".

The crosstab will only handle 1 value at a time. Maybe you can think of a work around?? thanks alot.

 

[molly]

PS - i did a make table to make things easier for you to follow. The data is really normalized but not for this sample test. Molly

 

[molly]

Here is an example of what i need. The Size (EG ounces) is next to the each price.

Cat ProdFam 701 701 703 703

West Dodgers 10.75 16 OZ 19.00 3 OZ etc
West Giants 9.71 16 OZ etc

The Crosstab gets the Prices just fine. However, i cannot get the Sizes into the crosstab for each heading.
Molly

 

[SkipVought]

Hi,

I know that you can do what you want, quite easily, with a PivotTable in Excel, using drag and drop.

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[molly]

Skip, thanks for the thought. But i would prefer staying in my Access 2003 to see this Price List and also print it from Access. Later, I plan to make a button which will bring the Price List up during a phone call with a customer.

My backup plan is to merge (concatenate ?) the Size and Price fields and i think the Xtab will then recognize them as 1 field. I haven't tried it yet. I am worried about the columns waving around. maybe there is a way to lock the combined "Size Price" field so they line up best.

Can I do a Pivot table in Access 2003? If so, would that work for me?
thanks all, Molly.

 

[SkipVought]

Check Access Help

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[molly]

Thanks Skip. I studied the Pivot Table and applied it. I put a new file Mollie2 onto Savefile in

http://www.savefile.com/files/1456892.

If you run QryPivot1, then after qry is on screen, do View, Pivotable View.

The pivot table does exactly like you said. it gives me what i wanted. Now some detail questions. I am still learning this so sorry for any dumb question.

(1) How do I get this pivot view into a Report? Or maybe it does not?
(2) How can i get rid of the grandtotals at the bottom and far right?
(3) How can i change the top headings ID's like 701 or 702 etc into a name their proper names like Jar Small or Jar Medium etc. The headings ID's are keeping the proper sequence but the number has no meaning to the salesman. He needs to see the name.
(4) How can i get a division name to be above the team name in order to save space?

I hope this is proper to ask. My questions really involve how to adjust things in a Pivot table. Maybe others can learn from this too.
thanks alot. Molly

 

[dhookom]

Any tricks?"
Check the FAQs in the Queries forum here at TekTips for solution of how to create a crosstab with more than one value. If you can't find it or have any questions, post back.

Duane
Hook'D on Access
MS Access MVP

 

[molly]

Thanks Skip and Duane for ideas and encouragement. I kept digging on the internet. I found a site

http://support.microsoft.com/kb/304458

that explains how to make a "Multiple Value Field query".

I have it working using 4 queries. The first Qry is the data, 2 qry's are crosstabs and the last qry combines the 2 crosstabs. Then I will make a report off of the combined multiple value qry.

When i get more time, i will explore the Pivot table again and also search this site for even better ideas.
Thanks all.

 

[dhookom]

I have seen the multiple crosstab solution and didn't care much for its performance. That's why I created the multi-value solution. However, if you got the MS solution meeting your needs then go for it.

Duane
Hook'D on Access
MS Access MVP

 

[molly]

Duane - i cannot find your multi-value solution when searching this site. Can you direct me where to find it? thanks Molly.

 

[dhookom]

faq701-4524

Duane
Hook'D on Access
MS Access MVP

 

[molly]

Duane - i am trying out your FAQ. I encountered a problem. After making the table that you noted, i did a new Qry and pasted in SQL design your Transform coding.

But the DeVal field was cutoff and the qry did not run.

Any reason or fix? I use Access 2003 if that matters.

Your transform statement in the NWind was:

TRANSFORM Sum(IIf([FldName]="Quantity",[Quantity],[Quantity]*[Order
Details]![UnitPrice])) AS DaVal
SELECT Products.ProductName
FROM tblXtabColumns, Orders INNER JOIN (Products INNER JOIN [Order Details]
ON Products.ProductID = [Order Details].ProductID) ON Orders.OrderID =
[Order Details].OrderID
WHERE (((Orders.OrderDate) Between #1/1/1998# And #3/31/1998#))
GROUP BY Products.ProductName
PIVOT [FldName] & Month([OrderDate]);


thanks
molly

 

[dhookom]

I created a table like:
[tt][blue]
tlbFieldNames
=============
FieldName (text field)
Price
Size
[/blue][/tt]
Then create a crosstab with this SQL:

TRANSFORM Max(IIf([FieldName]="Price",[Price],[Size])) AS Expr2
SELECT mtblInfo.CatalogueName, mtblInfo.ProductFamilyName
FROM mtblInfo, tblFieldNames
GROUP BY mtblInfo.CatalogueName, mtblInfo.ProductFamilyName
PIVOT [PackTypeID] & [FieldName];

Duane
Hook'D on Access
MS Access MVP

 

[molly]

Duane - you are truly amazing and a tricky guy. This is a major nice thing you did ... the multivalue pivot table.

it works just fine. Thanks you for using my sample file for me to follow. For other people, i made a new shorty file on savefile at called Mollie3 at:

http://www.savefile.com/files/1465871

May i ask another question. i tried to make a Report off of your pivot table. no go. i cannot see the fields when i try to make a wizard report. Maybe you have another trick up your sleve?
Molly

 

[molly]

I guess i should have called your method a multivalue cross tab, right? not a multi pivot table?

as for the report. if i reference the Query1 in my file, i see NO fields.

as a check, i made a single value cross tab and i can make a normal report. so something is special in coordinating your multi value Xtab qry and a report.

molly

 

[dhookom]

I have a crosstab report sample at

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

that might work for you. This solution actually creates an alias for your crosstab column headings so the report will always use constant columns.

Duane
Hook'D on Access
MS Access MVP

 

[molly]

Duane - I could not find your Sample that uses a 2 multi value query. when i use Report Wizard, i do not see the qry fields that are in the 2 multivalue query that we made. any ideas? thanks
Mollie

 

[dhookom]

The crosstab report that create aliases and the 2 value crosstab are separate solutions. I'm not sure how they would be combined. I expect they could combined but I haven't tried this. I hoped you would try.

Duane
Hook'D on Access
MS Access MVP

 

[molly]

Duane - yes, it looks like it cannot be done. i think i will try to concatenate the size and price as 1 field. and then do a cross tab and see how the Report wizard likes it or not. thanks anyway. you are terrific. Molly.