Generating an updated report based on new records added

[spatel]

Hello,

I have a report which is based on a crosstab qry. The cross tab query gets updated everytime a new payperiod payroll data file is added to its respective table; however my report dosen't reflect the changes with the new data.

Basically, everytime I add a new payperiod file, it creates a new column in the table for that payperiod which then get reflected in my cross-tab query. So If I had payperiod 01/10/04 initially and then if I add 01/24/04, it updates it in the table and then the cross-tab query but in the report it only has payperiod 01/10/04 because thats the only column existed originally when I created that report via the wizard.

How can I get the report to run so it captures all the columns currently existed in the cross-table query.

thanks

 

[Andrew Fergus]

Hi,

this is one of the limitations of Access where you create a report on a crosstab query - the column names in the query depend on the data but the report needs fixed variable names.

There are ways around this but you have to have data formats that support what you are trying to do. Another fellow had a similar problem and we eventually got there : see this post. Keep in mind that some of the earlier posts in that thread weren't 100% helpful.

The main thrust of it is that you need to have a "dynamic" column variable name in your crosstab query that can then get passed through to the report in a fixed way. To do this you need to work out the last period (using max or dmax), then select the x (pick a number) number of periods you want on your report and give them column names that will fit on your report such as "last period", "period - 1" etc.

HTH, Andrew.