Link to home
Start Free TrialLog in
Avatar of HappyAlways
HappyAlways

asked on

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?
Avatar of Jeffrey Coachman
Jeffrey Coachman
Flag of United States of America image

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...
Avatar of HappyAlways
HappyAlways

ASKER

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,.
Just so I'm sure, if Quantity is 2 will you still spread the quotient across the three months?
I'll let Ray take it from here...
no, it will spread to the march and April.
ASKER CERTIFIED SOLUTION
Avatar of GRayL
GRayL
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
So if all is OK, thanks, glad to help.
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.

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.