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.

 

[dhookom]

You created a parameter prompt query which might be causing your problems. IMHO, parameter prompts are never acceptable user interface faq701-6763.

You might have better luck if you used a combo box on a form to enter the criteria value.

Duane
Hook'D on Access
MS Access MVP

 

[molly]

Duane - yes you are right. The parameter prompt was hurting my Qry. Unfortunately, i will have to make about 5 qry's since they use a different price level. molly

 

[dhookom]

Why would you need multiple queries?

Duane
Hook'D on Access
MS Access MVP

 

[molly]

Hi duane. My thought of multiple queries, because of the paramater qry issue, is that i have different customer proce levels. i don't think i gave you this criteria so as to keep the issue simple. so i thought. turns out that it was a big issue as you discovered.

when i take out the parameter, then yes, i can see the fields in the Report wizard.

so i need to run the crosstab for say price level "1" customers. and then separate one's for levels 2, 3 and 6.
so i think this means four separate crosstab queries. unless you have a trick.

thanks. i feel that we are so close. i probably need to make a newer sample file.
molly

 

[dhookom]

Did you try create a form with a combo box of price levels that can be used to filter? As per your current crosstab, you will need to adjust the query->parameters for the control rather than the parameter prompt. The form must be open with a value selected in the combo box.

Duane
Hook'D on Access
MS Access MVP

 

[molly]

Duane - so far so good. The deal is 2 qry's and i am happy with it, using your trick. i took off the parameter stuff like you said. then the Report Wizard was able to see the Qry fields.

New question. Your last message said to use my Form.
i made a text box in the Form frmNsBulkMain on the TAB titled "Impact" and called the text box "txtCatalogueReportPriceLevel". it is Unbound. i made no control source. this is okay for this purpose, right?

i then made a button to run the report. i know how to do that.

in the Qry, on the PriceLevel field, i put on the criteria row:
[forms]![frmNsBulkMain]![txtCatalogueReportPriceLevel]

i do not know if the TAB needs referencing too??

Here is the rub. When i run the report, i get an error message:

"the microsoft Jet does not recognize "[forms]![frmNsBulkMain]![txtCatalogueReportPriceLevel]"
as a Valid field name or expression."

what am i doing wrong?

Now if i hard code the price level EG with "1" and do not use the Forms Bang, everything works fine. And when i change the 1 to 2, all is good too. So something is wrong with my Forms!bang i think.

thanks,
Molly

 

[dhookom]

you will need to adjust the query->parameters for the control rather than the parameter prompt

The file you had posted a while back had the parameter prompt and data type listed in the query parameters. You need to do the same for [forms]![frmNsBulkMain]![txtCatalogueReportPriceLevel].

Duane
Hook'D on Access
MS Access MVP

 

[molly]

Duane. BINGO !! Job accomplished. I understood what you wrote.