Distribution of Quantities to Months Query

[goksu]

Hello everyone.

this is my first post. I'll appreciate your help.

I am somewhat new to access. I especially lack experience working with dates as you will see below with my question.

I have been trying the last couple of days to create a query that distributes a group of quantities between two dates monthwise. Example is below. I have not been able to do this elegantly as yet. I cant think of a proper way to do it cleanly.

QTY1=100
QTY1 start date= 15Jan2009 (included)
QTY1 end date= 24Apr2009 (included)

so
total days=100
average per day=100/100 =1

DATES: JAN2009, FEB2009, May2009, Apr2009, etc...
QTY1: 17, 28, 31, 24
QTY2:
QTY3:
etc...

can we create a clean query that gives the above result? or do I need to go into macros?

 

[Mr. B]

Take a look at Cross Tab Queries. They can produce results like this.

 

[jjturner]

In order to break your date range into months you'll need to use an implicit SQL propagation technique by which the query generates records for each month.

You may want to refer to this thread see how you would go about setting it up.

Once you do this, you'll be ready to use a crosstab query to produce results in the format you're ultimately looking for.

 

[goksu]

Ok, here is what I figured out with the keywords "cross tab query" sent by Mr B. that reminded me of the word transpose. when I reread the help files on the issue this is what I came up with. data was in qr_res. create a table with all dates (daily) in range (not very elegant) (result: tb_dates). then do a crosstab transpose with the result tables with dates as row heading, ResID as column heading, BQperDay: Sum([BudgetQuantity]/([ef]-[es]+1)) dates criteria >=[qr_res].[es] And =[qr_res].[es] And (tb_dates.dates)