Calculating on a Continuous Form

[FrankTimJr]

I have two different questions, but all related to the same continuous form.

1 - I'm creating a database that tracks pending orders (orders not yet
billed) as well as completed orders (orders billed). I want to create a
form that calculates both the pending orders separate from the completed
orders, with a subtotal between pending and completed. Basically it would
look something like this:

(Order Type is the category set by the user, the default of course is
Pending and when the order is billed, the user will change the Order Type to
"Billed".)

Order Type: Revenue
Pending Orders (Jan): $1,000
Pending Orders (Feb): $1,000
Pending Orders (Mar): $1,000
Total Pending Orders: $3,000 (a sum of the three months)
Billed Orders: $2,000
Total Pending + Total Billed: $5,000

The Revenue for both Pending AND Billed Orders is the same field called
"Revenue". If I create a text box and add a formula, it's going to add both
the Pending AND Billed together and I want to separate them, THEN a grand
total of both categories.

2 - I want to be able to calculate a "Revenue Target" against the "Total
Pending + Total Billed" which would look something like this (a continuation
of the above layout):

Total Pending + Total Billed: $5,000
Revenue Target: $8,000
Revenue Gap: $3,000 (Total Pending + Total Billed) minus Revenue Target

The roadblock I'm running into is, the Revenue Target is not linked to the
main query the form is linked to, so how can I add a control to a form that
isn't linked? I could add an unbound control, but then the user would have
to enter the Revenue Target every time the form is opened since there is no
way to "save" the value entered into the unbound control unless there is a
way to change the defaultvalue of the unbound control.

Any help would be MOST appreciated.
Thanks,
Frank

 

[Marshall Barton]

I have two different questions, but all related to the same continuous form.

1 - I'm creating a database that tracks pending orders (orders not yet
billed) as well as completed orders (orders billed). I want to create a
form that calculates both the pending orders separate from the completed
orders, with a subtotal between pending and completed. Basically it would
look something like this:

(Order Type is the category set by the user, the default of course is
Pending and when the order is billed, the user will change the Order Type to
"Billed".)

Order Type: Revenue
Pending Orders (Jan): $1,000
Pending Orders (Feb): $1,000
Pending Orders (Mar): $1,000
Total Pending Orders: $3,000 (a sum of the three months)
Billed Orders: $2,000
Total Pending + Total Billed: $5,000

The Revenue for both Pending AND Billed Orders is the same field called
"Revenue". If I create a text box and add a formula, it's going to add both
the Pending AND Billed together and I want to separate them, THEN a grand
total of both categories.

2 - I want to be able to calculate a "Revenue Target" against the "Total
Pending + Total Billed" which would look something like this (a continuation
of the above layout):

Total Pending + Total Billed: $5,000
Revenue Target: $8,000
Revenue Gap: $3,000 (Total Pending + Total Billed) minus Revenue Target

The roadblock I'm running into is, the Revenue Target is not linked to the
main query the form is linked to, so how can I add a control to a form that
isn't linked? I could add an unbound control, but then the user would have
to enter the Revenue Target every time the form is opened since there is no
way to "save" the value entered into the unbound control unless there is a
way to change the defaultvalue of the unbound control.

Use text boxes in the form's Footer section to calculate the
totals:
txtTotalPending
=Sum(IIf([Order Type] = "Pending", Revenue, 0))
txtTotalBilled
=Sum(IIf([Order Type] = "Billed", Revenue, 0))
txtPendingAndBilled
=txtTotalPending + txtTotalBilled
txtTarget
=DLookup("Target", "TargetsTable", ? ? ?)
txtRevenueGap
=txtTarget - txtPendingAndBilled

The details of how to use Dlookup for the target depends on
the targets table and its fields.

 

[FrankTimJr]

You are a GENIUS!! Not only did the first solution work, but the second and
more important solution worked as well!

I set up the targets table as such:

tbl_Targets
Jan_Target
Feb_Target
etc...

Then in the form, I used this formula: =DLookup("Jan_Target","tbl_Targets")
and it worked perfectly. I didn't quite understand what the extra "? ? ?"
was in the example you gave, but setting it up the way i did worked, unless
you see I need to add something else to the DLOOKUP formula?

I have two different questions, but all related to the same continuous
form.

1 - I'm creating a database that tracks pending orders (orders not yet
billed) as well as completed orders (orders billed). I want to create a
form that calculates both the pending orders separate from the completed
orders, with a subtotal between pending and completed. Basically it would
look something like this:

(Order Type is the category set by the user, the default of course is
Pending and when the order is billed, the user will change the Order Type
to
"Billed".)

Order Type: Revenue
Pending Orders (Jan): $1,000
Pending Orders (Feb): $1,000
Pending Orders (Mar): $1,000
Total Pending Orders: $3,000 (a sum of the three months)
Billed Orders: $2,000
Total Pending + Total Billed: $5,000

The Revenue for both Pending AND Billed Orders is the same field called
"Revenue". If I create a text box and add a formula, it's going to add
both
the Pending AND Billed together and I want to separate them, THEN a grand
total of both categories.

2 - I want to be able to calculate a "Revenue Target" against the "Total
Pending + Total Billed" which would look something like this (a
continuation
of the above layout):

Total Pending + Total Billed: $5,000
Revenue Target: $8,000
Revenue Gap: $3,000 (Total Pending + Total Billed) minus Revenue Target

The roadblock I'm running into is, the Revenue Target is not linked to the
main query the form is linked to, so how can I add a control to a form
that
isn't linked? I could add an unbound control, but then the user would
have
to enter the Revenue Target every time the form is opened since there is
no
way to "save" the value entered into the unbound control unless there is a
way to change the defaultvalue of the unbound control.

Use text boxes in the form's Footer section to calculate the
totals:
txtTotalPending
=Sum(IIf([Order Type] = "Pending", Revenue, 0))
txtTotalBilled
=Sum(IIf([Order Type] = "Billed", Revenue, 0))
txtPendingAndBilled
=txtTotalPending + txtTotalBilled
txtTarget
=DLookup("Target", "TargetsTable", ? ? ?)
txtRevenueGap
=txtTarget - txtPendingAndBilled

The details of how to use Dlookup for the target depends on
the targets table and its fields.

 

[Marshall Barton]

The ??? were because I had no idea what your Targets table
looked like. I still don't know enough to definitively say
that's wrong, but what will you do next January? Reuse the
Jan_Target field and lose the previous years data or add
another field with a different name?

A more normalized design would only have two fields, one for
the month-year and the other for the target value. In this
case the code would be more like:
=DLookup("target","tbl_targets", "targetYYYYMM = """ &
Format(Date(), "yyyymm") & """")
for the current month. Or for the previous month:
Format(DateAdd("m", -1, Date(), "yyyymm")

Even that may be inappopriate depending on what your app
needs to do over the long haul. I guess my point here is
that now is the time to think this through so you don't have
to redo all this every month or year or if the users decide
they need a different range of months or whatever.
--
Marsh
MVP [MS Access]

You are a GENIUS!! Not only did the first solution work, but the second and
more important solution worked as well!

I set up the targets table as such:

tbl_Targets
Jan_Target
Feb_Target
etc...

Then in the form, I used this formula: =DLookup("Jan_Target","tbl_Targets")
and it worked perfectly. I didn't quite understand what the extra "? ? ?"
was in the example you gave, but setting it up the way i did worked, unless
you see I need to add something else to the DLOOKUP formula?

"Marshall Barton" wrote

I have two different questions, but all related to the same continuous
form.

1 - I'm creating a database that tracks pending orders (orders not yet
billed) as well as completed orders (orders billed). I want to create a
form that calculates both the pending orders separate from the completed
orders, with a subtotal between pending and completed. Basically it would
look something like this:

(Order Type is the category set by the user, the default of course is
Pending and when the order is billed, the user will change the Order Type
to
"Billed".)

Order Type: Revenue
Pending Orders (Jan): $1,000
Pending Orders (Feb): $1,000
Pending Orders (Mar): $1,000
Total Pending Orders: $3,000 (a sum of the three months)
Billed Orders: $2,000
Total Pending + Total Billed: $5,000

The Revenue for both Pending AND Billed Orders is the same field called
"Revenue". If I create a text box and add a formula, it's going to add
both
the Pending AND Billed together and I want to separate them, THEN a grand
total of both categories.

2 - I want to be able to calculate a "Revenue Target" against the "Total
Pending + Total Billed" which would look something like this (a
continuation
of the above layout):

Total Pending + Total Billed: $5,000
Revenue Target: $8,000
Revenue Gap: $3,000 (Total Pending + Total Billed) minus Revenue Target

The roadblock I'm running into is, the Revenue Target is not linked to the
main query the form is linked to, so how can I add a control to a form
that
isn't linked? I could add an unbound control, but then the user would
have
to enter the Revenue Target every time the form is opened since there is
no
way to "save" the value entered into the unbound control unless there is a
way to change the defaultvalue of the unbound control.

Use text boxes in the form's Footer section to calculate the
totals:
txtTotalPending
=Sum(IIf([Order Type] = "Pending", Revenue, 0))
txtTotalBilled
=Sum(IIf([Order Type] = "Billed", Revenue, 0))
txtPendingAndBilled
=txtTotalPending + txtTotalBilled
txtTarget
=DLookup("Target", "TargetsTable", ? ? ?)
txtRevenueGap
=txtTarget - txtPendingAndBilled

The details of how to use Dlookup for the target depends on
the targets table and its fields.