linking column headers in cross tab queries

[dexter195]

is there any way at all that you can combine the column headers in a cross tab query. i have products that have 5 prices and corresponding price codes so each product can be given at a different price to each customer.

eg. air freshener A €10
air freshener B €8
air freshener C €6
etc....

so i need to be able to combine "ProductDescription" "Price" and "PriceCode" into one heading.

is this at all possible to do?

 

[shannonp]

Description
air freshener A €10
air freshener B €8
air freshener C €6
etc...

Are the above values a single field? If so I would suggest you split them up (you could even take this further if you wanted to):

Description Type Price
air freshener A €10
air freshener B €8
air freshener C €6

Although not impossible, what you 'want' to do will be a little difficult but by using a little 'smart' text comparisons it could be accomplished...

 

[ReluctantDataGuy]

Yes, its possible. Create your crosstab query using the crosstab query wizard and just one of the fields for the column heading so that you get an approximation of what you want.

Then look at the design of the crosstab query, figure out which column is driving the column headings (it should say "column header".

Then Modify that column by changing it to a caclulated column. For example:

Product: [ProductDescription] & " - " & [Price] & " - " & [PriceCode]

Note: If you actually have a field named "Product" already, you'll need to change the field name to something different. You can name the calculated field anything you want as long as it doesn't duplicate another field name in the query.

Basically you'r just concatenating the fields together, and the crosstab query shouldn't care as long as your concatenation is correct.

Presumably, you won't mind if each column is pretty wide since that's a lot of information for column headers.

 

[dexter195]

Excellent stuff ReluctantDataGuy. that did the trick. ive been trying to figure that out all day. youve saved me a lot of brain ache + me boss will be very happy

my reports are gonna go quite a bit accross the page and into a few more. is there a way of having it so that it will stop after say 5 products and then start on another page with another 5 (going accross that page)
i can do this with cursors in sql but ive no idea how to apply this to access.
i can do the first 5 but after that im stumped
thanks again

 

[dexter195]

sorry to bother ye again but the dot in the price comes up as an underscore and i know that access doesnt allow this when your creating coulmns in tables, queries etc but is there a way of making access change that underscore into a dot just so that it looks like a price on the screen?

 

[ReluctantDataGuy]

Hmmm.... I forgot about that: The dot (.) is not a valid character for column headings. Try creating a new table in Datasheet View and name a field using a dot in the middle. You'll get an error saying it's not a valid name. I'm suprised Access converts the dot to underscore automatically in the crosstab query instead of returning an error message.

I don't know how you can work around that other than to omit the decimals by using:

Format([Price],"$#&quot in place of [Price]

If all your cents are .00 then this would be a good solution.

On limiting the columns to 5 per page...

I don't know of anything you can do in SQL to control this, however I was able to accomplish it by using the Page Setup properties. If you Print Preview the report and play with the page orientation, margin widths in File / Page Setup, and with the column widths in the report, you should be able to get the desired result.

 

[dexter195]

I think im gonna just leave the underscore in. the problem is that I need to have the cents in because these are quite important. Im still new to this access program (only started learning it in the last month or so). Im used to creating databases in sql and plsql.

That margin settings have helped but ive just been given another problem to solve. The report im presently trying to create is from a cross tab query as you know, and it’s a list of the products and quantities that have been rented by the clients. The problem is that when I create the report it has the products as columns that were rented but if I add more products that wern’t previously rented they won’t show up in the report so I have to create a new report with the new products. Is there a way of having the reports re-generated automatically???

Thanks for the help + sorry im such a pest

 

[ReluctantDataGuy]

Hmmm... Yes, I know there are solutions but they are not my forte. You might try posting this as a new question. I know there's good advice out there.

A guy I work with uses controls with generic names and then programmatically assigns captions to the labels used for column headings, and assigns control sources to the text boxes, etc. But I'm quite sure he doesn't control the number of columns.

And I've seen on forms where a bunch of these generic controls are placed out of the way on top of each other and invisible, and then they are programmatical located and sized. We have a table of all the possible fields, their types, widths, control source, caption and other properties. Then when user opens the form and selects one of the Tabs on a Tab control, the system determines which fields need to be displayed, in what order, and "places" then and makes them visible.

Maybe you could do something similar with the report.

Unfortunately, I helped create this scenario a couple years ago and don't have access to the code to see in detail how we did it. But as I said, maybe someone out there has a ready made solution for you.

 

[dexter195]

thats a very clever way around it. i reckon i could do that on a form but ive never programmed a report in access yet but ill give it a go.

il post the question later on if i cant get anywhere with it. thanks for all the help man its very much appreciated