Report Total

Hi Marshall,

I can't seem to get that to work. I don't have any invoice information in
my form, but my group is grouped by tbl_Account_GL_ID. I have tried to = the
tbl_Account_GL_ID to the tbl_transactions GL_ID but I don't get a result. I
don't have any data in the forms only the tables.

The GL_ID links the invoices to the group account.

Any ideas?

"Marshall Barton" wrote:

Seems like you're on the right track. Just add another
criteria for the invoice ID (I don't know what that would be
in the form or table).

=DSum("[Invoice_amt]","tbl_Invoices","[Invoice_Date]
[forms]![Report Date Range]![Beginning Trans Date] AND
[Invoice ID] = [forms]![Report Date Range]![Invoice ID]")

Don't tell me! Next you're going to tell me that you have
to include the older revisions in that calculation too ;-)
--
Marsh
MVP [MS Access]



Tina wrote:
I've gotten the following to work:
=DSum("[Invoice_amt]","tbl_Invoices","[Invoice_Date] [forms]![Report Date
Range]![Beginning Trans Date]")

but it only gives me the the total of all invoices previous to this date and
not just for that one group. I added the txt field to the group footer.
Every group show's the same amount.

"Tina" wrote:
Thanks Marshall that worked great. I have another question for you if thats
ok.

My report lists transactions (and revision amounts - subreport) for the
group between dates entered by the user.

I have a planned budget field that is the initial amt allocated to that grp.
At the end of the report I get a balance of all the transactions for that
period then subtract the planned budget (=[Planned Budget] - Sum([Invoice]))
-/+ revision(s).

My problem is that transactions that happened before the date entered by the
user will effect the initial planned budget and it only lists transaction
that are between the specified dates.

Is there some way that I can accumulate the previous transaction_amts that
are the beggining_trans_date entered by the user based on the
transaction_date. I will use this total to subtract from the Planned Budget.
I want an up to date amt even though the user is only looking at the report
for a certain period, what happened to the account before the
beggining_trans_date is relevant otherwise my totals won't be precise.

Should I create another txt field or try and do this in my control source
(query). I don't know how to go about doing this..

transaction_amt
transaction_date
Forms![beggining_trans_date]


"Marshall Barton" wrote:
First get the total for the subreport in a text box named
txtGrpTotal in the subreport's Report Footer section by
using an expression like:
=Sum(IIf(revision_operator="Add", 1, -1) * Revision_Amt)

Now we have a single value for the main report to use. The
group footer (that contains the subreport) can then use a
text box with the expression:
=[Planned Budget] - Sum([Invoice]) +
IIf([tbl_Budget_revision].Report.HasData,[tbl_Budget_revision].Report.txtGrpTotal,0)

Now I'm confused again :-(

If the values you want to accumlate are not in the invoice
table, what table are they in??

I though you only needed to refine the DSum to restrict it
to the invoice amounts for the current transaction??

I guess the thing you tried aounds reasonable as long as the
amount is in the invoice table and the GL_ID is the linking
field, but you "don't get a result", which I suppose means
Null (nothing found).

I suppose that you might have to post the relevant tables
with their important field, the field types and which ones
are primary and foreign keys. Maybe then I will be able to
claw my way out of this fog I seem to be stuck in.
--
Marsh
MVP [MS Access]



Tina wrote:
I can't seem to get that to work. I don't have any invoice information in
my form, but my group is grouped by tbl_Account_GL_ID. I have tried to = the
tbl_Account_GL_ID to the tbl_transactions GL_ID but I don't get a result. I
don't have any data in the forms only the tables.

The GL_ID links the invoices to the group account.


"Marshall Barton" wrote:
Seems like you're on the right track. Just add another
criteria for the invoice ID (I don't know what that would be
in the form or table).

=DSum("[Invoice_amt]","tbl_Invoices","[Invoice_Date]
[forms]![Report Date Range]![Beginning Trans Date] AND
[Invoice ID] = [forms]![Report Date Range]![Invoice ID]")

Don't tell me! Next you're going to tell me that you have
to include the older revisions in that calculation too ;-)


Tina wrote:
I've gotten the following to work:
=DSum("[Invoice_amt]","tbl_Invoices","[Invoice_Date] [forms]![Report Date
Range]![Beginning Trans Date]")

but it only gives me the the total of all invoices previous to this date and
not just for that one group. I added the txt field to the group footer.
Every group show's the same amount.

"Tina" wrote:
Thanks Marshall that worked great. I have another question for you if thats
ok.

My report lists transactions (and revision amounts - subreport) for the
group between dates entered by the user.

I have a planned budget field that is the initial amt allocated to that grp.
At the end of the report I get a balance of all the transactions for that
period then subtract the planned budget (=[Planned Budget] - Sum([Invoice]))
-/+ revision(s).

My problem is that transactions that happened before the date entered by the
user will effect the initial planned budget and it only lists transaction
that are between the specified dates.

Is there some way that I can accumulate the previous transaction_amts that
are the beggining_trans_date entered by the user based on the
transaction_date. I will use this total to subtract from the Planned Budget.
I want an up to date amt even though the user is only looking at the report
for a certain period, what happened to the account before the
beggining_trans_date is relevant otherwise my totals won't be precise.

Should I create another txt field or try and do this in my control source
(query). I don't know how to go about doing this..

transaction_amt
transaction_date
Forms![beggining_trans_date]


"Marshall Barton" wrote:
First get the total for the subreport in a text box named
txtGrpTotal in the subreport's Report Footer section by
using an expression like:
=Sum(IIf(revision_operator="Add", 1, -1) * Revision_Amt)

Now we have a single value for the main report to use. The
group footer (that contains the subreport) can then use a
text box with the expression:
=[Planned Budget] - Sum([Invoice]) +
IIf([tbl_Budget_revision].Report.HasData,[tbl_Budget_revision].Report.txtGrpTotal,0)

Hi,

Ok, I have two tables, tbl_Invoices and tbl_GL_Accounts.

tbl_Invoices consists of
Invoice_ID (auto #) (PK)
GL_ID (reference from tbl_GL_Accounts)(FK)
Invoice_Amt
Invoice_Date

tbl_GL_Accounts consists of:
GL_ID (auto #)(PK)
GL_Name
GL_Budget etc...

I have created a query based on these two tables, then took that query and
am using it in my report. My field list in my report shows that I have
tbl_invoice_GL_ID and tbl_GL_Account_GL_ID. So in the DSum expression I am
trying to equal those two fields and I get nothing. Though if I remove the
AND statement I get the sum of all the invoices before the user input date.

=DSum("[Invoice_amt]","tbl_Invoices","[Invoice_Date]
[forms]![Report Date Range]![Beginning Trans Date] AND
[tbl_Invoice_GL_ID] = [tbl_GL_Account_GL_ID]")

I hope I haven't confused you too much! Thanks

"Marshall Barton" wrote:

Now I'm confused again :-(

If the values you want to accumlate are not in the invoice
table, what table are they in??

I though you only needed to refine the DSum to restrict it
to the invoice amounts for the current transaction??

I guess the thing you tried aounds reasonable as long as the
amount is in the invoice table and the GL_ID is the linking
field, but you "don't get a result", which I suppose means
Null (nothing found).

I suppose that you might have to post the relevant tables
with their important field, the field types and which ones
are primary and foreign keys. Maybe then I will be able to
claw my way out of this fog I seem to be stuck in.
--
Marsh
MVP [MS Access]



Tina wrote:
I can't seem to get that to work. I don't have any invoice information in
my form, but my group is grouped by tbl_Account_GL_ID. I have tried to = the
tbl_Account_GL_ID to the tbl_transactions GL_ID but I don't get a result. I
don't have any data in the forms only the tables.

The GL_ID links the invoices to the group account.


"Marshall Barton" wrote:
Seems like you're on the right track. Just add another
criteria for the invoice ID (I don't know what that would be
in the form or table).

=DSum("[Invoice_amt]","tbl_Invoices","[Invoice_Date]
[forms]![Report Date Range]![Beginning Trans Date] AND
[Invoice ID] = [forms]![Report Date Range]![Invoice ID]")

Don't tell me! Next you're going to tell me that you have
to include the older revisions in that calculation too ;-)


Tina wrote:
I've gotten the following to work:
=DSum("[Invoice_amt]","tbl_Invoices","[Invoice_Date] [forms]![Report Date
Range]![Beginning Trans Date]")

but it only gives me the the total of all invoices previous to this date and
not just for that one group. I added the txt field to the group footer.
Every group show's the same amount.

"Tina" wrote:
Thanks Marshall that worked great. I have another question for you if thats
ok.

My report lists transactions (and revision amounts - subreport) for the
group between dates entered by the user.

I have a planned budget field that is the initial amt allocated to that grp.
At the end of the report I get a balance of all the transactions for that
period then subtract the planned budget (=[Planned Budget] - Sum([Invoice]))
-/+ revision(s).

My problem is that transactions that happened before the date entered by the
user will effect the initial planned budget and it only lists transaction
that are between the specified dates.

Is there some way that I can accumulate the previous transaction_amts that
are the beggining_trans_date entered by the user based on the
transaction_date. I will use this total to subtract from the Planned Budget.
I want an up to date amt even though the user is only looking at the report
for a certain period, what happened to the account before the
beggining_trans_date is relevant otherwise my totals won't be precise.

Should I create another txt field or try and do this in my control source
(query). I don't know how to go about doing this..

transaction_amt
transaction_date
Forms![beggining_trans_date]


"Marshall Barton" wrote:
First get the total for the subreport in a text box named
txtGrpTotal in the subreport's Report Footer section by
using an expression like:
=Sum(IIf(revision_operator="Add", 1, -1) * Revision_Amt)

Now we have a single value for the main report to use. The
group footer (that contains the subreport) can then use a
text box with the expression:
=[Planned Budget] - Sum([Invoice]) +
IIf([tbl_Budget_revision].Report.HasData,[tbl_Budget_revision].Report.txtGrpTotal,0)

Hi, forget about this. I'm just going to do some other calculation.

Instead could you help me again with the following:

=[Planned Budget] - Sum([Invoice)] -[dsum] +
IIf([tbl_Budget_revision].Report.HasData,
IIf([tbl_Budget_revision].Report!revision_operator="Add", 1,
-1) * [tbl_Budget_revision].Report!Revision_Amt, 0)

I have added a txt field that performs a calculation [dsum]. The statement
above works great but when there is no amt contained in [dsum] it causes
nothing to output for the statement above. Is there a way to output the
statement above without any data in [dsum]?

"Tina" wrote:

Hi,

Ok, I have two tables, tbl_Invoices and tbl_GL_Accounts.

tbl_Invoices consists of
Invoice_ID (auto #) (PK)
GL_ID (reference from tbl_GL_Accounts)(FK)
Invoice_Amt
Invoice_Date

tbl_GL_Accounts consists of:
GL_ID (auto #)(PK)
GL_Name
GL_Budget etc...

I have created a query based on these two tables, then took that query and
am using it in my report. My field list in my report shows that I have
tbl_invoice_GL_ID and tbl_GL_Account_GL_ID. So in the DSum expression I am
trying to equal those two fields and I get nothing. Though if I remove the
AND statement I get the sum of all the invoices before the user input date.

=DSum("[Invoice_amt]","tbl_Invoices","[Invoice_Date]
[forms]![Report Date Range]![Beginning Trans Date] AND
[tbl_Invoice_GL_ID] = [tbl_GL_Account_GL_ID]")

I hope I haven't confused you too much! Thanks

"Marshall Barton" wrote:

Now I'm confused again :-(

If the values you want to accumlate are not in the invoice
table, what table are they in??

I though you only needed to refine the DSum to restrict it
to the invoice amounts for the current transaction??

I guess the thing you tried aounds reasonable as long as the
amount is in the invoice table and the GL_ID is the linking
field, but you "don't get a result", which I suppose means
Null (nothing found).

I suppose that you might have to post the relevant tables
with their important field, the field types and which ones
are primary and foreign keys. Maybe then I will be able to
claw my way out of this fog I seem to be stuck in.
--
Marsh
MVP [MS Access]



Tina wrote:
I can't seem to get that to work. I don't have any invoice information in
my form, but my group is grouped by tbl_Account_GL_ID. I have tried to = the
tbl_Account_GL_ID to the tbl_transactions GL_ID but I don't get a result. I
don't have any data in the forms only the tables.

The GL_ID links the invoices to the group account.


"Marshall Barton" wrote:
Seems like you're on the right track. Just add another
criteria for the invoice ID (I don't know what that would be
in the form or table).

=DSum("[Invoice_amt]","tbl_Invoices","[Invoice_Date]
[forms]![Report Date Range]![Beginning Trans Date] AND
[Invoice ID] = [forms]![Report Date Range]![Invoice ID]")

Don't tell me! Next you're going to tell me that you have
to include the older revisions in that calculation too ;-)


Tina wrote:
I've gotten the following to work:
=DSum("[Invoice_amt]","tbl_Invoices","[Invoice_Date] [forms]![Report Date
Range]![Beginning Trans Date]")

but it only gives me the the total of all invoices previous to this date and
not just for that one group. I added the txt field to the group footer.
Every group show's the same amount.

"Tina" wrote:
Thanks Marshall that worked great. I have another question for you if thats
ok.

My report lists transactions (and revision amounts - subreport) for the
group between dates entered by the user.

I have a planned budget field that is the initial amt allocated to that grp.
At the end of the report I get a balance of all the transactions for that
period then subtract the planned budget (=[Planned Budget] - Sum([Invoice]))
-/+ revision(s).

My problem is that transactions that happened before the date entered by the
user will effect the initial planned budget and it only lists transaction
that are between the specified dates.

Is there some way that I can accumulate the previous transaction_amts that
are the beggining_trans_date entered by the user based on the
transaction_date. I will use this total to subtract from the Planned Budget.
I want an up to date amt even though the user is only looking at the report
for a certain period, what happened to the account before the
beggining_trans_date is relevant otherwise my totals won't be precise.

Should I create another txt field or try and do this in my control source
(query). I don't know how to go about doing this..

transaction_amt
transaction_date
Forms![beggining_trans_date]


"Marshall Barton" wrote:
First get the total for the subreport in a text box named
txtGrpTotal in the subreport's Report Footer section by
using an expression like:
=Sum(IIf(revision_operator="Add", 1, -1) * Revision_Amt)

Now we have a single value for the main report to use. The
group footer (that contains the subreport) can then use a
text box with the expression:
=[Planned Budget] - Sum([Invoice]) +
IIf([tbl_Budget_revision].Report.HasData,[tbl_Budget_revision].Report.txtGrpTotal,0)

It looks like you're using a _ instead of a . here. Also
the [tbl_GL_Account_GL_ID] reference need to refer to the
value in the report, not to a table the DSum never heard of.

=DSum("[Invoice_amt]","tbl_Invoices","[Invoice_Date]
Forms![Report Date Range]![Beginning Trans Date] AND
GL_ID = Forms![Report Date Range]!GL_ID]")

or maybe that last line should be:
GL_ID = Reports![nameofreport]!GL_ID]")
--
Marsh
MVP [MS Access]



Tina wrote:
Ok, I have two tables, tbl_Invoices and tbl_GL_Accounts.

tbl_Invoices consists of
Invoice_ID (auto #) (PK)
GL_ID (reference from tbl_GL_Accounts)(FK)
Invoice_Amt
Invoice_Date

tbl_GL_Accounts consists of:
GL_ID (auto #)(PK)
GL_Name
GL_Budget etc...

I have created a query based on these two tables, then took that query and
am using it in my report. My field list in my report shows that I have
tbl_invoice_GL_ID and tbl_GL_Account_GL_ID. So in the DSum expression I am
trying to equal those two fields and I get nothing. Though if I remove the
AND statement I get the sum of all the invoices before the user input date.

=DSum("[Invoice_amt]","tbl_Invoices","[Invoice_Date]
[forms]![Report Date Range]![Beginning Trans Date] AND
[tbl_Invoice_GL_ID] = [tbl_GL_Account_GL_ID]")

I hope I haven't confused you too much! Thanks

"Marshall Barton" wrote:

Now I'm confused again :-(

If the values you want to accumlate are not in the invoice
table, what table are they in??

I though you only needed to refine the DSum to restrict it
to the invoice amounts for the current transaction??

I guess the thing you tried aounds reasonable as long as the
amount is in the invoice table and the GL_ID is the linking
field, but you "don't get a result", which I suppose means
Null (nothing found).

I suppose that you might have to post the relevant tables
with their important field, the field types and which ones
are primary and foreign keys. Maybe then I will be able to
claw my way out of this fog I seem to be stuck in.
--
Marsh
MVP [MS Access]



Tina wrote:
I can't seem to get that to work. I don't have any invoice information in
my form, but my group is grouped by tbl_Account_GL_ID. I have tried to = the
tbl_Account_GL_ID to the tbl_transactions GL_ID but I don't get a result. I
don't have any data in the forms only the tables.

The GL_ID links the invoices to the group account.


"Marshall Barton" wrote:
Seems like you're on the right track. Just add another
criteria for the invoice ID (I don't know what that would be
in the form or table).

=DSum("[Invoice_amt]","tbl_Invoices","[Invoice_Date]
[forms]![Report Date Range]![Beginning Trans Date] AND
[Invoice ID] = [forms]![Report Date Range]![Invoice ID]")

Don't tell me! Next you're going to tell me that you have
to include the older revisions in that calculation too ;-)


Tina wrote:
I've gotten the following to work:
=DSum("[Invoice_amt]","tbl_Invoices","[Invoice_Date] [forms]![Report Date
Range]![Beginning Trans Date]")

but it only gives me the the total of all invoices previous to this date and
not just for that one group. I added the txt field to the group footer.
Every group show's the same amount.

"Tina" wrote:
Thanks Marshall that worked great. I have another question for you if thats
ok.

My report lists transactions (and revision amounts - subreport) for the
group between dates entered by the user.

I have a planned budget field that is the initial amt allocated to that grp.
At the end of the report I get a balance of all the transactions for that
period then subtract the planned budget (=[Planned Budget] - Sum([Invoice]))
-/+ revision(s).

My problem is that transactions that happened before the date entered by the
user will effect the initial planned budget and it only lists transaction
that are between the specified dates.

Is there some way that I can accumulate the previous transaction_amts that
are the beggining_trans_date entered by the user based on the
transaction_date. I will use this total to subtract from the Planned Budget.
I want an up to date amt even though the user is only looking at the report
for a certain period, what happened to the account before the
beggining_trans_date is relevant otherwise my totals won't be precise.

Should I create another txt field or try and do this in my control source
(query). I don't know how to go about doing this..

transaction_amt
transaction_date
Forms![beggining_trans_date]


"Marshall Barton" wrote:
First get the total for the subreport in a text box named
txtGrpTotal in the subreport's Report Footer section by
using an expression like:
=Sum(IIf(revision_operator="Add", 1, -1) * Revision_Amt)

Now we have a single value for the main report to use. The
group footer (that contains the subreport) can then use a
text box with the expression:
=[Planned Budget] - Sum([Invoice]) +
IIf([tbl_Budget_revision].Report.HasData,[tbl_Budget_revision].Report.txtGrpTotal,0)

Whoops, I missed this message earlier.

No can do. The aggregate functions don't know anything
about forms/reports, much less about their properties or
controls.
--
Marsh
MVP [MS Access]


Tina wrote:
Hi, forget about this. I'm just going to do some other calculation.

Instead could you help me again with the following:

=[Planned Budget] - Sum([Invoice)] -[dsum] +
IIf([tbl_Budget_revision].Report.HasData,
IIf([tbl_Budget_revision].Report!revision_operator="Add", 1,
-1) * [tbl_Budget_revision].Report!Revision_Amt, 0)

I have added a txt field that performs a calculation [dsum]. The statement
above works great but when there is no amt contained in [dsum] it causes
nothing to output for the statement above. Is there a way to output the
statement above without any data in [dsum]?


"Tina" wrote:
Ok, I have two tables, tbl_Invoices and tbl_GL_Accounts.

tbl_Invoices consists of
Invoice_ID (auto #) (PK)
GL_ID (reference from tbl_GL_Accounts)(FK)
Invoice_Amt
Invoice_Date

tbl_GL_Accounts consists of:
GL_ID (auto #)(PK)
GL_Name
GL_Budget etc...

I have created a query based on these two tables, then took that query and
am using it in my report. My field list in my report shows that I have
tbl_invoice_GL_ID and tbl_GL_Account_GL_ID. So in the DSum expression I am
trying to equal those two fields and I get nothing. Though if I remove the
AND statement I get the sum of all the invoices before the user input date.

=DSum("[Invoice_amt]","tbl_Invoices","[Invoice_Date]
[forms]![Report Date Range]![Beginning Trans Date] AND
[tbl_Invoice_GL_ID] = [tbl_GL_Account_GL_ID]")

I hope I haven't confused you too much! Thanks

"Marshall Barton" wrote:

Now I'm confused again :-(

If the values you want to accumlate are not in the invoice
table, what table are they in??

I though you only needed to refine the DSum to restrict it
to the invoice amounts for the current transaction??

I guess the thing you tried aounds reasonable as long as the
amount is in the invoice table and the GL_ID is the linking
field, but you "don't get a result", which I suppose means
Null (nothing found).

I suppose that you might have to post the relevant tables
with their important field, the field types and which ones
are primary and foreign keys. Maybe then I will be able to
claw my way out of this fog I seem to be stuck in.
--
Marsh
MVP [MS Access]



Tina wrote:
I can't seem to get that to work. I don't have any invoice information in
my form, but my group is grouped by tbl_Account_GL_ID. I have tried to = the
tbl_Account_GL_ID to the tbl_transactions GL_ID but I don't get a result. I
don't have any data in the forms only the tables.

The GL_ID links the invoices to the group account.


"Marshall Barton" wrote:
Seems like you're on the right track. Just add another
criteria for the invoice ID (I don't know what that would be
in the form or table).

=DSum("[Invoice_amt]","tbl_Invoices","[Invoice_Date]
[forms]![Report Date Range]![Beginning Trans Date] AND
[Invoice ID] = [forms]![Report Date Range]![Invoice ID]")

Don't tell me! Next you're going to tell me that you have
to include the older revisions in that calculation too ;-)


Tina wrote:
I've gotten the following to work:
=DSum("[Invoice_amt]","tbl_Invoices","[Invoice_Date] [forms]![Report Date
Range]![Beginning Trans Date]")

but it only gives me the the total of all invoices previous to this date and
not just for that one group. I added the txt field to the group footer.
Every group show's the same amount.

"Tina" wrote:
Thanks Marshall that worked great. I have another question for you if thats
ok.

My report lists transactions (and revision amounts - subreport) for the
group between dates entered by the user.

I have a planned budget field that is the initial amt allocated to that grp.
At the end of the report I get a balance of all the transactions for that
period then subtract the planned budget (=[Planned Budget] - Sum([Invoice]))
-/+ revision(s).

My problem is that transactions that happened before the date entered by the
user will effect the initial planned budget and it only lists transaction
that are between the specified dates.

Is there some way that I can accumulate the previous transaction_amts that
are the beggining_trans_date entered by the user based on the
transaction_date. I will use this total to subtract from the Planned Budget.
I want an up to date amt even though the user is only looking at the report
for a certain period, what happened to the account before the
beggining_trans_date is relevant otherwise my totals won't be precise.

Should I create another txt field or try and do this in my control source
(query). I don't know how to go about doing this..

transaction_amt
transaction_date
Forms![beggining_trans_date]


"Marshall Barton" wrote:
First get the total for the subreport in a text box named
txtGrpTotal in the subreport's Report Footer section by
using an expression like:
=Sum(IIf(revision_operator="Add", 1, -1) * Revision_Amt)

Now we have a single value for the main report to use. The
group footer (that contains the subreport) can then use a
text box with the expression:
=[Planned Budget] - Sum([Invoice]) +
IIf([tbl_Budget_revision].Report.HasData,[tbl_Budget_revision].Report.txtGrpTotal,0)