A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Setting Up & Running Reports
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Grouping a Report made with a cross tab qry



 
 
Thread Tools Display Modes
  #11  
Old April 6th, 2009, 08:21 PM posted to microsoft.public.access.reports
pedro
external usenet poster
 
Posts: 129
Default 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  
Old April 7th, 2009, 02:58 AM posted to microsoft.public.access.reports
Duane Hookom
external usenet poster
 
Posts: 7,177
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:56 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.