Sub Total Field

[Guest]

I designed a form to input invoice items.
I have Qty1, Description1, Price1 and a Calculated Field = TotalPrice1 with
formula=Qty1*Price1

The same for about 8 more line items with their respective number (Qty1,
Qty2, and so on).
I entered an unbound box for a subtotal Field with the following
formula=TotalPrice1+TotalPrice2 and so on up to the eight item.

My problem is that unless all fields (Qty and Price fields of each
respective line item) have a value the subtotal formula does not give a
calculated value.

In other words, I have to actually input zeros or actual numbers in order to
have the calculated sum of all totals give me the sum of all these fields.
I need to know the way to do this even if the TotalPrice fields on the
individual items are empty.
Thanks in advance

 

[Guest]

Just add the NZ function for each field

formula=nz(TotalPrice1,0)+nz(TotalPrice2,0) etc etc

 

[Steve Schapel]

Bikerman,

Whereas Ofer's suggestion is correct for your specific question, I
should mention that your database design is making things unnecessarily
difficult and complicated. What you have done would normally be
regarded as an invalid approach in a database. The different line items
in your invoice should not be separate fields in the same record. They
should be separate records in an additional related table. This is the
relational database approach. You might find the information here of
interest: http://support.microsoft.com/?id=283878 . With this design,
getting totals and other data management becomes much simpler and more
powerful. I would encourage you to review your table and form setup.

 

[Guest]

Steve,
Obviously I am not a relational database programer.
But instead of sending me to read a document that is totally outside of my
comprehensive abilities and does not mention how to create a simple invoice,
could you explain in lay terms how to approach this.
The reason I did it this way is because of the ease of how I could
accomplish my final goal which was to generate an ivoice from information
that I would input in the form.
Again, I don't want to recreate the wheel, however I cannot seem to find an
easy template in Access to create a simple invoice. Excel has the template,
however all of my customers data is in this database.
Jose

 

[Steve Schapel]

Jose,

There seems to be an inevitable "speed bump" to negotiate when getting
started with Access (or any database programme), even if the database
itself is simple. This involves understanding the principle of data
relationships. I referred you to that article, not because it is
relevant to your specific project, but because it may help with your
understanding of these principles. I know it is not easy, but
unfortunately necessary in order to use Access properly.

On the basis of what I know so far about your project, I would imagine
you need at least 3 tables, something like this:

Table: Customers
CustomerID
Customer
Address
ContactPerson
.... other customer-specific information

Table: Invoices
InvoiceNumber
CustomerID
InvoiceDate
Salesperson
.... other invoice-specific information

Table: InvoiceDetails
InvoiceDetailID
InvoiceNumber
Quantity
Item
UnitPrice
.... other information specific to each order line

There may be other tables required as well for other functionality, for
example if you want to record and track payments received. You will
probably also have a products table, I imagine. But this gives the
skeleton structure. This may seem more complicated at first glance,
compared to what you have now, but this is an illusion... this is the
way Access is designed to work most effectivley.

As for your data entry, the "standard" way to set this up is to have a
form based on the Invoices table, and another form, in continuous view,
based on the InvoiceDetails table, which is placed as a subform on the
Invoices form. You use the Link Master Fields and Link Child Fields
properties of the subform to relate the InvoiceDetails to the Invoice,
so that each order line entered via the form is automatically flagged
(via the InvoiceNumber field) as belonging to the current invoice.

I hope you will be able to look into this approach.

 

[Guest]

Thank you Steve,
Now this is more like it.
I really apreciate you taking the time to explain, expecifically how to set
up the tables. I kind of had it like you said, except for the invoice data
in one table and the invoice detail in another table.
Now all I have to do is master how to do the subforms without complicating
myself.
But I really apreciate the help.
Jose

 

[Steve Schapel]

Good luck, Jose. You will get plenty of help in this forum with
subforms, if you need it. Subforms are fantastic, and one of the key
features that sets Access head and shoulders above many other database
development tools.

Regards
Steve