Report Examples

[chickalina]

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.

 

[Duane Hookom]

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

 

[chickalina]

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]

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

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.

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

 

[chickalina]

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

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

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.

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


:

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.

 

[chickalina]

I used that... Access said to take off the last parentheses and when I ran
the query I got:
"
You tried to execute a query that does not include the specified expression
"IssueID" as part of an aggregate function."

If you want the user to enter a qyyyy then try:
CurrQuarterInt: Sum(Abs([Quarter]) = Format([ChangeDate],"qyyyy") ) *
[Interest])

This will create a column of values where the changedate equals the
[Quarter] value entered by the user.

--
Duane Hookom
Microsoft Access MVP

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?

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


:

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?

:

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


:

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



:

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


:

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.

:

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


:

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.

 

[Duane Hookom]

You took off the wrong ). Try
CurrQuarterInt: Sum(Abs([Quarter] = Format([ChangeDate],"qyyyy") ) *
[Interest])


--
Duane Hookom
Microsoft Access MVP

I used that... Access said to take off the last parentheses and when I ran
the query I got:
"
You tried to execute a query that does not include the specified expression
"IssueID" as part of an aggregate function."

If you want the user to enter a qyyyy then try:
CurrQuarterInt: Sum(Abs([Quarter]) = Format([ChangeDate],"qyyyy") ) *
[Interest])

This will create a column of values where the changedate equals the
[Quarter] value entered by the user.

--
Duane Hookom
Microsoft Access MVP

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?

:

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


:

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?

:

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


:

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



:

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


:

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.

:

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


:

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.

 

[chickalina]

I spent soooooooooo much time on this DB, so this concludes that I must not
be destined to be a developer...

Now when I put that in there, I get "Cannot group on Fields selected with a
'*'

You took off the wrong ). Try
CurrQuarterInt: Sum(Abs([Quarter] = Format([ChangeDate],"qyyyy") ) *
[Interest])


--
Duane Hookom
Microsoft Access MVP

I used that... Access said to take off the last parentheses and when I ran
the query I got:
"
You tried to execute a query that does not include the specified expression
"IssueID" as part of an aggregate function."

If you want the user to enter a qyyyy then try:
CurrQuarterInt: Sum(Abs([Quarter]) = Format([ChangeDate],"qyyyy") ) *
[Interest])

This will create a column of values where the changedate equals the
[Quarter] value entered by the user.

--
Duane Hookom
Microsoft Access MVP


:

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?

:

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


:

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?

:

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


:

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



:

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


:

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.

:

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


:

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.

 

[chickalina]

And the worst part is? I have to have this done by Friday and I am hopelessly
lost. It's just not clicking.

I used that... Access said to take off the last parentheses and when I ran
the query I got:
"
You tried to execute a query that does not include the specified expression
"IssueID" as part of an aggregate function."

If you want the user to enter a qyyyy then try:
CurrQuarterInt: Sum(Abs([Quarter]) = Format([ChangeDate],"qyyyy") ) *
[Interest])

This will create a column of values where the changedate equals the
[Quarter] value entered by the user.

--
Duane Hookom
Microsoft Access MVP

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?

:

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


:

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?

:

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


:

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



:

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


:

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.

:

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


:

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.

 

[Duane Hookom]

It only makes sense in a totals query that you can't group on the "*". If you
don't see a * in your grid, then check your query properties and set Output
All Fields to No.

--
Duane Hookom
Microsoft Access MVP

I spent soooooooooo much time on this DB, so this concludes that I must not
be destined to be a developer...

Now when I put that in there, I get "Cannot group on Fields selected with a
'*'

You took off the wrong ). Try
CurrQuarterInt: Sum(Abs([Quarter] = Format([ChangeDate],"qyyyy") ) *
[Interest])


--
Duane Hookom
Microsoft Access MVP

I used that... Access said to take off the last parentheses and when I ran
the query I got:
"
You tried to execute a query that does not include the specified expression
"IssueID" as part of an aggregate function."

:

If you want the user to enter a qyyyy then try:
CurrQuarterInt: Sum(Abs([Quarter]) = Format([ChangeDate],"qyyyy") ) *
[Interest])

This will create a column of values where the changedate equals the
[Quarter] value entered by the user.

--
Duane Hookom
Microsoft Access MVP


:

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?

:

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


:

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?

:

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


:

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



:

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


:

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.

:

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


:

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.