updating a field in a form

[Guest]

i have created a database for a firm. I have used a query to calculate the
total costs of the products purchased. however i am having trouble showing
the outcome of the cost calculted on the form for each order?

Do i use an update query??

If so how??

Thanx in adv

 

[Steve Schapel]

John,

No, an Update Query would not be applicable in this case. An Update
Query updates data stored in a table, where your total costs is not in a
table.

Does your form show one Order at a time? If so, you can probably just
put an unbound textbox in the Form Footer section, with its Control
Source property set to an appropriate expression to calculate the Total
Costs right there on the form. Otherwise, you can make a query that
includes the existing Record Source of the form, plus the query you have
used to calculate the Total Costs, joined on the appropriate field, for
example OrderNumber. Then base your form on this second query, and then
you have the totals on the form bound to the query field. This approach
will only be good if the form is for data summary or viewing puposes,
you won't be able to enter/edit data. A third approach is to use a
DLookup() function or a DSum() function in the Control Source of a
textbox on the form. Which is the best approach will depend on the
details of what you are doing and what you want.

 

[Guest]

Still having problems with the total cost control source!

This is what i put in

((IIf([Discount 1]=Yes,0.95,1)*[Order 1]*3.52+IIf([Order 1]=0,0,2.5))
+((IIf([Discount 2]=Yes,0.95,1)*[Order 2]*3.52+IIf([Order
2]=0,0,2.5))+((IIf([Discount 3]=Yes,0.95,1)*[Order 3]*3.52+IIf([Order
3]=0,0,2.5))+
((IIf([Discount 4]=Yes,0.95,1)*[Order 4]*3.52+IIf([Order
4]=0,0,2.5))+((IIf([Discount 5]=Yes,0.95,1)*[Order 5]*3.52+IIf([Order
5]=0,0,2.5))

basically this is for a meat distribiting company. Each person can order up
to 5 peices of meat. if the meat they get is 1kg more or less than their
request they get 5% discount. btw Order1 is the actual weight of meat they
are getting. Discount must be done for individual meat and then totaled up.

3.52 - price per kg
2.5 - preperation price

to make sure that if the customer order less than 5 meat, the preperation
price doesnt add on to the total cost of the order i put in the if statement
to reduce the value of that order to 0.

Is what i have done here rite???

 

[Steve Schapel]

John,

What is the problem you are experiencing. I haven't analysed the
expression you gave in detail, but it would help to know what
specifically is not working for you.

Having said that, it looks like you have each order as separate fields
such as Order 1, Order 2, etc. Is this correct? If so, this would
definitely not be the recommended approach. It would be much better,
and make life a lot easier for you, if you had just one order quantity
field, and each of the person's (up to) 5 pieces of meat are recorded as
separate records. Is there any way you could review your table design
at this stage?

 

[Guest]

the order1-5 are the actual weights of the peices of meat they purchased.
its not too late to change the table design. how would i create this order
quantity field and what would it do?

Are u saying to create a command button that opens another form that will
have the 5 orders on it????

The problem i am having now is that i copied the exact same expression i
used in the query to calculate the price into the control source on the
unbound txt box u told me to put in but now i keep getting "#Error"

Realy apreciate your help

John,

What is the problem you are experiencing. I haven't analysed the
expression you gave in detail, but it would help to know what
specifically is not working for you.

Having said that, it looks like you have each order as separate fields
such as Order 1, Order 2, etc. Is this correct? If so, this would
definitely not be the recommended approach. It would be much better,
and make life a lot easier for you, if you had just one order quantity
field, and each of the person's (up to) 5 pieces of meat are recorded as
separate records. Is there any way you could review your table design
at this stage?

--
Steve Schapel, Microsoft Access MVP

Still having problems with the total cost control source!

This is what i put in

((IIf([Discount 1]=Yes,0.95,1)*[Order 1]*3.52+IIf([Order 1]=0,0,2.5))
+((IIf([Discount 2]=Yes,0.95,1)*[Order 2]*3.52+IIf([Order
2]=0,0,2.5))+((IIf([Discount 3]=Yes,0.95,1)*[Order 3]*3.52+IIf([Order
3]=0,0,2.5))+
((IIf([Discount 4]=Yes,0.95,1)*[Order 4]*3.52+IIf([Order
4]=0,0,2.5))+((IIf([Discount 5]=Yes,0.95,1)*[Order 5]*3.52+IIf([Order
5]=0,0,2.5))

basically this is for a meat distribiting company. Each person can order up
to 5 peices of meat. if the meat they get is 1kg more or less than their
request they get 5% discount. btw Order1 is the actual weight of meat they
are getting. Discount must be done for individual meat and then totaled up.

3.52 - price per kg
2.5 - preperation price

to make sure that if the customer order less than 5 meat, the preperation
price doesnt add on to the total cost of the order i put in the if statement
to reduce the value of that order to 0.

Is what i have done here rite???

 

[Steve Schapel]

John,

Possibly the reason for the #Error on the form is because in the
textbox's Control Source you need to put a = in front of the expression.
Although, it also seems to me that there are too many (s in there as
well. Shouldn't it be like this?...
=IIf([Discount 1]=Yes,0.95,1)*[Order 1]*3.52+IIf([Order
1]=0,0,2.5)+IIf([Discount 2]=Yes,0.95,1)*[Order 2]*3.52+IIf([Order
2]=0,0,2.5)+IIf([Discount 3]=Yes,0.95,1)*[Order 3]*3.52+IIf([Order
3]=0,0,2.5)+IIf([Discount 4]=Yes,0.95,1)*[Order 4]*3.52+IIf([Order
4]=0,0,2.5)+IIf([Discount 5]=Yes,0.95,1)
*[Order 5]*3.52+IIf([Order 5]=0,0,2.5)

Anyway, regarding your tables, I assume you also have a field or fields
that identify the customer, order date, order number perhaps, stuff like
that? This information should be in one table, and then you should have
another table for the purchases. This table should only have these fields:
OrderNumber
Discount
Order

So, if your first table is like this...
OrderNumber Customer OrderDate
1 Betty 5-Apr-05
2 Fred 6-Apr-05

.... and Fred purchases 5 pieces of meat, then his 5 orders will be
stored, from a table point of view, as 5 *records* in the second table,
not in 5 separate *fields*. This is the database approach, as distinct
from a spreadsheet or abacus approach. So the second table will have
like this...
OrderNumber Order Discount
2 7.3 Yes
2 5.4 Yes
2 1.6 No
etc

As for the form setup for entry of the data, the stanbdard procedure is
to use a Continuous View form, based on the second table, as a subform
on the main form which is based on the first table. Hope that sort of
makes sense. If any customer is restricted to a maximum of 5 orders,
you would need to use a procedure on the subform to do this... that's
another question, but it's quite easy to achieve. If you haven't done
this sort of thing before, there may be a learning process involved.
But in the end, it will be much better. Then, to get your total cost,
you put a relatively simple expression like this in a textbox in the
footer section of the subform...
=Sum([Order]*3.52*(1+[Discount]*0.05)+2.5)