Access 2003 : Saving data entered via a Form

[mcnaugg]

I am an absolute beginner to Access and I am having trouble saving my info
that I have entered via a Form.
I want to construct a very basic DB just to get the basics sorted out and I
built one for entering the cost of items purchased. There are three basic
fields 1) Item Cost, 2) Tax and 3) Total Cost. From a previous query
relating to calculating the Vat and Total Costs, I was advised to put "Item
Cost" and "Tax" on the form and also an unbound field (Total Cost) in which
the calculation can be performed. This I did and on the form the various
"records" that I entered were calculated correctly and appear on the form as
I scroll through the records.
However if I now look at the Table only the Item Cost field has been
populated, the "Tax " and "Total Cost" fields remain empty. This also
applies to a report designed via the Report Wizard.
What am I doing wrong?

 

[Amy Blankenship]

I am an absolute beginner to Access and I am having trouble saving my info
that I have entered via a Form.
I want to construct a very basic DB just to get the basics sorted out and
I
built one for entering the cost of items purchased. There are three basic
fields 1) Item Cost, 2) Tax and 3) Total Cost. From a previous query
relating to calculating the Vat and Total Costs, I was advised to put
"Item
Cost" and "Tax" on the form and also an unbound field (Total Cost) in
which
the calculation can be performed. This I did and on the form the various
"records" that I entered were calculated correctly and appear on the form
as
I scroll through the records.

Never store anything you can calculate.

I'd suggest something like this:

tblItems
itemId (autonumber primary key)
itemDesc
itemCost

tblInvoice
invoiceID (autonumber primary key)
CustomerID (foreign key to customer table, not discussed here)

tblTaxRates
taxRateID (autonumber primary key)
taxRate
taxRateEffective (date)

tblInvoiceItems
invoiceItemID (autonumber primary key)
itemID (Foreign key to tblItems)
invoiceID (FK to tblInvoice)
numberOrdered
itemCost
taxRateID

Some might say that you shouldn't record itemCost again, but should the base
cost of the item change over time, this will mean that your old invoices
will always be accurate.

From the cost, numberOrdered, and taxRateID, you should always be able to
calculate the totalCost on a form or report without ever having to store it.

I'd suggest that you use code in your form's onCurrent event to set the
defaultValue of taxRateID to the taxRateID with the most recent effective
date, but that you also make the control for that item a combobox so that
you can select a different tax rate if needed.

HTH;

Amy

 

[mcnaugg]

Hi Amy

Thanks for the reply. I think that your answer maybe above my level at the
moment but I will give it a try. I have made quite a few simple DBs where I
was entering text only (Family History records etc) but this was the first
time that I was trying to carry out any calculations. I was just trying a
very basic DB just to input the cost of items that I was buying and keep a
running total. I was not planning to go to anything more complex at this
stage. Once I had cracked that, then I would expand further.

I gather that I should have four Tables. How would I design the form to
input the data and also the report?

 

[Amy Blankenship]

Hi Amy

Thanks for the reply. I think that your answer maybe above my level at
the
moment but I will give it a try. I have made quite a few simple DBs where
I
was entering text only (Family History records etc) but this was the first
time that I was trying to carry out any calculations. I was just trying a
very basic DB just to input the cost of items that I was buying and keep a
running total. I was not planning to go to anything more complex at this
stage. Once I had cracked that, then I would expand further.

You may find that Excel is better for what you are doing. In Excel, you can
simply select a column and see its sum at the bottom.

I gather that I should have four Tables. How would I design the form to
input the data and also the report?

The structure I gave you is for if you are actually _selling_ a certain
inventory of items, for which you would always know a set cost. It might
also work if you always buy the same item at the same store, and so the cost
of the item doesn't change.

I don't think that a database is the best thing to use for the kind of
information you're storing, but someone else may be able to jump in with a
brilliant application they built to do just this.

 

[mcnaugg]

Hi

I agree that the db I am using as an example would be much easier done in
Excel and I have already gone that route successfully but I wanted to get the
very basics of DB design where there some calculations to be carried out even
if it is only multiplying Item cost against Vat and being able to print out a
report with the totals.

 

[Amy Blankenship]

Hi

I agree that the db I am using as an example would be much easier done in
Excel and I have already gone that route successfully but I wanted to get
the
very basics of DB design where there some calculations to be carried out
even
if it is only multiplying Item cost against Vat and being able to print
out a
report with the totals.

The problem is that you don't have a core set of information you'll be using
over and over, which is what the basics of database design is all about.
Calculations have nothing to do with the design of the database--they are
carried out in the forms, queries, and reports.

HTH;

Amy

 

[mcnaugg]

Hi

I think that this is where I'm falling down. I am trying to input data on
an ongoing basis via a form and then to be able to print a list of items
purchased and a running total. How is the "core set of info" inputted if not
via a form?

Gareth