By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,264 Members | 1,277 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,264 IT Pros & Developers. It's quick & easy.

SQL loop for pivot table

P: 1
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.
Oct 5 '07 #1
Share this Question
Share on Google+
2 Replies


Jim Doherty
Expert 100+
P: 897
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
Oct 6 '07 #2

nico5038
Expert 2.5K+
P: 3,072
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:
Expand|Select|Wrap|Line Numbers
  1. select Year([YourDateField]) & Right("00" & Month([YourDateField]),2 As YearMonth, ....
  2.  
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:
Expand|Select|Wrap|Line Numbers
  1. select distinct Year([YourDateField]) & Right("00" & Month([YourDateField]),2 As YearMonth From tblX;
  2.  
Now you can have any selection you want.

Getting the idea ?

Nic;o)
Oct 6 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.