Data spread out

Hi Experts,

I have data that looks like the below:

Date          name     Quantity   Amount
 03/2011      Smith       4      1000
 03/2011      John        3      9000
 03/2011      Bert        1      1500

I would like to be able to show the result,

if the quantity is more than one, then the quantity will be divided by the amount and spread in the next columns evenly in the query results.

example,
                                        march  April  May  June
 03/2011      Smith       4      1000   250     250   250  250
 03/2011      John        3      9000   3000    3000  3000
  03/2011      Bert        1      1500  1500

Any suggestions?
HappyAlwaysAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
First, please reconsider using you nonstandard month/year format.
03/2011 is not a date, and cannot be used as one.

If your data is like you what posted, then it is already "Summarized". (By Month)
Hence it might be better to use your raw summarized data...
0
HappyAlwaysAuthor Commented:
Actually it is that way. DD/MM/YY

the reason I want to spread the data for accounting and accrual purposes. We need to book that data by month. so when we have more than one month grouped, we need to spread it to the following months,.
0
GRayLCommented:
Just so I'm sure, if Quantity is 2 will you still spread the quotient across the three months?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Jeffrey CoachmanMIS LiasonCommented:
I'll let Ray take it from here...
0
HappyAlwaysAuthor Commented:
no, it will spread to the march and April.
0
GRayLCommented:
Assuming multi-year data, for a given year, try this:

SELECT Format(DataDate,"mm/yyyy")As [mm/yyyy], [Name], Quantity, Amount,  
Iif(1 BETWEEN Month(DataDate) AND Month(DataDate)+Quantity-1,Amount/Quantity,Null) AS Jan,
Iif(2 BETWEEN Month(DataDate) AND Month(DataDate)+Quantity-1,Amount/Quantity,Null) AS Feb,
Iif(3 BETWEEN Month(DataDate) AND Month(DataDate)+Quantity-1,Amount/Quantity,Null) AS Mar,
Iif(4 BETWEEN Month(DataDate) AND Month(DataDate)+Quantity-1,Amount/Quantity,Null) AS Apr,
Iif(5 BETWEEN Month(DataDate) AND Month(DataDate)+Quantity-1,Amount/Quantity,Null) AS May,
Iif(6 BETWEEN Month(DataDate) AND Month(DataDate)+Quantity-1,Amount/Quantity,Null) AS Jun,
Iif(7 BETWEEN Month(DataDate) AND Month(DataDate)+Quantity-1,Amount/Quantity,Null) AS Jul,
Iif(8 BETWEEN Month(DataDate) AND Month(DataDate)+Quantity-1,Amount/Quantity,Null) AS Aug,
Iif(9 BETWEEN Month(DataDate) AND Month(DataDate)+Quantity-1,Amount/Quantity,Null) AS Sep,
Iif(10 BETWEEN Month(DataDate) AND Month(DataDate)+Quantity-1,Amount/Quantity,Null) AS Oct,
Iif(11 BETWEEN Month(DataDate) AND Month(DataDate)+Quantity-1,Amount/Quantity,Null) AS Nov,
Iif(12 BETWEEN Month(DataDate) AND Month(DataDate)+Quantity-1,Amount/Quantity,Null) AS Dec,
FROM tblData
WHERE Year(DataDate) = [Enter Year yyyy]
Order By Month(DataDate);
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
HappyAlwaysAuthor Commented:
When I ran it, it gave me a message( the select statement a reserved word or argument that is missspelled or missing or the punctuation is incorrect) when I click Ok, it higlight the word form from the statement.
0
GRayLCommented:
So if all is OK, thanks, glad to help.
0
HappyAlwaysAuthor Commented:
you know, I tried to understand it but I could not. thank you for all your help. but I am not sure what are you telling the system to do when you wrote:

Aug: IIf(8 Between Month([DateInvoiced]) And Month([DateInvoiced])+[Quantity]-1,[Total]/[Quantity],Null)

and how to spread to each month.

I feel mentally challanged.

0
GRayLCommented:
You have your tblData with the fields DataDate, Name, Quantity, and Amount.  I'm suggesting you have to generate 12 additional fields, one per month and in each, insert the quantity Amount/Quantity, if that field lies within the range
Month(DataDate) AND Month(DataDate)+Quantity -1 (we need the minus 1 to make the math work out).  I evaluate that logic using the Iif() function.  

In the example:

Aug: IIf(8 Between Month([DateInvoiced]) And Month([DateInvoiced])+[Quantity]-1,[Total]/[Quantity],Null)

If 8 is in the range, insert Amount/Quantity in the August (8th month) field, insert the quotient, else insert a null

and so on for all months.

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.