August 20, 2014 at 7:44 am
Hi,
I am currently trying to write a query that pulls a summation of item specific data from sales orders. For simplicity's sake, the column structure can be something like the following...
Item#,
PoundsDuringWeekNumber (this would be the current week number out of the 52 weeks in a year and the pounds of the item during that week)
However, those are not going to be the only 2 columns. The idea of the query is that the user would be able to provide the query a date range (say 2 months) and the columns would then morph into the following...
Item#,
PoundsDuringWeekNumber (current),
PoundsDuringWeekNumber(current - 1),
PoundsDuringWeekNumber (current - 2),
etc. etc.
PoundsDuringWeekNumber(current - 8)
Initial ideas where to create a function to execute the summation of the pounds during the date range of the week in question and execute it across the columns, but with the indeterminable number of columns, the query would not know how many times to execute the function.
Any help is greatly appreciated.
Thanks
August 20, 2014 at 11:17 am
Looks like you need a crosstab/pivot. I suggest reading the last 2 articles in my signature (Cross Tabs and Pivots...) which, in my opinion, are the standard for how to do what you need to do. Read the articles and put together some code and if you are still having problems post your code to this thread, using the guidance in the first article in my signature, and I'm sure you'll get a working a solution. And actually by putting together the question in the method recommended you may come up with your own answer, I have many times by going through that exercise.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply