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?
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?
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,.
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...
ASKER
no, it will spread to the march and April.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
ASKER
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])+[Qua ntity]-1,[ Total]/[Qu antity],Nu ll)
and how to spread to each month.
I feel mentally challanged.
Aug: IIf(8 Between Month([DateInvoiced]) And Month([DateInvoiced])+[Qua
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])+[Qua ntity]-1,[ Total]/[Qu antity],Nu ll)
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.
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])+[Qua
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.
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...