Cross Tab Query wont update report

[spartansFC]

Hi

I've created a cross tab query which displays a list of children at a specific nursery and then shows what invoice payments have gone against each child for each month.

On my invoice form, the user inputs each month by only putting the first day of each month, ie. 01/01/2010 or 01/09/2009, this means if i have any extra payments for that month, this will be totalled up for that month, i suppose i could have created a combo box and just had "Jan-10" or "Sept-09".

Anyways, the report works great until i enter new data, the report won't display the extra column information. I've read that i need to set up a dynamic column cross tab query but need a few pointers.

I've attached a rough design of my report to show you what i mean.

Mikie

 

[dhookom]

Check the FAQs for this forum.

Duane
Hook'D on Access
MS Access MVP

 

[spartansFC]

Hi Duane

thanks for the reply, i looked at the FAQ and ran the wizard within access and set my row headings to Jan - Dec which works fine up to a point.

When i use the wizard and the above method and put in say amount for May 2010, then that amount gets added to the May total.

I wanted my row headings to work Jan-09, Feb-09 etc up to Apr-12.

I might just create a report based off a query that works off a specific monthly period, as there's only 3 years funding alotted.

Mikie

 

[dhookom]

Did you look at faq703-5466? If so, what didn't work about it? What wizard did you run? You shouldn't end up with column names like look like actual months.

Duane
Hook'D on Access
MS Access MVP

 

[spartansFC]

Hi Duane

thanks again for that link, i have seen that webpage before as i've googled like mad trying to figure out this cross tab report, and to be honest that webpage help went over my head a bit, just couldn't get my head around it. I did some more googling, and found a work around though. In the cross tab query, in the properties, i labelled my column headings there, i.e Apr-09, May-09, June-09 etc. It worked straight away.

Thanks again for the help, i've attached a screen shot of my final report just incase you want to see.

Mikie

 

[dhookom]

I believe you will have additional maintenance with every month change. The solution I suggested had no code involved and allows you to specify a window/range of months.

Duane
Hook'D on Access
MS Access MVP

 

[spartansFC]

You're right again Duane, but because this database will only be used for now a maximum of 2 years, i can limit the way the query and reports are done, the reports don't require any coding and they will update automatically every time the user enters data.

i've set up 2 queries for Year 1 and Year 2 and both are just limited to Apr-09 to Mar-10 (Year 1) and Apr-10 to Mar-11 (Year 2), i've then created a crosstab query based on each year and because i've added those column headings to the cross tab query and the combo box selection the users use to select the month, the report works perfectly, well for me it does and like i said, it updates automatically.

thanks again

Mikie