If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Grouping a Report made with a cross tab qry
Hi Duane
Tks for your answer. I think my problem will remain cose i only want it to subtract and account to other if the grouping level equals "Activo". In that case it'll do this : all values from this account number (42) should subtract on this account number (43). Suppose total of account 43 is 100 and total of account 42 is 30 the result should be 70. (43-42). But only if group level is like "Activo" cose if not, it won't do anything. Tks again for all your effort Duane on helping me. Best regards Pedro "Duane Hookom" wrote: If I understand correctly you have an AccoutNumber field that determines if the details of that line item should be added or subtracted. Assumining you add a field [AddOrSub] to your table of accounts that stores 1 for those you want to add and -1 for those you want to subtract. Your expression in your report would be something like: =Sum([AddOrSub]*[2008-01]) or =Soma([AddOrSub]*[2008-01]) -- Duane Hookom Microsoft Access MVP "Pedro" wrote: Ok. Can you explain how it'll work after i add that field with that value on the table? Tks Duane? "Duane Hookom" wrote: I didn't think you would need to add a table, just a field in "already have a table with group levels". "on a specific group level ..." suggest that you would set a -1 for these specific group levels. -- Duane Hookom Microsoft Access MVP "Pedro" wrote: Hi Duane. Tks for your answer. I'm not sure if that way it'll work cose i've already have a table with group levels and adding one more on the report it'll show the sum or subtraction for every group level and i don't want that but on a specific group level ... "Duane Hookom" wrote: I think you need to add a field or fields to tables to store either a 1 or a -1. Then =Soma([NewField]*[2008-01]) I would not be hard-coding ContaN values into your expression since it seems you keep coming back with new values and expressions. -- Duane Hookom Microsoft Access MVP "Pedro" wrote: Hi Duane You're absolutly right. The name of the group levels has to do with ballance account, for example, Activo, Passivo and others. And yes they're are on a previous table. That table has numbers (group number) and designation of the group as well as the account numbers. The idea is : If some control box on the sum of the group level shows me the "Total Passivo", i would like to do something like : =Soma(Abs([ContaN] In (71;79))*[2008-01])-Soma(Abs([ContaN] In (61))*[2008-01]) But of course changing only the account numbers. If control box is "Passivo" Do this sum and subtraction If control box is "Activo" Do this sum and subtraction If... If... If... Else Sum([2008-01]) Endif I hope you get my idea. Don't know if it's possible to put all this instruccions on a control box multiplied by 12 cose they're 12 to do this. Remember it's a report made of a qry croo table. Tks very much Duane Best regards Pedro "Duane Hookom" wrote: "..the grouping level is equal to "James".." Is James your actually grouping level? Is this the name of your field? It seems to me you probably have a field that you are grouping by. I expect this field is pulled from a table where this field is unique (a lookup table). If some of these values should be added and some of them subtracted, then you really need to add a field to the lookup table to store whether or not the records should be added or subtracted. -- Duane Hookom Microsoft Access MVP "Duane Hookom" wrote: Isn't this similar to the question I answered regarding subtracting specific account number records? -- Duane Hookom Microsoft Access MVP "Pedro" wrote: Hi everyone I have a report made from a crosstab qry. It shows some values fromsome account names with month on colum head. How can i execute a sum or a subtraction of some values if the grouping level is equal to "James"? Tks in advance Pedro |
#12
|
|||
|
|||
Grouping a Report made with a cross tab qry
Then I think you need to store something in your tables that models if
Account is 42 and the other grouping level is Activo then multiply by -1 rather than 1. If you can't model this in your tables then I would create a small user-defined function that accepts the grouping level field and the account field and returns either 1 or -1. -- Duane Hookom Microsoft Access MVP "Pedro" wrote: Hi Duane Tks for your answer. I think my problem will remain cose i only want it to subtract and account to other if the grouping level equals "Activo". In that case it'll do this : all values from this account number (42) should subtract on this account number (43). Suppose total of account 43 is 100 and total of account 42 is 30 the result should be 70. (43-42). But only if group level is like "Activo" cose if not, it won't do anything. Tks again for all your effort Duane on helping me. Best regards Pedro "Duane Hookom" wrote: If I understand correctly you have an AccoutNumber field that determines if the details of that line item should be added or subtracted. Assumining you add a field [AddOrSub] to your table of accounts that stores 1 for those you want to add and -1 for those you want to subtract. Your expression in your report would be something like: =Sum([AddOrSub]*[2008-01]) or =Soma([AddOrSub]*[2008-01]) -- Duane Hookom Microsoft Access MVP "Pedro" wrote: Ok. Can you explain how it'll work after i add that field with that value on the table? Tks Duane? "Duane Hookom" wrote: I didn't think you would need to add a table, just a field in "already have a table with group levels". "on a specific group level ..." suggest that you would set a -1 for these specific group levels. -- Duane Hookom Microsoft Access MVP "Pedro" wrote: Hi Duane. Tks for your answer. I'm not sure if that way it'll work cose i've already have a table with group levels and adding one more on the report it'll show the sum or subtraction for every group level and i don't want that but on a specific group level ... "Duane Hookom" wrote: I think you need to add a field or fields to tables to store either a 1 or a -1. Then =Soma([NewField]*[2008-01]) I would not be hard-coding ContaN values into your expression since it seems you keep coming back with new values and expressions. -- Duane Hookom Microsoft Access MVP "Pedro" wrote: Hi Duane You're absolutly right. The name of the group levels has to do with ballance account, for example, Activo, Passivo and others. And yes they're are on a previous table. That table has numbers (group number) and designation of the group as well as the account numbers. The idea is : If some control box on the sum of the group level shows me the "Total Passivo", i would like to do something like : =Soma(Abs([ContaN] In (71;79))*[2008-01])-Soma(Abs([ContaN] In (61))*[2008-01]) But of course changing only the account numbers. If control box is "Passivo" Do this sum and subtraction If control box is "Activo" Do this sum and subtraction If... If... If... Else Sum([2008-01]) Endif I hope you get my idea. Don't know if it's possible to put all this instruccions on a control box multiplied by 12 cose they're 12 to do this. Remember it's a report made of a qry croo table. Tks very much Duane Best regards Pedro "Duane Hookom" wrote: "..the grouping level is equal to "James".." Is James your actually grouping level? Is this the name of your field? It seems to me you probably have a field that you are grouping by. I expect this field is pulled from a table where this field is unique (a lookup table). If some of these values should be added and some of them subtracted, then you really need to add a field to the lookup table to store whether or not the records should be added or subtracted. -- Duane Hookom Microsoft Access MVP "Duane Hookom" wrote: Isn't this similar to the question I answered regarding subtracting specific account number records? -- Duane Hookom Microsoft Access MVP "Pedro" wrote: Hi everyone I have a report made from a crosstab qry. It shows some values fromsome account names with month on colum head. How can i execute a sum or a subtraction of some values if the grouping level is equal to "James"? Tks in advance Pedro |
|
Thread Tools | |
Display Modes | |
|
|