Access Opening Balance Expression

Ha! Yeah. I just threw Query2 there because I couldn't remember exactly what
I called it. :-) Thanks a million! You are awesome.

I wish I knew 1/8th of what you guys know when it comes to this stuff. I've
learned to appreciate Access and its capabilities, but, have struggled with
getting comfortable at learning some of the more challening bits. Excel has
always been my software of choice, which is how I used to track the database
I am now creating.

Have a great weekend!

"Duane Hookom" wrote:

"Query2"? Please kick it up a notch and save your query with a name like
"qtotChildBeginBalance".

Then try:
=DLookup("BegBal","qtotChildBeginBalance","ChildID =" & [ChildID])
This assumes ChildID is numeric.

--
Duane Hookom
Microsoft Access MVP


"Difficult1" wrote:

Hi, Duane. I managed to get this query to return good information, however, I
cannot seem to get the DLookup to bring in the BegBal. What I have is a
control with the Control Source set to =DLookup([Query2],[BegBal]). Is that
correct?

"Duane Hookom" wrote:

First, IMO, I don't think parameter prompts in queries are ever appropriate.
Use controls on forms for all user interaction. Assuming you follow this
suggestion, you can create a query to get all starting balances with a totals
query like:

SELECT ChildID, Sum(StmtAmt) as BegBal
FROM Transactions
WHERE [Date] Forms!frmDateSelect!txtStartDate
GROUP BY ChildID;

Then you can use a subreport or DLookup() to grab the BegBal from the query.

Other options include creating a union query with this new query and your
report's record source or using a combo box or using code.

--
Duane Hookom
Microsoft Access MVP


"Difficult1" wrote:

Good morning, Duane. Yes, the sum of all transactions prior the start date is
exactly what it should be. My report is grouped on ChildID (Primary Key), so
it should be grouped by that.

"Duane Hookom" wrote:

How is a beginning balance calculated? Is it the sum of all transactions
prior to the start date?

Do you need this by customer or client?

--
Duane Hookom
Microsoft Access MVP


"Difficult1" wrote:

Good afternoon. I am in need of a formula that will calculate a beggining
balance, based on a paramenter that is already set up for that report.

What I have is a parameter that asks for the first statement date (7/1/09)
and ending statement date (07/31/09). This paramenter is in the query
criteria box
for field
"[Transactions].[Date]". This makes my report show only detail between those
dates, which is working perfectly. I have a field in my report called
"[StmtAmt]"
This "[StmtAmt]" field is the one that I want to total before the first
statement date.

I cannot seem to figure this one out. I know there must be a logical way to
do it, but, I am just not seeing it. I would like to think it would be as
simple as
"[StartDate]-1" in the criteria box of the query, then again, I may have
made myself more
of a mess than necessary.

Any help would be greatly appreciated!

Thanks for taking the time to help me out with this. Unfortunately, I still
can't get it to work, for whatever reason. When I put the =Dlookup into my
report, the only thing that shows is #Error#. Not sure what that's all about
or how to fix it.

I guess at this point I'll go back to my Excel s/s and Crystal report combo.


"Duane Hookom" wrote:

"Query2"? Please kick it up a notch and save your query with a name like
"qtotChildBeginBalance".

Then try:
=DLookup("BegBal","qtotChildBeginBalance","ChildID =" & [ChildID])
This assumes ChildID is numeric.

--
Duane Hookom
Microsoft Access MVP


"Difficult1" wrote:

Hi, Duane. I managed to get this query to return good information, however, I
cannot seem to get the DLookup to bring in the BegBal. What I have is a
control with the Control Source set to =DLookup([Query2],[BegBal]). Is that
correct?

"Duane Hookom" wrote:

First, IMO, I don't think parameter prompts in queries are ever appropriate.
Use controls on forms for all user interaction. Assuming you follow this
suggestion, you can create a query to get all starting balances with a totals
query like:

SELECT ChildID, Sum(StmtAmt) as BegBal
FROM Transactions
WHERE [Date] Forms!frmDateSelect!txtStartDate
GROUP BY ChildID;

Then you can use a subreport or DLookup() to grab the BegBal from the query.

Other options include creating a union query with this new query and your
report's record source or using a combo box or using code.

--
Duane Hookom
Microsoft Access MVP


"Difficult1" wrote:

Good morning, Duane. Yes, the sum of all transactions prior the start date is
exactly what it should be. My report is grouped on ChildID (Primary Key), so
it should be grouped by that.

"Duane Hookom" wrote:

How is a beginning balance calculated? Is it the sum of all transactions
prior to the start date?

Do you need this by customer or client?

--
Duane Hookom
Microsoft Access MVP


"Difficult1" wrote:

Good afternoon. I am in need of a formula that will calculate a beggining
balance, based on a paramenter that is already set up for that report.

What I have is a parameter that asks for the first statement date (7/1/09)
and ending statement date (07/31/09). This paramenter is in the query
criteria box
for field
"[Transactions].[Date]". This makes my report show only detail between those
dates, which is working perfectly. I have a field in my report called
"[StmtAmt]"
This "[StmtAmt]" field is the one that I want to total before the first
statement date.

I cannot seem to figure this one out. I know there must be a logical way to
do it, but, I am just not seeing it. I would like to think it would be as
simple as
"[StartDate]-1" in the criteria box of the query, then again, I may have
made myself more
of a mess than necessary.

Any help would be greatly appreciated!

Did you try the union query solution I suggested a while back?

--
Duane Hookom
Microsoft Access MVP


"Difficult1" wrote:

Thanks for taking the time to help me out with this. Unfortunately, I still
can't get it to work, for whatever reason. When I put the =Dlookup into my
report, the only thing that shows is #Error#. Not sure what that's all about
or how to fix it.

I guess at this point I'll go back to my Excel s/s and Crystal report combo.


"Duane Hookom" wrote:

"Query2"? Please kick it up a notch and save your query with a name like
"qtotChildBeginBalance".

Then try:
=DLookup("BegBal","qtotChildBeginBalance","ChildID =" & [ChildID])
This assumes ChildID is numeric.

--
Duane Hookom
Microsoft Access MVP


"Difficult1" wrote:

Hi, Duane. I managed to get this query to return good information, however, I
cannot seem to get the DLookup to bring in the BegBal. What I have is a
control with the Control Source set to =DLookup([Query2],[BegBal]). Is that
correct?

"Duane Hookom" wrote:

First, IMO, I don't think parameter prompts in queries are ever appropriate.
Use controls on forms for all user interaction. Assuming you follow this
suggestion, you can create a query to get all starting balances with a totals
query like:

SELECT ChildID, Sum(StmtAmt) as BegBal
FROM Transactions
WHERE [Date] Forms!frmDateSelect!txtStartDate
GROUP BY ChildID;

Then you can use a subreport or DLookup() to grab the BegBal from the query.

Other options include creating a union query with this new query and your
report's record source or using a combo box or using code.

--
Duane Hookom
Microsoft Access MVP


"Difficult1" wrote:

Good morning, Duane. Yes, the sum of all transactions prior the start date is
exactly what it should be. My report is grouped on ChildID (Primary Key), so
it should be grouped by that.

"Duane Hookom" wrote:

How is a beginning balance calculated? Is it the sum of all transactions
prior to the start date?

Do you need this by customer or client?

--
Duane Hookom
Microsoft Access MVP


"Difficult1" wrote:

Good afternoon. I am in need of a formula that will calculate a beggining
balance, based on a paramenter that is already set up for that report.

What I have is a parameter that asks for the first statement date (7/1/09)
and ending statement date (07/31/09). This paramenter is in the query
criteria box
for field
"[Transactions].[Date]". This makes my report show only detail between those
dates, which is working perfectly. I have a field in my report called
"[StmtAmt]"
This "[StmtAmt]" field is the one that I want to total before the first
statement date.

I cannot seem to figure this one out. I know there must be a logical way to
do it, but, I am just not seeing it. I would like to think it would be as
simple as
"[StartDate]-1" in the criteria box of the query, then again, I may have
made myself more
of a mess than necessary.

Any help would be greatly appreciated!

Hi, Duane. I did. I didn't get anywhere that way either. Maybe what I am
trying to do is way to advanced for me. I am probably forgetting something
absolutely stupid. I do appreciate the help though!

"Duane Hookom" wrote:

Did you try the union query solution I suggested a while back?

--
Duane Hookom
Microsoft Access MVP


"Difficult1" wrote:

Thanks for taking the time to help me out with this. Unfortunately, I still
can't get it to work, for whatever reason. When I put the =Dlookup into my
report, the only thing that shows is #Error#. Not sure what that's all about
or how to fix it.

I guess at this point I'll go back to my Excel s/s and Crystal report combo.


"Duane Hookom" wrote:

"Query2"? Please kick it up a notch and save your query with a name like
"qtotChildBeginBalance".

Then try:
=DLookup("BegBal","qtotChildBeginBalance","ChildID =" & [ChildID])
This assumes ChildID is numeric.

--
Duane Hookom
Microsoft Access MVP


"Difficult1" wrote:

Hi, Duane. I managed to get this query to return good information, however, I
cannot seem to get the DLookup to bring in the BegBal. What I have is a
control with the Control Source set to =DLookup([Query2],[BegBal]). Is that
correct?

"Duane Hookom" wrote:

First, IMO, I don't think parameter prompts in queries are ever appropriate.
Use controls on forms for all user interaction. Assuming you follow this
suggestion, you can create a query to get all starting balances with a totals
query like:

SELECT ChildID, Sum(StmtAmt) as BegBal
FROM Transactions
WHERE [Date] Forms!frmDateSelect!txtStartDate
GROUP BY ChildID;

Then you can use a subreport or DLookup() to grab the BegBal from the query.

Other options include creating a union query with this new query and your
report's record source or using a combo box or using code.

--
Duane Hookom
Microsoft Access MVP


"Difficult1" wrote:

Good morning, Duane. Yes, the sum of all transactions prior the start date is
exactly what it should be. My report is grouped on ChildID (Primary Key), so
it should be grouped by that.

"Duane Hookom" wrote:

How is a beginning balance calculated? Is it the sum of all transactions
prior to the start date?

Do you need this by customer or client?

--
Duane Hookom
Microsoft Access MVP


"Difficult1" wrote:

Good afternoon. I am in need of a formula that will calculate a beggining
balance, based on a paramenter that is already set up for that report.

What I have is a parameter that asks for the first statement date (7/1/09)
and ending statement date (07/31/09). This paramenter is in the query
criteria box
for field
"[Transactions].[Date]". This makes my report show only detail between those
dates, which is working perfectly. I have a field in my report called
"[StmtAmt]"
This "[StmtAmt]" field is the one that I want to total before the first
statement date.

I cannot seem to figure this one out. I know there must be a logical way to
do it, but, I am just not seeing it. I would like to think it would be as
simple as
"[StartDate]-1" in the criteria box of the query, then again, I may have
made myself more
of a mess than necessary.

Any help would be greatly appreciated!

You might want to share your union query SQL view with us and describe the
results.
--
Duane Hookom
Microsoft Access MVP


"Difficult1" wrote:

Hi, Duane. I did. I didn't get anywhere that way either. Maybe what I am
trying to do is way to advanced for me. I am probably forgetting something
absolutely stupid. I do appreciate the help though!

"Duane Hookom" wrote:

Did you try the union query solution I suggested a while back?

--
Duane Hookom
Microsoft Access MVP


"Difficult1" wrote:

Thanks for taking the time to help me out with this. Unfortunately, I still
can't get it to work, for whatever reason. When I put the =Dlookup into my
report, the only thing that shows is #Error#. Not sure what that's all about
or how to fix it.

I guess at this point I'll go back to my Excel s/s and Crystal report combo.


"Duane Hookom" wrote:

"Query2"? Please kick it up a notch and save your query with a name like
"qtotChildBeginBalance".

Then try:
=DLookup("BegBal","qtotChildBeginBalance","ChildID =" & [ChildID])
This assumes ChildID is numeric.

--
Duane Hookom
Microsoft Access MVP


"Difficult1" wrote:

Hi, Duane. I managed to get this query to return good information, however, I
cannot seem to get the DLookup to bring in the BegBal. What I have is a
control with the Control Source set to =DLookup([Query2],[BegBal]). Is that
correct?

"Duane Hookom" wrote:

First, IMO, I don't think parameter prompts in queries are ever appropriate.
Use controls on forms for all user interaction. Assuming you follow this
suggestion, you can create a query to get all starting balances with a totals
query like:

SELECT ChildID, Sum(StmtAmt) as BegBal
FROM Transactions
WHERE [Date] Forms!frmDateSelect!txtStartDate
GROUP BY ChildID;

Then you can use a subreport or DLookup() to grab the BegBal from the query.

Other options include creating a union query with this new query and your
report's record source or using a combo box or using code.

--
Duane Hookom
Microsoft Access MVP


"Difficult1" wrote:

Good morning, Duane. Yes, the sum of all transactions prior the start date is
exactly what it should be. My report is grouped on ChildID (Primary Key), so
it should be grouped by that.

"Duane Hookom" wrote:

How is a beginning balance calculated? Is it the sum of all transactions
prior to the start date?

Do you need this by customer or client?

--
Duane Hookom
Microsoft Access MVP


"Difficult1" wrote:

Good afternoon. I am in need of a formula that will calculate a beggining
balance, based on a paramenter that is already set up for that report.

What I have is a parameter that asks for the first statement date (7/1/09)
and ending statement date (07/31/09). This paramenter is in the query
criteria box
for field
"[Transactions].[Date]". This makes my report show only detail between those
dates, which is working perfectly. I have a field in my report called
"[StmtAmt]"
This "[StmtAmt]" field is the one that I want to total before the first
statement date.

I cannot seem to figure this one out. I know there must be a logical way to
do it, but, I am just not seeing it. I would like to think it would be as
simple as
"[StartDate]-1" in the criteria box of the query, then again, I may have
made myself more
of a mess than necessary.

Any help would be greatly appreciated!

SELECT ChildID,
FROM Transactions
WHERE Date Between [Forms]![frmDateRange]![FromDate] And
[Forms]![frmDateRange]![ToDate]
UNION SELECT 0, CustomerID,Null, Null, Null, Sum(BegBal)
FROM Transactions
WHERE Date [Forms]![frmDateRange]![FromDate]
GROUP BY ChildID
ORDER BY 2, 1;


I get a message that says the number of columns do not match. I have never
done one of these before, so I feel kinda stupid.


"Duane Hookom" wrote:

You might want to share your union query SQL view with us and describe the
results.
--
Duane Hookom
Microsoft Access MVP


"Difficult1" wrote:

Hi, Duane. I did. I didn't get anywhere that way either. Maybe what I am
trying to do is way to advanced for me. I am probably forgetting something
absolutely stupid. I do appreciate the help though!

"Duane Hookom" wrote:

Did you try the union query solution I suggested a while back?

--
Duane Hookom
Microsoft Access MVP


"Difficult1" wrote:

Thanks for taking the time to help me out with this. Unfortunately, I still
can't get it to work, for whatever reason. When I put the =Dlookup into my
report, the only thing that shows is #Error#. Not sure what that's all about
or how to fix it.

I guess at this point I'll go back to my Excel s/s and Crystal report combo.


"Duane Hookom" wrote:

"Query2"? Please kick it up a notch and save your query with a name like
"qtotChildBeginBalance".

Then try:
=DLookup("BegBal","qtotChildBeginBalance","ChildID =" & [ChildID])
This assumes ChildID is numeric.

--
Duane Hookom
Microsoft Access MVP


"Difficult1" wrote:

Hi, Duane. I managed to get this query to return good information, however, I
cannot seem to get the DLookup to bring in the BegBal. What I have is a
control with the Control Source set to =DLookup([Query2],[BegBal]). Is that
correct?

"Duane Hookom" wrote:

First, IMO, I don't think parameter prompts in queries are ever appropriate.
Use controls on forms for all user interaction. Assuming you follow this
suggestion, you can create a query to get all starting balances with a totals
query like:

SELECT ChildID, Sum(StmtAmt) as BegBal
FROM Transactions
WHERE [Date] Forms!frmDateSelect!txtStartDate
GROUP BY ChildID;

Then you can use a subreport or DLookup() to grab the BegBal from the query.

Other options include creating a union query with this new query and your
report's record source or using a combo box or using code.

--
Duane Hookom
Microsoft Access MVP


"Difficult1" wrote:

Good morning, Duane. Yes, the sum of all transactions prior the start date is
exactly what it should be. My report is grouped on ChildID (Primary Key), so
it should be grouped by that.

"Duane Hookom" wrote:

How is a beginning balance calculated? Is it the sum of all transactions
prior to the start date?

Do you need this by customer or client?

--
Duane Hookom
Microsoft Access MVP


"Difficult1" wrote:

Good afternoon. I am in need of a formula that will calculate a beggining
balance, based on a paramenter that is already set up for that report.

What I have is a parameter that asks for the first statement date (7/1/09)
and ending statement date (07/31/09). This paramenter is in the query
criteria box
for field
"[Transactions].[Date]". This makes my report show only detail between those
dates, which is working perfectly. I have a field in my report called
"[StmtAmt]"
This "[StmtAmt]" field is the one that I want to total before the first
statement date.

I cannot seem to figure this one out. I know there must be a logical way to
do it, but, I am just not seeing it. I would like to think it would be as
simple as
"[StartDate]-1" in the criteria box of the query, then again, I may have
made myself more
of a mess than necessary.

Any help would be greatly appreciated!

As per the error message, the number of columns/fields in each of the SELECT
clauses in a union query must be equal. Your first SELECT has only one column
with a orphaned comma while your second SELECT has 6 columns.

Your first SELECT should have all of the fields needed in your report. The
second select should be the GROUP BY query which will probably have Null for
most of the columns. Do you actually have a BegBal field in the Transactions
table? I would expect the second SELECT would SUM a transaction amount.

--
Duane Hookom
Microsoft Access MVP


"Difficult1" wrote:

SELECT ChildID,
FROM Transactions
WHERE Date Between [Forms]![frmDateRange]![FromDate] And
[Forms]![frmDateRange]![ToDate]
UNION SELECT 0, CustomerID,Null, Null, Null, Sum(BegBal)
FROM Transactions
WHERE Date [Forms]![frmDateRange]![FromDate]
GROUP BY ChildID
ORDER BY 2, 1;


I get a message that says the number of columns do not match. I have never
done one of these before, so I feel kinda stupid.


"Duane Hookom" wrote:

You might want to share your union query SQL view with us and describe the
results.
--
Duane Hookom
Microsoft Access MVP


"Difficult1" wrote:

Hi, Duane. I did. I didn't get anywhere that way either. Maybe what I am
trying to do is way to advanced for me. I am probably forgetting something
absolutely stupid. I do appreciate the help though!

"Duane Hookom" wrote:

Did you try the union query solution I suggested a while back?

--
Duane Hookom
Microsoft Access MVP


"Difficult1" wrote:

Thanks for taking the time to help me out with this. Unfortunately, I still
can't get it to work, for whatever reason. When I put the =Dlookup into my
report, the only thing that shows is #Error#. Not sure what that's all about
or how to fix it.

I guess at this point I'll go back to my Excel s/s and Crystal report combo.


"Duane Hookom" wrote:

"Query2"? Please kick it up a notch and save your query with a name like
"qtotChildBeginBalance".

Then try:
=DLookup("BegBal","qtotChildBeginBalance","ChildID =" & [ChildID])
This assumes ChildID is numeric.

--
Duane Hookom
Microsoft Access MVP


"Difficult1" wrote:

Hi, Duane. I managed to get this query to return good information, however, I
cannot seem to get the DLookup to bring in the BegBal. What I have is a
control with the Control Source set to =DLookup([Query2],[BegBal]). Is that
correct?

"Duane Hookom" wrote:

First, IMO, I don't think parameter prompts in queries are ever appropriate.
Use controls on forms for all user interaction. Assuming you follow this
suggestion, you can create a query to get all starting balances with a totals
query like:

SELECT ChildID, Sum(StmtAmt) as BegBal
FROM Transactions
WHERE [Date] Forms!frmDateSelect!txtStartDate
GROUP BY ChildID;

Then you can use a subreport or DLookup() to grab the BegBal from the query.

Other options include creating a union query with this new query and your
report's record source or using a combo box or using code.

--
Duane Hookom
Microsoft Access MVP


"Difficult1" wrote:

Good morning, Duane. Yes, the sum of all transactions prior the start date is
exactly what it should be. My report is grouped on ChildID (Primary Key), so
it should be grouped by that.

"Duane Hookom" wrote:

How is a beginning balance calculated? Is it the sum of all transactions
prior to the start date?

Do you need this by customer or client?

--
Duane Hookom
Microsoft Access MVP


"Difficult1" wrote:

Good afternoon. I am in need of a formula that will calculate a beggining
balance, based on a paramenter that is already set up for that report.

What I have is a parameter that asks for the first statement date (7/1/09)
and ending statement date (07/31/09). This paramenter is in the query
criteria box
for field
"[Transactions].[Date]". This makes my report show only detail between those
dates, which is working perfectly. I have a field in my report called
"[StmtAmt]"
This "[StmtAmt]" field is the one that I want to total before the first
statement date.

I cannot seem to figure this one out. I know there must be a logical way to
do it, but, I am just not seeing it. I would like to think it would be as
simple as
"[StartDate]-1" in the criteria box of the query, then again, I may have
made myself more
of a mess than necessary.

Any help would be greatly appreciated!

Okay, so, with some modifications, I am getting all of the information that I
need into my report. The only issue I am running into now is the dollar
amount in the BegBal calculation. I want it is sum everything before the
FromDate. It looks like it is using the same number for each individual
($84), and not adding them up per ChildID before FromDate.

SELECT ChildID, ChildFirst, ChildLast, ParentFirst, ParentLast, BegBal,
Amount, PaymentAmt, StmtAmt, Description, Method, Date, Address1, City,
State, Zip, Hours, TimeIn, TimeOut, CheckNumber
FROM qTransbydate
WHERE Date Between [Forms]![frmDateRange]![FromDate] And
[Forms]![frmDateRange]![ToDate]
UNION SELECT ChildID,Null, Null, Null,Null, Sum([BegBal]), Null, Null, Null,
Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, Null
FROM qTransbyDate
WHERE Date [Forms]![frmDateRange]![FromDate]
GROUP BY ChildID
ORDER BY 2, 1;


"Duane Hookom" wrote:

As per the error message, the number of columns/fields in each of the SELECT
clauses in a union query must be equal. Your first SELECT has only one column
with a orphaned comma while your second SELECT has 6 columns.

Your first SELECT should have all of the fields needed in your report. The
second select should be the GROUP BY query which will probably have Null for
most of the columns. Do you actually have a BegBal field in the Transactions
table? I would expect the second SELECT would SUM a transaction amount.

--
Duane Hookom
Microsoft Access MVP


"Difficult1" wrote:

SELECT ChildID,
FROM Transactions
WHERE Date Between [Forms]![frmDateRange]![FromDate] And
[Forms]![frmDateRange]![ToDate]
UNION SELECT 0, CustomerID,Null, Null, Null, Sum(BegBal)
FROM Transactions
WHERE Date [Forms]![frmDateRange]![FromDate]
GROUP BY ChildID
ORDER BY 2, 1;


I get a message that says the number of columns do not match. I have never
done one of these before, so I feel kinda stupid.


"Duane Hookom" wrote:

You might want to share your union query SQL view with us and describe the
results.
--
Duane Hookom
Microsoft Access MVP


"Difficult1" wrote:

Hi, Duane. I did. I didn't get anywhere that way either. Maybe what I am
trying to do is way to advanced for me. I am probably forgetting something
absolutely stupid. I do appreciate the help though!

"Duane Hookom" wrote:

Did you try the union query solution I suggested a while back?

--
Duane Hookom
Microsoft Access MVP


"Difficult1" wrote:

Thanks for taking the time to help me out with this. Unfortunately, I still
can't get it to work, for whatever reason. When I put the =Dlookup into my
report, the only thing that shows is #Error#. Not sure what that's all about
or how to fix it.

I guess at this point I'll go back to my Excel s/s and Crystal report combo.


"Duane Hookom" wrote:

"Query2"? Please kick it up a notch and save your query with a name like
"qtotChildBeginBalance".

Then try:
=DLookup("BegBal","qtotChildBeginBalance","ChildID =" & [ChildID])
This assumes ChildID is numeric.

--
Duane Hookom
Microsoft Access MVP


"Difficult1" wrote:

Hi, Duane. I managed to get this query to return good information, however, I
cannot seem to get the DLookup to bring in the BegBal. What I have is a
control with the Control Source set to =DLookup([Query2],[BegBal]). Is that
correct?

"Duane Hookom" wrote:

First, IMO, I don't think parameter prompts in queries are ever appropriate.
Use controls on forms for all user interaction. Assuming you follow this
suggestion, you can create a query to get all starting balances with a totals
query like:

SELECT ChildID, Sum(StmtAmt) as BegBal
FROM Transactions
WHERE [Date] Forms!frmDateSelect!txtStartDate
GROUP BY ChildID;

Then you can use a subreport or DLookup() to grab the BegBal from the query.

Other options include creating a union query with this new query and your
report's record source or using a combo box or using code.

--
Duane Hookom
Microsoft Access MVP


"Difficult1" wrote:

Good morning, Duane. Yes, the sum of all transactions prior the start date is
exactly what it should be. My report is grouped on ChildID (Primary Key), so
it should be grouped by that.

"Duane Hookom" wrote:

How is a beginning balance calculated? Is it the sum of all transactions
prior to the start date?

Do you need this by customer or client?

--
Duane Hookom
Microsoft Access MVP


"Difficult1" wrote:

Good afternoon. I am in need of a formula that will calculate a beggining
balance, based on a paramenter that is already set up for that report.

What I have is a parameter that asks for the first statement date (7/1/09)
and ending statement date (07/31/09). This paramenter is in the query
criteria box
for field
"[Transactions].[Date]". This makes my report show only detail between those
dates, which is working perfectly. I have a field in my report called
"[StmtAmt]"
This "[StmtAmt]" field is the one that I want to total before the first
statement date.

I cannot seem to figure this one out. I know there must be a logical way to
do it, but, I am just not seeing it. I would like to think it would be as
simple as
"[StartDate]-1" in the criteria box of the query, then again, I may have
made myself more
of a mess than necessary.

Any help would be greatly appreciated!

Good work. What do you see if you create a query with only this part of the
UNION query:

SELECT ChildID,Null, Null, Null,Null, Sum([BegBal]), Null, Null, Null,
Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, Null
FROM qTransbyDate
WHERE [Date] [Forms]![frmDateRange]![FromDate]
GROUP BY ChildID
ORDER BY 2, 1;

--
Duane Hookom
Microsoft Access MVP


"Difficult1" wrote:

Okay, so, with some modifications, I am getting all of the information that I
need into my report. The only issue I am running into now is the dollar
amount in the BegBal calculation. I want it is sum everything before the
FromDate. It looks like it is using the same number for each individual
($84), and not adding them up per ChildID before FromDate.

SELECT ChildID, ChildFirst, ChildLast, ParentFirst, ParentLast, BegBal,
Amount, PaymentAmt, StmtAmt, Description, Method, Date, Address1, City,
State, Zip, Hours, TimeIn, TimeOut, CheckNumber
FROM qTransbydate
WHERE Date Between [Forms]![frmDateRange]![FromDate] And
[Forms]![frmDateRange]![ToDate]
UNION SELECT ChildID,Null, Null, Null,Null, Sum([BegBal]), Null, Null, Null,
Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, Null
FROM qTransbyDate
WHERE Date [Forms]![frmDateRange]![FromDate]
GROUP BY ChildID
ORDER BY 2, 1;


"Duane Hookom" wrote:

As per the error message, the number of columns/fields in each of the SELECT
clauses in a union query must be equal. Your first SELECT has only one column
with a orphaned comma while your second SELECT has 6 columns.

Your first SELECT should have all of the fields needed in your report. The
second select should be the GROUP BY query which will probably have Null for
most of the columns. Do you actually have a BegBal field in the Transactions
table? I would expect the second SELECT would SUM a transaction amount.

--
Duane Hookom
Microsoft Access MVP


"Difficult1" wrote:

SELECT ChildID,
FROM Transactions
WHERE Date Between [Forms]![frmDateRange]![FromDate] And
[Forms]![frmDateRange]![ToDate]
UNION SELECT 0, CustomerID,Null, Null, Null, Sum(BegBal)
FROM Transactions
WHERE Date [Forms]![frmDateRange]![FromDate]
GROUP BY ChildID
ORDER BY 2, 1;


I get a message that says the number of columns do not match. I have never
done one of these before, so I feel kinda stupid.


"Duane Hookom" wrote:

You might want to share your union query SQL view with us and describe the
results.
--
Duane Hookom
Microsoft Access MVP


"Difficult1" wrote:

Hi, Duane. I did. I didn't get anywhere that way either. Maybe what I am
trying to do is way to advanced for me. I am probably forgetting something
absolutely stupid. I do appreciate the help though!

"Duane Hookom" wrote:

Did you try the union query solution I suggested a while back?

--
Duane Hookom
Microsoft Access MVP


"Difficult1" wrote:

Thanks for taking the time to help me out with this. Unfortunately, I still
can't get it to work, for whatever reason. When I put the =Dlookup into my
report, the only thing that shows is #Error#. Not sure what that's all about
or how to fix it.

I guess at this point I'll go back to my Excel s/s and Crystal report combo.


"Duane Hookom" wrote:

"Query2"? Please kick it up a notch and save your query with a name like
"qtotChildBeginBalance".

Then try:
=DLookup("BegBal","qtotChildBeginBalance","ChildID =" & [ChildID])
This assumes ChildID is numeric.

--
Duane Hookom
Microsoft Access MVP


"Difficult1" wrote:

Hi, Duane. I managed to get this query to return good information, however, I
cannot seem to get the DLookup to bring in the BegBal. What I have is a
control with the Control Source set to =DLookup([Query2],[BegBal]). Is that
correct?

"Duane Hookom" wrote:

First, IMO, I don't think parameter prompts in queries are ever appropriate.
Use controls on forms for all user interaction. Assuming you follow this
suggestion, you can create a query to get all starting balances with a totals
query like:

SELECT ChildID, Sum(StmtAmt) as BegBal
FROM Transactions
WHERE [Date] Forms!frmDateSelect!txtStartDate
GROUP BY ChildID;

Then you can use a subreport or DLookup() to grab the BegBal from the query.

Other options include creating a union query with this new query and your
report's record source or using a combo box or using code.

--
Duane Hookom
Microsoft Access MVP


"Difficult1" wrote:

Good morning, Duane. Yes, the sum of all transactions prior the start date is
exactly what it should be. My report is grouped on ChildID (Primary Key), so
it should be grouped by that.

"Duane Hookom" wrote:

How is a beginning balance calculated? Is it the sum of all transactions
prior to the start date?

Do you need this by customer or client?

--
Duane Hookom
Microsoft Access MVP


"Difficult1" wrote:

Good afternoon. I am in need of a formula that will calculate a beggining
balance, based on a paramenter that is already set up for that report.

What I have is a parameter that asks for the first statement date (7/1/09)
and ending statement date (07/31/09). This paramenter is in the query
criteria box
for field
"[Transactions].[Date]". This makes my report show only detail between those
dates, which is working perfectly. I have a field in my report called
"[StmtAmt]"
This "[StmtAmt]" field is the one that I want to total before the first
statement date.

I cannot seem to figure this one out. I know there must be a logical way to
do it, but, I am just not seeing it. I would like to think it would be as
simple as
"[StartDate]-1" in the criteria box of the query, then again, I may have
made myself more
of a mess than necessary.

Any help would be greatly appreciated!

Still the same thing. Maybe I am putting the [BegBal] field in the wrong
place? I have a ChildID header with all the names and addresses, and that is
where I have it. Then in the ChildID detail section, I have all of my detail
for that month. It appears that the number coming into BegBal is the first
charge listed in the detail.

for example:

ChildID Header
BegBal = $84.00

ChildID Detail
7/1/09 Daycare $84.00


"Duane Hookom" wrote:

Good work. What do you see if you create a query with only this part of the
UNION query:

SELECT ChildID,Null, Null, Null,Null, Sum([BegBal]), Null, Null, Null,
Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, Null
FROM qTransbyDate
WHERE [Date] [Forms]![frmDateRange]![FromDate]
GROUP BY ChildID
ORDER BY 2, 1;

--
Duane Hookom
Microsoft Access MVP


"Difficult1" wrote:

Okay, so, with some modifications, I am getting all of the information that I
need into my report. The only issue I am running into now is the dollar
amount in the BegBal calculation. I want it is sum everything before the
FromDate. It looks like it is using the same number for each individual
($84), and not adding them up per ChildID before FromDate.

SELECT ChildID, ChildFirst, ChildLast, ParentFirst, ParentLast, BegBal,
Amount, PaymentAmt, StmtAmt, Description, Method, Date, Address1, City,
State, Zip, Hours, TimeIn, TimeOut, CheckNumber
FROM qTransbydate
WHERE Date Between [Forms]![frmDateRange]![FromDate] And
[Forms]![frmDateRange]![ToDate]
UNION SELECT ChildID,Null, Null, Null,Null, Sum([BegBal]), Null, Null, Null,
Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, Null
FROM qTransbyDate
WHERE Date [Forms]![frmDateRange]![FromDate]
GROUP BY ChildID
ORDER BY 2, 1;


"Duane Hookom" wrote:

As per the error message, the number of columns/fields in each of the SELECT
clauses in a union query must be equal. Your first SELECT has only one column
with a orphaned comma while your second SELECT has 6 columns.

Your first SELECT should have all of the fields needed in your report. The
second select should be the GROUP BY query which will probably have Null for
most of the columns. Do you actually have a BegBal field in the Transactions
table? I would expect the second SELECT would SUM a transaction amount.

--
Duane Hookom
Microsoft Access MVP


"Difficult1" wrote:

SELECT ChildID,
FROM Transactions
WHERE Date Between [Forms]![frmDateRange]![FromDate] And
[Forms]![frmDateRange]![ToDate]
UNION SELECT 0, CustomerID,Null, Null, Null, Sum(BegBal)
FROM Transactions
WHERE Date [Forms]![frmDateRange]![FromDate]
GROUP BY ChildID
ORDER BY 2, 1;


I get a message that says the number of columns do not match. I have never
done one of these before, so I feel kinda stupid.


"Duane Hookom" wrote:

You might want to share your union query SQL view with us and describe the
results.
--
Duane Hookom
Microsoft Access MVP


"Difficult1" wrote:

Hi, Duane. I did. I didn't get anywhere that way either. Maybe what I am
trying to do is way to advanced for me. I am probably forgetting something
absolutely stupid. I do appreciate the help though!

"Duane Hookom" wrote:

Did you try the union query solution I suggested a while back?

--
Duane Hookom
Microsoft Access MVP


"Difficult1" wrote:

Thanks for taking the time to help me out with this. Unfortunately, I still
can't get it to work, for whatever reason. When I put the =Dlookup into my
report, the only thing that shows is #Error#. Not sure what that's all about
or how to fix it.

I guess at this point I'll go back to my Excel s/s and Crystal report combo.


"Duane Hookom" wrote:

"Query2"? Please kick it up a notch and save your query with a name like
"qtotChildBeginBalance".

Then try:
=DLookup("BegBal","qtotChildBeginBalance","ChildID =" & [ChildID])
This assumes ChildID is numeric.

--
Duane Hookom
Microsoft Access MVP


"Difficult1" wrote:

Hi, Duane. I managed to get this query to return good information, however, I
cannot seem to get the DLookup to bring in the BegBal. What I have is a
control with the Control Source set to =DLookup([Query2],[BegBal]). Is that
correct?

"Duane Hookom" wrote:

First, IMO, I don't think parameter prompts in queries are ever appropriate.
Use controls on forms for all user interaction. Assuming you follow this
suggestion, you can create a query to get all starting balances with a totals
query like:

SELECT ChildID, Sum(StmtAmt) as BegBal
FROM Transactions
WHERE [Date] Forms!frmDateSelect!txtStartDate
GROUP BY ChildID;

Then you can use a subreport or DLookup() to grab the BegBal from the query.

Other options include creating a union query with this new query and your
report's record source or using a combo box or using code.

--
Duane Hookom
Microsoft Access MVP


"Difficult1" wrote:

Good morning, Duane. Yes, the sum of all transactions prior the start date is
exactly what it should be. My report is grouped on ChildID (Primary Key), so
it should be grouped by that.

"Duane Hookom" wrote:

How is a beginning balance calculated? Is it the sum of all transactions
prior to the start date?

Do you need this by customer or client?

--
Duane Hookom
Microsoft Access MVP


"Difficult1" wrote:

Good afternoon. I am in need of a formula that will calculate a beggining
balance, based on a paramenter that is already set up for that report.

What I have is a parameter that asks for the first statement date (7/1/09)
and ending statement date (07/31/09). This paramenter is in the query
criteria box
for field
"[Transactions].[Date]". This makes my report show only detail between those
dates, which is working perfectly. I have a field in my report called
"[StmtAmt]"
This "[StmtAmt]" field is the one that I want to total before the first
statement date.

I cannot seem to figure this one out. I know there must be a logical way to
do it, but, I am just not seeing it. I would like to think it would be as
simple as
"[StartDate]-1" in the criteria box of the query, then again, I may have
made myself more
of a mess than necessary.

Any help would be greatly appreciated!