It sounds as if there are two separate charges: labor and items. If it took
1.5 hours @ $125/hour to replace two rollers valued at $109, you would have:
= ([Hours] * [LaborRate]) + ([Quantity] * [UnitPrice])
= (1.5 * $125) + (2 * 109)
= $187.50 + $218
= $405.50
You may be able to manage all of this in one form as long as there is only
one item, but what if there are 2 or 3 or 10? What you really need is an
Invoice table (tblInvoice), an Items table (tblItems), and an InvoiceItems
table (tblInvoiceItems).
tblInvoice
InvoiceID (primary key, or PK)
InvoiceDate
Other items specific to the invoice
tblItems
ItemID (PK)
ItemDescription
UnitPrice
tblInvoiceItems
InvoiceItemsID (PK)
InvoiceID (foreign key, or FK)
ItemID (FK)
Qty
UnitPrice
Each Item is entered into the Items table by way of a form bound to the
table. Labor could be included as an Item.
Create a relationship between InvoiceID in tblInvoice and its namesake in
tblInvoiceItems. Do the same with ItemID. Each Invoice may have many
items, and each Item may be a part of many Invoices. This is a many-to-many
relationship, so the junction table tblInvoiceItems is needed to resolve the
relationship.
Create a form based on tblInvoice, with a continuous subform based on
tblInvoiceItems. Make a combo box (cboItem) on the subform. Set its
Control Source to ItemID. Make its Row Source based on tblItems. Let its
columns be ItemID, ItemDescription, and UnitPrice. Set its Bound Column to
1, and its column widths to something like 0";1.5";1". Set the last number
to 0 if you don't want to see the UnitPrice in the dropdown list.
Note that you will be storing UnitPrice both in tblItems and
tblInvoiceItems. This is because the UnitPrice is subject to change. You
will probably want to store the price in effect when the invoice was
created, while leaving yourself free to edit the price in tblItems. To
store UnitPrice in tblInvoiceItems, put something like this in the
AfterUpdate event for the combo box:
Me.UnitPrice = Me.cboItem.Column(2)
Note that column numbering is zero-based in this context, so Column 0 is the
first column, etc.
The Orders form and the Order subform from the Northwinds database that
ships with Access provides some illustration of how this works, although
UnitPrice is not in the Products form. In terms of structure Orders in
Northwind is analogous to tblInvoice, Products to tblItems, and Order
Details to tblInvoiceItems.
I realize this is a lot of stuff all of a sudden. However, beyond the
mathematical expression I expect you will need something like the structure
I have described to handle properly the situation you have described.
Stockwell43 said:
It's basically an invoice received by a company that maintence the
equipment.
So if it's a service invoice they will charge by the hour and any parts.
So if they repair a printer and it took 1.5 hours labor should be $187.50
and if the part was new rollers then unit price would be $109. So my
subform
will have two lines, one to show the labor and one to show the part. My
total
price should be 296.50. which should show on the main form in an unbound
textbox. All was working fine but I need to total the labor field if more
than one hour AND if no unit price is inserted.
Is this making sense or am I making this too confusing?
Thanks!
BruceM said:
You know your business rules. We do not. If you are building boats and
the
labor cost for each boat is the same, you do not need hours. However, in
that case I would expect the labor to be part of the UnitPrice. Some
explanation of the situation is needed before I or anybody else can say
if
you need an Hours field.
Hi Bruce,
No, I am not storing it on the table it is an unbound texbox that I am
doing
the calculating in the report.
Well, your correct. If I change the quantity to 2 the labor doesn't
change
and if unit price is null the total price stays the same. Do I need to
add
another field for hours? and then somehow have the total price add the
sum
of
Hours*Labor + Quantity*UnitPrice?
I sure wish I had knowledge of you folks, I would had this figured out
three
days ago.
)
Any help would be most appreciated!!!!
:
Help has more information about Nz.
How would you determine Total Price without a unit price? Would you
substitute 0 for Labor if it is null?
Assuming that the formula you want is:
(Quantity * UnitPrice) + Labor
rather than:
Quantity * (UnitPrice + Labor)
you could use Nz to substitute a 0 for Labor if it is null:
=([Quantity]*[UnitPrice]) + Nz([Labor],0)
Access will do the calculation without the parentheses around:
([Quantity]*[UnitPrice])
I added the parentheses to make it clearer here. You do need the
parentheses with Nz.
I have no suggestion about how to determine a TotalPrice without a
UnitPrice. What rule guides that situation? What would you
substitute
for
Null in UnitPrice? By the way, if you are storing TotalPrice, don't.
Calculate it on the fly as needed.
Hello,
I have a sub form with various fields including Quantity, Labor,
Unit
Price
and Total price. Every works fine except if I have a zero in labor
and
or
unit price my total price field doesn't calculate. here is what I am
using:
=[Quantity]*[UnitPrice]+[Labor]. I know I should be using the NZ
formula
but
don't know what it is. Any help would be appreciated!!
Thanks!!
