Adding new crosstab fields to report

[ErinL]

Hi everyone -

I have a report based on a crosstab. Every week when the inventory numbers are entered into the database, a new field gets added to the crosstab with the date as the column header but this field does not show on the report. I am wondering if there is a way to automatically add this field to the report without having to manually add it in Design View each week.

If anyone knows of a way to do this or a better way to get the same kind of formatting on a report, I would greatly appreciate it.

Thanks in advance.

 

[vbaInet]

So I'm guessing that you've PIVOTED some columns in your crosstab so that they are accessible in the Report Fields? Can you explain a bit more how and why a new field is added to the pivot every week?

Is a new field added to the table as well?

 

[ErinL]

Thank you for the response.

Yes the columns are pivoted from the table.

The table (tblInventory) consists of three fields - InventoryDate, ItemNumber and QuantityOnHand. These fields are consistent week to week.

I created a crosstab query based on that table using ItemNumber as the Row Heading, InventoryDate as the Column Heading and QuantityOnHand as the Value. This is the query I use for the report and it is in the format that the user would like. Each week when the new inventory numbers are entered, it adds a new week column to the crosstab query but since these are like new fields in the report, the "field" must be manually added to the report in Design view.

I found a work around by swapping the Row Heading and Column Heading (i.e. make the item numbers go across the top and the dates down the side) and it works but it isn't in the format that the user would like to have so I was wondering if there was a way to do it with the dates across the top.

Thank you.

 

[vbaInet]

You can programmatically do it but that would involve a good amount of coding which I don't have time for at the mo.

Have you thought about having the query as a subreport? If you have any Totals in the report footer, you could always use aggregate functions such as DSum() and DCount()