Report Limitation

  • Thread starter Thread starter znibk
  • Start date Start date
Z

znibk

Below is the information for the table I have and following that is the SQL
view of the query for the ending balance information. I am completely lost as
to how to limit the number of months to show on my monthly comparison
(limitation). I want only the last three months to print in the report but
currently, the query shows all ending monthly balances from the start of the
database to the end. I will show that SQL statement after the one for the
ending monthly balance.

I am not sure how to SQL view for a table, here is the information

I have:
Field Name: ABANK
Data Type: AutoNumber
Field Name: Bank
Data Type: Number (I have a join query connected to this table)
Field Name: MonthYear
Data Type: Number (Join query connected with this table)
Field Name: BegBalDate
Data Type: Date/Time
Field Name: BegBal
Data Type: Currency

As stated in the December 4, 2008 post, I have approximately 15 beginning
balances with their dates listed in my table. I want to be able to pick just
ONE date with the beginning balance which corresponds to that date.

My query is this:
SELECT tblBANK.Bank, tbl_ARVEST.ABegBalDate, tbl_ARVEST.ABegBal
FROM tblMONTHYEAR INNER JOIN (tblBANK INNER JOIN tbl_ARVEST ON tblBANK.BANKS
= tbl_ARVEST.Bank) ON tblMONTHYEAR.MONTH_YEAR = tbl_ARVEST.MonthYear
GROUP BY tblBANK.Bank, tbl_ARVEST.ABegBalDate, tbl_ARVEST.ABegBal
HAVING (((tblBANK.Bank)="ARVEST Bank") AND ((tbl_ARVEST.ABegBalDate) Between
[Forms]![FrmDates]![TxtStart] And [Forms]![FrmDates]![TxtEnd]));


The ultimate goal is to be able to create a report in which I have the
beginning balance of a certain year, then am able to add the income and
expenses by using
Between [Forms]![FrmDates]![TxtStart] And [Forms]![FrmDates]![TxtEnd])) to
work .

Currently, I am trying to add the begbal and the total income together to
get a total assets.
I have added the total expenses together.

However, I am having difficulty in obtaining the correct ending balance.

The SQL statement of the Monthly comparisons. Currently I have no RefDate
because if I put the monthly date in from the Between
[Forms]![frmDATES]![Start Text] And [Forms]![frmDATES]![EndText] it gives me
only the current month. Technically, I guess the Reference Date is probably
what is wrong with both queries, the one to obtain only the specific year's
beginning balance, and the Reference Dates to obtain only three months of
comparison for the income.


SELECT tbl_Income.RefDate, tblMONTHYEAR.MonthYear, tblBANK.Bank,
Sum([SS]+Nz([More],0)-Nz([Less],0)) AS ASalesDep, Sum(tbl_Income.ACDDep) AS
ACDDep, Nz([ASalesDep],0)+Nz([ACDDep],0) AS TxtASalesDepAndACDepSum,
Sum(tbl_Income.AOSDep) AS AOSDep, Sum(tbl_Income.AOIDep) AS AOIDep,
Nz([ASalesDep],0)+Nz([ACDDep],0)+Nz([AOSDep])+Nz([AOIDep],0) AS TxtIncomeSum
FROM tblMONTHYEAR INNER JOIN (tblINCOMETYPE INNER JOIN (tblBANK INNER JOIN
tbl_Income ON tblBANK.BANKS = tbl_Income.Bank) ON tblINCOMETYPE.INCOMETYPE =
tbl_Income.IncType) ON tblMONTHYEAR.MONTH_YEAR = tbl_Income.RefDate
GROUP BY tbl_Income.RefDate, tblMONTHYEAR.MonthYear, tblBANK.Bank
HAVING (((tblBANK.Bank)="ARVEST Bank"));


Thank you so much for you time,

Karen
 
Use your desired date information as Criteria for the appropriate date field
in the Query that you use as Record Source for the Report.

I often provide a form into which the user can enter, or select, date
ranges, and refer to the information in that form. To do so, you can either
specify something like Forms!frmDateSelectionForm!BeginningDate in the
criteria, or you can construct the Record Source with VBA code in the
Report's Open event, referring to the Form to obtain the values desired.

Larry Linson
Microsoft Office Access MVP

znibk said:
Below is the information for the table I have and following that is the
SQL
view of the query for the ending balance information. I am completely lost
as
to how to limit the number of months to show on my monthly comparison
(limitation). I want only the last three months to print in the report but
currently, the query shows all ending monthly balances from the start of
the
database to the end. I will show that SQL statement after the one for the
ending monthly balance.

I am not sure how to SQL view for a table, here is the information

I have:
Field Name: ABANK
Data Type: AutoNumber
Field Name: Bank
Data Type: Number (I have a join query connected to this table)
Field Name: MonthYear
Data Type: Number (Join query connected with this table)
Field Name: BegBalDate
Data Type: Date/Time
Field Name: BegBal
Data Type: Currency

As stated in the December 4, 2008 post, I have approximately 15 beginning
balances with their dates listed in my table. I want to be able to pick
just
ONE date with the beginning balance which corresponds to that date.

My query is this:
SELECT tblBANK.Bank, tbl_ARVEST.ABegBalDate, tbl_ARVEST.ABegBal
FROM tblMONTHYEAR INNER JOIN (tblBANK INNER JOIN tbl_ARVEST ON
tblBANK.BANKS
= tbl_ARVEST.Bank) ON tblMONTHYEAR.MONTH_YEAR = tbl_ARVEST.MonthYear
GROUP BY tblBANK.Bank, tbl_ARVEST.ABegBalDate, tbl_ARVEST.ABegBal
HAVING (((tblBANK.Bank)="ARVEST Bank") AND ((tbl_ARVEST.ABegBalDate)
Between
[Forms]![FrmDates]![TxtStart] And [Forms]![FrmDates]![TxtEnd]));


The ultimate goal is to be able to create a report in which I have the
beginning balance of a certain year, then am able to add the income and
expenses by using
Between [Forms]![FrmDates]![TxtStart] And [Forms]![FrmDates]![TxtEnd])) to
work .

Currently, I am trying to add the begbal and the total income together to
get a total assets.
I have added the total expenses together.

However, I am having difficulty in obtaining the correct ending balance.

The SQL statement of the Monthly comparisons. Currently I have no RefDate
because if I put the monthly date in from the Between
[Forms]![frmDATES]![Start Text] And [Forms]![frmDATES]![EndText] it gives
me
only the current month. Technically, I guess the Reference Date is
probably
what is wrong with both queries, the one to obtain only the specific
year's
beginning balance, and the Reference Dates to obtain only three months of
comparison for the income.


SELECT tbl_Income.RefDate, tblMONTHYEAR.MonthYear, tblBANK.Bank,
Sum([SS]+Nz([More],0)-Nz([Less],0)) AS ASalesDep, Sum(tbl_Income.ACDDep)
AS
ACDDep, Nz([ASalesDep],0)+Nz([ACDDep],0) AS TxtASalesDepAndACDepSum,
Sum(tbl_Income.AOSDep) AS AOSDep, Sum(tbl_Income.AOIDep) AS AOIDep,
Nz([ASalesDep],0)+Nz([ACDDep],0)+Nz([AOSDep])+Nz([AOIDep],0) AS
TxtIncomeSum
FROM tblMONTHYEAR INNER JOIN (tblINCOMETYPE INNER JOIN (tblBANK INNER JOIN
tbl_Income ON tblBANK.BANKS = tbl_Income.Bank) ON tblINCOMETYPE.INCOMETYPE
=
tbl_Income.IncType) ON tblMONTHYEAR.MONTH_YEAR = tbl_Income.RefDate
GROUP BY tbl_Income.RefDate, tblMONTHYEAR.MonthYear, tblBANK.Bank
HAVING (((tblBANK.Bank)="ARVEST Bank"));


Thank you so much for you time,

Karen
Click to expand...
 
Larry thanks for answering my post.

I have done what you suggested but it seems not to pull all the records.

The problem is probably mine because I use a reference date as the name of
the month so that I can get a total for the month. I tried using the
IncomingDate, but I received each one of the entries, not a total for the
month. So, how can I overcome that?

Also, when I get ready to run my reports, I have a Date Form in which I
enter beginning and ending dates--that corresponds with five of the six
reports I run. So, how do I correlate the additional SummaryDate to the
seventh report?

Your help will again be most appreciated.

God Bless,

Karen

Larry Linson said:
Use your desired date information as Criteria for the appropriate date field
in the Query that you use as Record Source for the Report.

I often provide a form into which the user can enter, or select, date
ranges, and refer to the information in that form. To do so, you can either
specify something like Forms!frmDateSelectionForm!BeginningDate in the
criteria, or you can construct the Record Source with VBA code in the
Report's Open event, referring to the Form to obtain the values desired.

Larry Linson
Microsoft Office Access MVP

znibk said:
Below is the information for the table I have and following that is the
SQL
view of the query for the ending balance information. I am completely lost
as
to how to limit the number of months to show on my monthly comparison
(limitation). I want only the last three months to print in the report but
currently, the query shows all ending monthly balances from the start of
the
database to the end. I will show that SQL statement after the one for the
ending monthly balance.

I am not sure how to SQL view for a table, here is the information

I have:
Field Name: ABANK
Data Type: AutoNumber
Field Name: Bank
Data Type: Number (I have a join query connected to this table)
Field Name: MonthYear
Data Type: Number (Join query connected with this table)
Field Name: BegBalDate
Data Type: Date/Time
Field Name: BegBal
Data Type: Currency

As stated in the December 4, 2008 post, I have approximately 15 beginning
balances with their dates listed in my table. I want to be able to pick
just
ONE date with the beginning balance which corresponds to that date.

My query is this:
SELECT tblBANK.Bank, tbl_ARVEST.ABegBalDate, tbl_ARVEST.ABegBal
FROM tblMONTHYEAR INNER JOIN (tblBANK INNER JOIN tbl_ARVEST ON
tblBANK.BANKS
= tbl_ARVEST.Bank) ON tblMONTHYEAR.MONTH_YEAR = tbl_ARVEST.MonthYear
GROUP BY tblBANK.Bank, tbl_ARVEST.ABegBalDate, tbl_ARVEST.ABegBal
HAVING (((tblBANK.Bank)="ARVEST Bank") AND ((tbl_ARVEST.ABegBalDate)
Between
[Forms]![FrmDates]![TxtStart] And [Forms]![FrmDates]![TxtEnd]));


The ultimate goal is to be able to create a report in which I have the
beginning balance of a certain year, then am able to add the income and
expenses by using
Between [Forms]![FrmDates]![TxtStart] And [Forms]![FrmDates]![TxtEnd])) to
work .

Currently, I am trying to add the begbal and the total income together to
get a total assets.
I have added the total expenses together.

However, I am having difficulty in obtaining the correct ending balance.

The SQL statement of the Monthly comparisons. Currently I have no RefDate
because if I put the monthly date in from the Between
[Forms]![frmDATES]![Start Text] And [Forms]![frmDATES]![EndText] it gives
me
only the current month. Technically, I guess the Reference Date is
probably
what is wrong with both queries, the one to obtain only the specific
year's
beginning balance, and the Reference Dates to obtain only three months of
comparison for the income.


SELECT tbl_Income.RefDate, tblMONTHYEAR.MonthYear, tblBANK.Bank,
Sum([SS]+Nz([More],0)-Nz([Less],0)) AS ASalesDep, Sum(tbl_Income.ACDDep)
AS
ACDDep, Nz([ASalesDep],0)+Nz([ACDDep],0) AS TxtASalesDepAndACDepSum,
Sum(tbl_Income.AOSDep) AS AOSDep, Sum(tbl_Income.AOIDep) AS AOIDep,
Nz([ASalesDep],0)+Nz([ACDDep],0)+Nz([AOSDep])+Nz([AOIDep],0) AS
TxtIncomeSum
FROM tblMONTHYEAR INNER JOIN (tblINCOMETYPE INNER JOIN (tblBANK INNER JOIN
tbl_Income ON tblBANK.BANKS = tbl_Income.Bank) ON tblINCOMETYPE.INCOMETYPE
=
tbl_Income.IncType) ON tblMONTHYEAR.MONTH_YEAR = tbl_Income.RefDate
GROUP BY tbl_Income.RefDate, tblMONTHYEAR.MonthYear, tblBANK.Bank
HAVING (((tblBANK.Bank)="ARVEST Bank"));


Thank you so much for you time,

Karen
Click to expand...
Click to expand...
 
Back
Top