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
|
|||
|
|||
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) |
#12
|
|||
|
|||
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) |
#13
|
|||
|
|||
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) |
#14
|
|||
|
|||
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) |
#15
|
|||
|
|||
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) |
#16
|
|||
|
|||
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) |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Totals on Main Report | Brad | Setting Up & Running Reports | 6 | January 18th, 2005 02:49 PM |
incorrect sums in report using 2 tables | jkendrick75 | Setting Up & Running Reports | 22 | December 13th, 2004 02:19 PM |
Still Hoping for help with a Query problem | Don Sealer | Using Forms | 15 | November 13th, 2004 06:24 AM |
Save Report With CreateReport Coding Issue | Jeff Conrad | Setting Up & Running Reports | 8 | July 12th, 2004 08:39 AM |
Report totals Query Total Vs Table Total | Michael Noblet | Setting Up & Running Reports | 1 | May 25th, 2004 05:17 PM |