How can Ii update a table field with data from a query?

  • Thread starter Thread starter Ciscus
  • Start date Start date
C

Ciscus

Hi,

I have limited knowledge of Microsoft Access and all VBA-code around it.

I am trying to create a database in which I can enter all financial numbers
of a musical group I am playing in. I have created a table which consists
the beginning ammount of our money, called "initial". In this table there
are also 2 fields which are namend "incoming" and "outgoing". These tree
fields are used in a query to calculate te next value of "initial"

I have used a query with an extra expression looking like this:

Calculated = SUM([initial]+[incoming]-[outgoing]

This seems to be working fine, but now I am stuck. I don't seem to be able
to copy the value of the last query-record into a new table-record
(initial-field).

Is this the correct way to do such a calculation and how can I copy the data
into my table?


Many thanks,

Ciscus.
 
Ciscus,

In answer to your question "Is this the correct way to do such a
calculation", I am sorry to say that the answer is No. There is no
value in replicating data from one record to another, nor is there any
purpose seerved by attempting to save calculated data. Do you also have
any other data, such as dates and notes. I recommend a table like this...
TransactionID
TransactionDate
Amount
Type (being incoming or outgoing)
Comment

The principle of a database approach is that the table is the storage
repository of the data, and is not normally seen by users. In such an
example, I would make the Type field a Number data type, and enter -1
for outgoing and 1 for incoming... this will assist with later
calculations required. An Option Group can be used on the data form to
make this easier and more foolproof. If you use a table design as
suggested, you will easily be able to derive whatever data you want via
a query whenever you need it.
 
Back
Top