Grouping a Report made with a cross tab qry

  • Thread starter Thread starter Pedro
  • Start date Start date
P

Pedro

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
 
Isn't this similar to the question I answered regarding subtracting specific
account number records?
 
Hi Duane
It's not because i have a grouping report based on a crosstab qry with
several grouping levels. On one of thm i want to do some sums.
Tks in advance
Pedro
 
"..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.
 
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
 
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 said:
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 said:
"..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.
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
"..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


:

Isn't this similar to the question I answered regarding subtracting specific
account number records?
--
Duane Hookom
Microsoft Access MVP


:

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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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
:

"..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


:

Isn't this similar to the question I answered regarding subtracting specific
account number records?
--
Duane Hookom
Microsoft Access MVP


:

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
Click to expand...
Click to expand...
Click to expand...
 
Ok. Can you explain how it'll work after i add that field with that value on
the table?
Tks Duane?

Duane Hookom said:
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 said:
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 said:
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


:

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
:

"..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


:

Isn't this similar to the question I answered regarding subtracting specific
account number records?
--
Duane Hookom
Microsoft Access MVP


:

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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
Ok. Can you explain how it'll work after i add that field with that value on
the table?
Tks Duane?

Duane Hookom said:
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 said:
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 ...

:

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


:

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
:

"..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


:

Isn't this similar to the question I answered regarding subtracting specific
account number records?
--
Duane Hookom
Microsoft Access MVP


:

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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
Ok. Can you explain how it'll work after i add that field with that value on
the table?
Tks Duane?

Duane Hookom said:
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


:

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 ...

:

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


:

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
:

"..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


:

Isn't this similar to the question I answered regarding subtracting specific
account number records?
--
Duane Hookom
Microsoft Access MVP


:

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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
Ok. Can you explain how it'll work after i add that field with that value on
the table?
Tks Duane?

:

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


:

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 ...

:

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


:

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
:

"..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


:

Isn't this similar to the question I answered regarding subtracting specific
account number records?
--
Duane Hookom
Microsoft Access MVP


:

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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top