Access 2003:Basic DB Design

[mcnaugg]

As a raw beginner to DB design I have made a number posts to the Discussion
Group asking for advice. Unfortunately the replies have generally further
confused me.
I have used Access in the past to store a number of Family History records
downloaded from various sites. This information was inputted manually via a
Form or via an Excel link. Susequently I could access this info via a query/
report as necessary.

I then tried to make a very basic DB to store a number of items that I
purchased. This consisted of four fields: “Itemâ€, “Costâ€, “Vat†and “Total
Costâ€. Since I didn,t know how to carry out calculations I was advised to
make put the first three fields into a table. Then make a form with these
three fields plus an “Unbound Field†in which to perform the calculation (
Cost * Vat). This I did and I entered a number of records. When scrolling
through the records (via the form) all of the fields could be seen and were
correctly calculated.
However if I now went to the table only the “Item†and “Cost†were
populated. The same happened if I produced a report.
I was then told that I must not store/save data in a table.
So I would be grateful if advice could be given on how to build a simple DB
to store a number of items that were purchased from various stores, input the
“before Vat cost†and then have the form show the actual Vat cost and the
total cost (with Vat). I would then like to be able to print a report
showing the items and related costs. Once I have cracked this then I can
proceed to build in Queries etc. and further expand the DB.

 

[Tom van Stiphout]

On Fri, 25 Apr 2008 05:40:00 -0700, mcnaugg

The advice so far has been good. You should indeed not have TotalCost
in the table. Delete that field.
Now create your first query. Select the table, drag the three fields
to the grid below, and create a fourth field by entering:
TotalCost: Cost + Vat
Save the query. Run it. Voila: 4 columns of data.
Use this query for your report.

You could now also modify your form (or create a second one, just for
an experiment), and base it on the query. Put all four fields on it.
Now you can enter data in the three fields and the fourth one will
automatically be recalculated, even if you don't put that expression
in the field (because it is already in the query).

Now programming in Access is starting to be fun!

-Tom.

 

[mcnaugg]

Hi Tom

Thanks for the reply.

Let me please understand your advice.
In the table I have 3 fields, "Item", "ItemCost" and "Vat". From these
three fields I produce a form to allow the necessary info to be inputted. I
then create a query using the above Table.

This I have done and the 3 fields are included automatically. Now with the
query opened do I make a new field in the Field Row name TotalCost? If so
where do I input "Cost + Vat"?

 

[mcnaugg]

Hi Tom

Forget my last message. I have just realised what I was doing wrong and have
now entered the full text as you indicated into the next field cell available
and Eureka it works.

Thanks 100%

Gareth