SQL loop for pivot table

[yogitime]

I Need help. I have a access database that is used for scheduling purposes. I have a form that allows you to select month and year and it will run a report based on a query showing what customers are do, how much there bill will be, etc.

What I want to do is use this query to create a report or pivot table to loop for each month in a year so I can quickly look at a year at a time, not just a month.

I can't seem to figure out how to loop a query using variables. Can someone give me some coding help on how to do this.


Thanks.

[Jim Doherty]

I Need help. I have a access database that is used for scheduling purposes. I have a form that allows you to select month and year and it will run a report based on a query showing what customers are do, how much there bill will be, etc.

What I want to do is use this query to create a report or pivot table to loop for each month in a year so I can quickly look at a year at a time, not just a month.

I can't seem to figure out how to loop a query using variables. Can someone give me some coding help on how to do this.


Thanks.

Have you looked at using a the 'crosstab' query where customers are contained (grouped by) in the first column and the months shown as fixed column headers for a particular given year? I am not convinced you need to use 'looping a query' which to me means coding it can be done quite easily in Access's standard SQL

Jim

[nico5038]

My approach would be to start with a report that's showing all YearMonth's as different groupings. (See the Grouping button that looks like [= )
The only requirement will be to add a YearMonth field in the report's query like:

select Year([YourDateField]) & Right("00" & Month([YourDateField]),2 As YearMonth, ....

Now use the new field [YearMonth] to group the report.

In the report form you can use a query to extract the start and end YearMonth for the start and end combobox by using the above select like:

select distinct Year([YourDateField]) & Right("00" & Month([YourDateField]),2 As YearMonth From tblX;

Now you can have any selection you want.

Getting the idea ?

Nic;o)