Totals for a Purchase Order

[hellaby]

I'm a bit of a novice at Access, but have created a form and subform to allow
quick entry of Purchase Order details for our company. The purchase order is
for us to track who our suppliers are and to issue purchase order numbers for
suppliers. I have used unbound text boxes for Subtotal, Tax and Total fields
of the purchase order with the following code and everything works fine.

SubTotal =frm_PODetail.Form!txtOrder_Subtotal


Tax=CLng([Subtotal])*[Tax_Rate]*100/100

Total=[Subtotal]+[Tax]

However, my accounts department wants to track the tax figures and The
totals - the very thing I've been told you shouldn't store in a table. With
an unbound text box I can't run a query to supply the tax figures and totals.
I've tried creating an alias field in the underlying query for the form, but
I must be using the wrong expression because nothing shows up in the text
boxes.

Does anyone know how to run the query using unbound text boxes or how to
store tax and total figures so I can run a query?

 

[Maurice]

Have you tried adding a field in your underlying query. You could try
something like:

Fieldname: [field1]+[field2]

The fieldname before the : provides the alias which you can change to your
own name. Also replace the other two fields with your own fields. When you
save the query the added field will show up in the fieldname list of the form
and nothing is stored in the underlying table because it's a calculated field.

hth

 

[hellaby]

Have tried that, but nothing showed up in the textbox. I thought maybe I had
the wrong syntax.

Have you tried adding a field in your underlying query. You could try
something like:

Fieldname: [field1]+[field2]

The fieldname before the : provides the alias which you can change to your
own name. Also replace the other two fields with your own fields. When you
save the query the added field will show up in the fieldname list of the form
and nothing is stored in the underlying table because it's a calculated field.

hth
--
Maurice Ausum

I'm a bit of a novice at Access, but have created a form and subform to allow
quick entry of Purchase Order details for our company. The purchase order is
for us to track who our suppliers are and to issue purchase order numbers for
suppliers. I have used unbound text boxes for Subtotal, Tax and Total fields
of the purchase order with the following code and everything works fine.

SubTotal =frm_PODetail.Form!txtOrder_Subtotal


Tax=CLng([Subtotal])*[Tax_Rate]*100/100

Total=[Subtotal]+[Tax]

However, my accounts department wants to track the tax figures and The
totals - the very thing I've been told you shouldn't store in a table. With
an unbound text box I can't run a query to supply the tax figures and totals.
I've tried creating an alias field in the underlying query for the form, but
I must be using the wrong expression because nothing shows up in the text
boxes.

Does anyone know how to run the query using unbound text boxes or how to
store tax and total figures so I can run a query?

 

[Steve]

You need to use a totals query based on your purchase order details table to
get the subtotal.
Then create another query based on the totals query to get the total. In
this query you use calculated fields to get the tax and total:
Tax:CLng([Subtotal])*[Tax_Rate]*100/100
Total:[SubTotal] + [Tax]

You can use this query then to report tax and totals to your accounts
department.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[hellaby]

Thanks Steve, I do have these exact settings in my form, and the form works
fine if you just want to look at it. Unfortunately when I then try and do a
query I can't seem to use these calculations in the clculated query fields.
I think it's been complicated by the fact that my Tax_Rate is selected from a
combo box - there are four different taxes that we are tracking, dependent on
whether the supplier is local, international or registered for local taxes.
I don't seem to be able to use the queries you've listed without an error
occuring.

You need to use a totals query based on your purchase order details table to
get the subtotal.
Then create another query based on the totals query to get the total. In
this query you use calculated fields to get the tax and total:
Tax:CLng([Subtotal])*[Tax_Rate]*100/100
Total:[SubTotal] + [Tax]

You can use this query then to report tax and totals to your accounts
department.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

I'm a bit of a novice at Access, but have created a form and subform to
allow
quick entry of Purchase Order details for our company. The purchase order
is
for us to track who our suppliers are and to issue purchase order numbers
for
suppliers. I have used unbound text boxes for Subtotal, Tax and Total
fields
of the purchase order with the following code and everything works fine.

SubTotal =frm_PODetail.Form!txtOrder_Subtotal


Tax=CLng([Subtotal])*[Tax_Rate]*100/100

Total=[Subtotal]+[Tax]

However, my accounts department wants to track the tax figures and The
totals - the very thing I've been told you shouldn't store in a table.
With
an unbound text box I can't run a query to supply the tax figures and
totals.
I've tried creating an alias field in the underlying query for the form,
but
I must be using the wrong expression because nothing shows up in the text
boxes.

Does anyone know how to run the query using unbound text boxes or how to
store tax and total figures so I can run a query?

 

[Steve]

First of all you need a TaxID field that comes from a tax table or some
other source that defines your four different taxes. Then this source needs
to be included in your totals query so that your totals query calculates Tax
Rate.

Then, formulas in queries often are dofferent than formulas in forms. In
forms you refer to the name of a control that holds data needed in the
calculation. The name of a control on a form may or may not be the same as
the control source of the control. In a query, you refer to the name of a
field in a table or the name of another calculated field. If the name of a
control on a form is not the same as the control source of the control, then
your formula in the query is going to be different than the formula in the
form.

Note also that formulas in forms have an equal sign and formulas in queries
have a colon.

Once you resolve all the above issues, let's take solving your problem one
step at a time. So question #1: does your totals query give you the correct
subtotals?

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

Thanks Steve, I do have these exact settings in my form, and the form
works
fine if you just want to look at it. Unfortunately when I then try and do
a
query I can't seem to use these calculations in the clculated query
fields.
I think it's been complicated by the fact that my Tax_Rate is selected
from a
combo box - there are four different taxes that we are tracking, dependent
on
whether the supplier is local, international or registered for local
taxes.
I don't seem to be able to use the queries you've listed without an error
occuring.

You need to use a totals query based on your purchase order details table
to
get the subtotal.
Then create another query based on the totals query to get the total. In
this query you use calculated fields to get the tax and total:
Tax:CLng([Subtotal])*[Tax_Rate]*100/100
Total:[SubTotal] + [Tax]

You can use this query then to report tax and totals to your accounts
department.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

I'm a bit of a novice at Access, but have created a form and subform to
allow
quick entry of Purchase Order details for our company. The purchase
order
is
for us to track who our suppliers are and to issue purchase order
numbers
for
suppliers. I have used unbound text boxes for Subtotal, Tax and Total
fields
of the purchase order with the following code and everything works
fine.

SubTotal =frm_PODetail.Form!txtOrder_Subtotal


Tax=CLng([Subtotal])*[Tax_Rate]*100/100

Total=[Subtotal]+[Tax]

However, my accounts department wants to track the tax figures and The
totals - the very thing I've been told you shouldn't store in a table.
With
an unbound text box I can't run a query to supply the tax figures and
totals.
I've tried creating an alias field in the underlying query for the
form,
but
I must be using the wrong expression because nothing shows up in the
text
boxes.

Does anyone know how to run the query using unbound text boxes or how
to
store tax and total figures so I can run a query?