|
| 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 |
|
#1
December 19th, 2006, 01:43 AM
posted to microsoft.public.access.reports
|
|||
|
|||
Report Examples
Does anyone have an example of a query & report that is user defined monetary
amounts calculating current quarter and also previous quarters as a total? (showing all records and not listing each quarter separately) I really need help... I created a db and this is the only hold up to completion. 
|
|
#2
December 19th, 2006, 04:11 AM
posted to microsoft.public.access.reports
|
|||
|
|||
|
Report Examples
If you really need help, you should provide some table/record source
structure and possibly sample records with desired display in your report. I'm not sure what you mean by "user defined monetary amounts". -- Duane Hookom Microsoft Access MVP "chickalina" wrote: Does anyone have an example of a query & report that is user defined monetary amounts calculating current quarter and also previous quarters as a total? (showing all records and not listing each quarter separately) I really need help... I created a db and this is the only hold up to completion.
|
|
#3
December 19th, 2006, 04:28 AM
posted to microsoft.public.access.reports
|
|||
|
|||
|
Report Examples
OK Here goes:
Thanks in advance! tbl_Issues IssueID IssueDescription IssueType tbl_ReserveSchedule IssueID (dual Primary key with Change Date) ChangeDate TaxAccrual - this is a number TaxAccrual - this is a number TaxInterest - number (18 fields all together) qry_Issues_Log_Reserve1 IssueID IssueDescription TaxPeriod ChangeDate Quarter: using Format [ChangeDate] for qyyyy in the Field: with [Enter Date] for user prompt to enter quarter they want to run the report for TAXACC - IIF statement to account for Nulls TaxInterest - number TAXINT - IIF (incidentally there are 28 fields plus their IIF statements) TAXTTOTAL: to total up all the tax INTTOTAL: to total up all the Interest What I want the report to look like Country PriorBalance Quarter Selected Info QtrTOT Tax Interest TaxAcc TaxInt OtherTax OtherInt TotTax TotaInt TOTAL Issue 1 2 1 1 1 1 2 2 11 Issue 2 1 1 1 1 1 1 1 9 Cntry 3 3 2 2 2 2 3 3 20 I couldnt' fit one more column, but there would be the previous balance + the current quarter for the Actual Balance Then the report would total at the end for a grand total of everything. "Duane Hookom" wrote: If you really need help, you should provide some table/record source structure and possibly sample records with desired display in your report. I'm not sure what you mean by "user defined monetary amounts". -- Duane Hookom Microsoft Access MVP "chickalina" wrote: Does anyone have an example of a query & report that is user defined monetary amounts calculating current quarter and also previous quarters as a total? (showing all records and not listing each quarter separately) I really need help... I created a db and this is the only hold up to completion.
|
|
#4
December 19th, 2006, 05:45 AM
posted to microsoft.public.access.reports
|
|||
|
|||
|
Report Examples
Sorry, this is just too much for me to digest. In addition to issues with
wrapping, you seem to have 2 fields in a table with the same name. Can you narrow your question down to a single (or maybe two) small issues that might provide a method to resolve the remainder of your issues? -- Duane Hookom Microsoft Access MVP "chickalina" wrote: OK Here goes: Thanks in advance! tbl_Issues IssueID IssueDescription IssueType tbl_ReserveSchedule IssueID (dual Primary key with Change Date) ChangeDate TaxAccrual - this is a number TaxAccrual - this is a number TaxInterest - number (18 fields all together) qry_Issues_Log_Reserve1 IssueID IssueDescription TaxPeriod ChangeDate Quarter: using Format [ChangeDate] for qyyyy in the Field: with [Enter Date] for user prompt to enter quarter they want to run the report for TAXACC - IIF statement to account for Nulls TaxInterest - number TAXINT - IIF (incidentally there are 28 fields plus their IIF statements) TAXTTOTAL: to total up all the tax INTTOTAL: to total up all the Interest What I want the report to look like Country PriorBalance Quarter Selected Info QtrTOT Tax Interest TaxAcc TaxInt OtherTax OtherInt TotTax TotaInt TOTAL Issue 1 2 1 1 1 1 2 2 11 Issue 2 1 1 1 1 1 1 1 9 Cntry 3 3 2 2 2 2 3 3 20 I couldnt' fit one more column, but there would be the previous balance + the current quarter for the Actual Balance Then the report would total at the end for a grand total of everything. "Duane Hookom" wrote: If you really need help, you should provide some table/record source structure and possibly sample records with desired display in your report. I'm not sure what you mean by "user defined monetary amounts". -- Duane Hookom Microsoft Access MVP "chickalina" wrote: Does anyone have an example of a query & report that is user defined monetary amounts calculating current quarter and also previous quarters as a total? (showing all records and not listing each quarter separately) I really need help... I created a db and this is the only hold up to completion.
|
|
#5
December 19th, 2006, 05:59 AM
posted to microsoft.public.access.reports
|
|||
|
|||
|
Report Examples
Here's a smaller version
Country prvbal curbal tax int 1tax 1int 2tax 2int txtot inttol currtot actbal issue1 1 1 1 1 1 1 2 2 4 6 issue2 2 2 2 2 2 2 2 2 12 16 country 3 3 3 3 3 3 3 3 16 24 "Duane Hookom" wrote: Sorry, this is just too much for me to digest. In addition to issues with wrapping, you seem to have 2 fields in a table with the same name. Can you narrow your question down to a single (or maybe two) small issues that might provide a method to resolve the remainder of your issues? -- Duane Hookom Microsoft Access MVP "chickalina" wrote: OK Here goes: Thanks in advance! tbl_Issues IssueID IssueDescription IssueType tbl_ReserveSchedule IssueID (dual Primary key with Change Date) ChangeDate TaxAccrual - this is a number TaxAccrual - this is a number TaxInterest - number (18 fields all together) qry_Issues_Log_Reserve1 IssueID IssueDescription TaxPeriod ChangeDate Quarter: using Format [ChangeDate] for qyyyy in the Field: with [Enter Date] for user prompt to enter quarter they want to run the report for TAXACC - IIF statement to account for Nulls TaxInterest - number TAXINT - IIF (incidentally there are 28 fields plus their IIF statements) TAXTTOTAL: to total up all the tax INTTOTAL: to total up all the Interest What I want the report to look like Country PriorBalance Quarter Selected Info QtrTOT Tax Interest TaxAcc TaxInt OtherTax OtherInt TotTax TotaInt TOTAL Issue 1 2 1 1 1 1 2 2 11 Issue 2 1 1 1 1 1 1 1 9 Cntry 3 3 2 2 2 2 3 3 20 I couldnt' fit one more column, but there would be the previous balance + the current quarter for the Actual Balance Then the report would total at the end for a grand total of everything. "Duane Hookom" wrote: If you really need help, you should provide some table/record source structure and possibly sample records with desired display in your report. I'm not sure what you mean by "user defined monetary amounts". -- Duane Hookom Microsoft Access MVP "chickalina" wrote: Does anyone have an example of a query & report that is user defined monetary amounts calculating current quarter and also previous quarters as a total? (showing all records and not listing each quarter separately) I really need help... I created a db and this is the only hold up to completion.
|
|
#6
December 19th, 2006, 02:27 PM
posted to microsoft.public.access.reports
|
|||
|
|||
|
Report Examples
I was expecting you to narrow your request to a single issue that you could
use to learn from and model the solution to your bigger issue. Instead, you repeated some information so that it displays narrower. If you want to display a sum of [Interest] for the current quarter, you could use an expression in a query like: CurrQuarterInt: Sum(Abs(Format([ChangeDate],"yyyyq") = Format(Date(),"yyyyq") ) * [Interest]) To get last quarters interest, you would subtract a quarter from Date() using DateAdd(). -- Duane Hookom Microsoft Access MVP "chickalina" wrote: Here's a smaller version Country prvbal curbal tax int 1tax 1int 2tax 2int txtot inttol currtot actbal issue1 1 1 1 1 1 1 2 2 4 6 issue2 2 2 2 2 2 2 2 2 12 16 country 3 3 3 3 3 3 3 3 16 24 "Duane Hookom" wrote: Sorry, this is just too much for me to digest. In addition to issues with wrapping, you seem to have 2 fields in a table with the same name. Can you narrow your question down to a single (or maybe two) small issues that might provide a method to resolve the remainder of your issues? -- Duane Hookom Microsoft Access MVP "chickalina" wrote: OK Here goes: Thanks in advance! tbl_Issues IssueID IssueDescription IssueType tbl_ReserveSchedule IssueID (dual Primary key with Change Date) ChangeDate TaxAccrual - this is a number TaxAccrual - this is a number TaxInterest - number (18 fields all together) qry_Issues_Log_Reserve1 IssueID IssueDescription TaxPeriod ChangeDate Quarter: using Format [ChangeDate] for qyyyy in the Field: with [Enter Date] for user prompt to enter quarter they want to run the report for TAXACC - IIF statement to account for Nulls TaxInterest - number TAXINT - IIF (incidentally there are 28 fields plus their IIF statements) TAXTTOTAL: to total up all the tax INTTOTAL: to total up all the Interest What I want the report to look like Country PriorBalance Quarter Selected Info QtrTOT Tax Interest TaxAcc TaxInt OtherTax OtherInt TotTax TotaInt TOTAL Issue 1 2 1 1 1 1 2 2 11 Issue 2 1 1 1 1 1 1 1 9 Cntry 3 3 2 2 2 2 3 3 20 I couldnt' fit one more column, but there would be the previous balance + the current quarter for the Actual Balance Then the report would total at the end for a grand total of everything. "Duane Hookom" wrote: If you really need help, you should provide some table/record source structure and possibly sample records with desired display in your report. I'm not sure what you mean by "user defined monetary amounts". -- Duane Hookom Microsoft Access MVP "chickalina" wrote: Does anyone have an example of a query & report that is user defined monetary amounts calculating current quarter and also previous quarters as a total? (showing all records and not listing each quarter separately) I really need help... I created a db and this is the only hold up to completion.
|
|
#7
December 19th, 2006, 03:04 PM
posted to microsoft.public.access.reports
|
|||
|
|||
|
Report Examples
I have to do this for all 20 fields?
Also, the date would not be Date(). It would be a [Enter Date] Criteria on my Quarter: Format... and each field would need also require a [Enter Date] to calculate the prior balance? "Duane Hookom" wrote: I was expecting you to narrow your request to a single issue that you could use to learn from and model the solution to your bigger issue. Instead, you repeated some information so that it displays narrower. If you want to display a sum of [Interest] for the current quarter, you could use an expression in a query like: CurrQuarterInt: Sum(Abs(Format([ChangeDate],"yyyyq") = Format(Date(),"yyyyq") ) * [Interest]) To get last quarters interest, you would subtract a quarter from Date() using DateAdd(). -- Duane Hookom Microsoft Access MVP "chickalina" wrote: Here's a smaller version Country prvbal curbal tax int 1tax 1int 2tax 2int txtot inttol currtot actbal issue1 1 1 1 1 1 1 2 2 4 6 issue2 2 2 2 2 2 2 2 2 12 16 country 3 3 3 3 3 3 3 3 16 24 "Duane Hookom" wrote: Sorry, this is just too much for me to digest. In addition to issues with wrapping, you seem to have 2 fields in a table with the same name. Can you narrow your question down to a single (or maybe two) small issues that might provide a method to resolve the remainder of your issues? -- Duane Hookom Microsoft Access MVP "chickalina" wrote: OK Here goes: Thanks in advance! tbl_Issues IssueID IssueDescription IssueType tbl_ReserveSchedule IssueID (dual Primary key with Change Date) ChangeDate TaxAccrual - this is a number TaxAccrual - this is a number TaxInterest - number (18 fields all together) qry_Issues_Log_Reserve1 IssueID IssueDescription TaxPeriod ChangeDate Quarter: using Format [ChangeDate] for qyyyy in the Field: with [Enter Date] for user prompt to enter quarter they want to run the report for TAXACC - IIF statement to account for Nulls TaxInterest - number TAXINT - IIF (incidentally there are 28 fields plus their IIF statements) TAXTTOTAL: to total up all the tax INTTOTAL: to total up all the Interest What I want the report to look like Country PriorBalance Quarter Selected Info QtrTOT Tax Interest TaxAcc TaxInt OtherTax OtherInt TotTax TotaInt TOTAL Issue 1 2 1 1 1 1 2 2 11 Issue 2 1 1 1 1 1 1 1 9 Cntry 3 3 2 2 2 2 3 3 20 I couldnt' fit one more column, but there would be the previous balance + the current quarter for the Actual Balance Then the report would total at the end for a grand total of everything. "Duane Hookom" wrote: If you really need help, you should provide some table/record source structure and possibly sample records with desired display in your report. I'm not sure what you mean by "user defined monetary amounts". -- Duane Hookom Microsoft Access MVP "chickalina" wrote: Does anyone have an example of a query & report that is user defined monetary amounts calculating current quarter and also previous quarters as a total? (showing all records and not listing each quarter separately) I really need help... I created a db and this is the only hold up to completion.
|
|
#8
December 19th, 2006, 03:15 PM
posted to microsoft.public.access.reports
|
|||
|
|||
|
Report Examples
Yes, you would probably need to do this for 20 fields. It may be an issue
with your table structure being un-normalized causing repetitive, similar calculations. If you aren't comparing with Date() the replace Date() with your comparison date. -- Duane Hookom Microsoft Access MVP "chickalina" wrote: I have to do this for all 20 fields? Also, the date would not be Date(). It would be a [Enter Date] Criteria on my Quarter: Format... and each field would need also require a [Enter Date] to calculate the prior balance? "Duane Hookom" wrote: I was expecting you to narrow your request to a single issue that you could use to learn from and model the solution to your bigger issue. Instead, you repeated some information so that it displays narrower. If you want to display a sum of [Interest] for the current quarter, you could use an expression in a query like: CurrQuarterInt: Sum(Abs(Format([ChangeDate],"yyyyq") = Format(Date(),"yyyyq") ) * [Interest]) To get last quarters interest, you would subtract a quarter from Date() using DateAdd(). -- Duane Hookom Microsoft Access MVP "chickalina" wrote: Here's a smaller version Country prvbal curbal tax int 1tax 1int 2tax 2int txtot inttol currtot actbal issue1 1 1 1 1 1 1 2 2 4 6 issue2 2 2 2 2 2 2 2 2 12 16 country 3 3 3 3 3 3 3 3 16 24 "Duane Hookom" wrote: Sorry, this is just too much for me to digest. In addition to issues with wrapping, you seem to have 2 fields in a table with the same name. Can you narrow your question down to a single (or maybe two) small issues that might provide a method to resolve the remainder of your issues? -- Duane Hookom Microsoft Access MVP "chickalina" wrote: OK Here goes: Thanks in advance! tbl_Issues IssueID IssueDescription IssueType tbl_ReserveSchedule IssueID (dual Primary key with Change Date) ChangeDate TaxAccrual - this is a number TaxAccrual - this is a number TaxInterest - number (18 fields all together) qry_Issues_Log_Reserve1 IssueID IssueDescription TaxPeriod ChangeDate Quarter: using Format [ChangeDate] for qyyyy in the Field: with [Enter Date] for user prompt to enter quarter they want to run the report for TAXACC - IIF statement to account for Nulls TaxInterest - number TAXINT - IIF (incidentally there are 28 fields plus their IIF statements) TAXTTOTAL: to total up all the tax INTTOTAL: to total up all the Interest What I want the report to look like Country PriorBalance Quarter Selected Info QtrTOT Tax Interest TaxAcc TaxInt OtherTax OtherInt TotTax TotaInt TOTAL Issue 1 2 1 1 1 1 2 2 11 Issue 2 1 1 1 1 1 1 1 9 Cntry 3 3 2 2 2 2 3 3 20 I couldnt' fit one more column, but there would be the previous balance + the current quarter for the Actual Balance Then the report would total at the end for a grand total of everything. "Duane Hookom" wrote: If you really need help, you should provide some table/record source structure and possibly sample records with desired display in your report. I'm not sure what you mean by "user defined monetary amounts". -- Duane Hookom Microsoft Access MVP "chickalina" wrote: Does anyone have an example of a query & report that is user defined monetary amounts calculating current quarter and also previous quarters as a total? (showing all records and not listing each quarter separately) I really need help... I created a db and this is the only hold up to completion.
|
|
#9
December 19th, 2006, 03:25 PM
posted to microsoft.public.access.reports
|
|||
|
|||
|
Report Examples
The table structure is not un-normalized because each field is needed. Each
of the 20 fields represents something different. I think that's the only reason for the repetitive, similar calculations. So for the quarter needed I would use: CurrQuarterInt: Sum(Abs(Format([Quarter],"yyyyq") = Format(Date(),"yyyyq") ) * [Interest]) where it would still prompt the user in the Quarter Field. and for the Previous Quarter Ending Balance (which is a total of everything up until that date: =DateAdd()[prevquarter]"q", [quarter]) I'd have to create a field for prevquarter? "Duane Hookom" wrote: Yes, you would probably need to do this for 20 fields. It may be an issue with your table structure being un-normalized causing repetitive, similar calculations. If you aren't comparing with Date() the replace Date() with your comparison date. -- Duane Hookom Microsoft Access MVP "chickalina" wrote: I have to do this for all 20 fields? Also, the date would not be Date(). It would be a [Enter Date] Criteria on my Quarter: Format... and each field would need also require a [Enter Date] to calculate the prior balance? "Duane Hookom" wrote: I was expecting you to narrow your request to a single issue that you could use to learn from and model the solution to your bigger issue. Instead, you repeated some information so that it displays narrower. If you want to display a sum of [Interest] for the current quarter, you could use an expression in a query like: CurrQuarterInt: Sum(Abs(Format([ChangeDate],"yyyyq") = Format(Date(),"yyyyq") ) * [Interest]) To get last quarters interest, you would subtract a quarter from Date() using DateAdd(). -- Duane Hookom Microsoft Access MVP "chickalina" wrote: Here's a smaller version Country prvbal curbal tax int 1tax 1int 2tax 2int txtot inttol currtot actbal issue1 1 1 1 1 1 1 2 2 4 6 issue2 2 2 2 2 2 2 2 2 12 16 country 3 3 3 3 3 3 3 3 16 24 "Duane Hookom" wrote: Sorry, this is just too much for me to digest. In addition to issues with wrapping, you seem to have 2 fields in a table with the same name. Can you narrow your question down to a single (or maybe two) small issues that might provide a method to resolve the remainder of your issues? -- Duane Hookom Microsoft Access MVP "chickalina" wrote: OK Here goes: Thanks in advance! tbl_Issues IssueID IssueDescription IssueType tbl_ReserveSchedule IssueID (dual Primary key with Change Date) ChangeDate TaxAccrual - this is a number TaxAccrual - this is a number TaxInterest - number (18 fields all together) qry_Issues_Log_Reserve1 IssueID IssueDescription TaxPeriod ChangeDate Quarter: using Format [ChangeDate] for qyyyy in the Field: with [Enter Date] for user prompt to enter quarter they want to run the report for TAXACC - IIF statement to account for Nulls TaxInterest - number TAXINT - IIF (incidentally there are 28 fields plus their IIF statements) TAXTTOTAL: to total up all the tax INTTOTAL: to total up all the Interest What I want the report to look like Country PriorBalance Quarter Selected Info QtrTOT Tax Interest TaxAcc TaxInt OtherTax OtherInt TotTax TotaInt TOTAL Issue 1 2 1 1 1 1 2 2 11 Issue 2 1 1 1 1 1 1 1 9 Cntry 3 3 2 2 2 2 3 3 20 I couldnt' fit one more column, but there would be the previous balance + the current quarter for the Actual Balance Then the report would total at the end for a grand total of everything. "Duane Hookom" wrote: If you really need help, you should provide some table/record source structure and possibly sample records with desired display in your report. I'm not sure what you mean by "user defined monetary amounts". -- Duane Hookom Microsoft Access MVP "chickalina" wrote: Does anyone have an example of a query & report that is user defined monetary amounts calculating current quarter and also previous quarters as a total? (showing all records and not listing each quarter separately) I really need help... I created a db and this is the only hold up to completion.
|
|
#10
December 19th, 2006, 05:34 PM
posted to microsoft.public.access.reports
|
|||
|
|||
|
Report Examples
I have tried everything with the DateAdd function... it only returns the date
not the total balance for the quarter before the quarter chosen. It would not actually be a -3 it would be a [EnterDate]. I'm really sorry I'm having a hard time explaining myself. User inputs 32006 for the [Enter Date] date, and I am trying to use DateAdd to calculate the entire total Tax or Interest before the specified quarter. What are trying to see is: Prev Qtr Bal (lump sum) List the Changes this quarter in columns separately A total of changes for that quarter Add Previous Qtr Bal to Changes to get a grand total. List all issues whether or not there were changes. is this better? I have total columns for all tax fields and interest fields... what Expression would I use to just add everything up for the previous quarter? "Duane Hookom" wrote: Yes, you would probably need to do this for 20 fields. It may be an issue with your table structure being un-normalized causing repetitive, similar calculations. If you aren't comparing with Date() the replace Date() with your comparison date. -- Duane Hookom Microsoft Access MVP "chickalina" wrote: I have to do this for all 20 fields? Also, the date would not be Date(). It would be a [Enter Date] Criteria on my Quarter: Format... and each field would need also require a [Enter Date] to calculate the prior balance? "Duane Hookom" wrote: I was expecting you to narrow your request to a single issue that you could use to learn from and model the solution to your bigger issue. Instead, you repeated some information so that it displays narrower. If you want to display a sum of [Interest] for the current quarter, you could use an expression in a query like: CurrQuarterInt: Sum(Abs(Format([ChangeDate],"yyyyq") = Format(Date(),"yyyyq") ) * [Interest]) To get last quarters interest, you would subtract a quarter from Date() using DateAdd(). -- Duane Hookom Microsoft Access MVP "chickalina" wrote: Here's a smaller version Country prvbal curbal tax int 1tax 1int 2tax 2int txtot inttol currtot actbal issue1 1 1 1 1 1 1 2 2 4 6 issue2 2 2 2 2 2 2 2 2 12 16 country 3 3 3 3 3 3 3 3 16 24 "Duane Hookom" wrote: Sorry, this is just too much for me to digest. In addition to issues with wrapping, you seem to have 2 fields in a table with the same name. Can you narrow your question down to a single (or maybe two) small issues that might provide a method to resolve the remainder of your issues? -- Duane Hookom Microsoft Access MVP "chickalina" wrote: OK Here goes: Thanks in advance! tbl_Issues IssueID IssueDescription IssueType tbl_ReserveSchedule IssueID (dual Primary key with Change Date) ChangeDate TaxAccrual - this is a number TaxAccrual - this is a number TaxInterest - number (18 fields all together) qry_Issues_Log_Reserve1 IssueID IssueDescription TaxPeriod ChangeDate Quarter: using Format [ChangeDate] for qyyyy in the Field: with [Enter Date] for user prompt to enter quarter they want to run the report for TAXACC - IIF statement to account for Nulls TaxInterest - number TAXINT - IIF (incidentally there are 28 fields plus their IIF statements) TAXTTOTAL: to total up all the tax INTTOTAL: to total up all the Interest What I want the report to look like Country PriorBalance Quarter Selected Info QtrTOT Tax Interest TaxAcc TaxInt OtherTax OtherInt TotTax TotaInt TOTAL Issue 1 2 1 1 1 1 2 2 11 Issue 2 1 1 1 1 1 1 1 9 Cntry 3 3 2 2 2 2 3 3 20 I couldnt' fit one more column, but there would be the previous balance + the current quarter for the Actual Balance Then the report would total at the end for a grand total of everything. "Duane Hookom" wrote: If you really need help, you should provide some table/record source structure and possibly sample records with desired display in your report. I'm not sure what you mean by "user defined monetary amounts". -- Duane Hookom Microsoft Access MVP "chickalina" wrote: Does anyone have an example of a query & report that is user defined monetary amounts calculating current quarter and also previous quarters as a total? (showing all records and not listing each quarter separately) I really need help... I created a db and this is the only hold up to completion.
|
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
