How to show a Dynamic Crosstab Query in a Report?

[Jsam69]

I am trying to make a report that shows shipment information. I have a shipment table, a box table, and a box detail table. I have made a crosstab query that works well.

I've used the box number as a row heading, the item type as the column heading, and the item quantity for the value. The query shows how many of each item was sent in each box.

Since each shipment will have different items, I can't just convert this query to a report using wizard because the field names would be the same every time.

How can I make a dynamic report by using a crosstab query?

If I could have every item type as a column heading, even though some items' quantities would be zero, then that would be fine.

Thanks

-Joe

 

[Jsam69]

Ok, here's what I did. I have an objects table that just has the object names and an objectID. I ran a select query with those values and named it objectquery. Then I made a crosstab query with box Info!Box_ID as the row header, objectquery!Objecttype as the column header and quantity as the value. All of the object types show up as column headers, but all of the values from each box are the same for each item. What is happening? Please help.

 

[Jsam69]

I figured it out. In query design mode, I changed the column headings property to a string with all of the field names that I needed. For example "date1","date2","date3",.... This works even when there is no data for a certain field, and the report opens fine. Thanks for your help.

 

[yessir]

how do you do this?

how do you do this?

 

[musesshadow]

Open the query design view and double click on an empty space. You'll be provided with the properties box for the crosstab query where you'll find a "Column Headers" entry. Just enter the desired headings as

"Heading 1"; "Heading 2"; "Heading 3"; ...