Multiple data in a single field

[Christopher W via AccessMonster.com]

Firstly let me start by saying that I am a relative beginner to access
database development and a raw beginner to any programming. What I am trying
to do is to work out a way to store multiple data within one field. I know
that the theory is one field equals one entry and a field that requires more
than one entry should have its own table, however I don’t know how to get
this to work with the application I am looking for in my database.

Detail on my database.
This database is to store costing information on the valuation of assets.
There is a transaction table “Assets” and a number of other tables “generic
asset name”, “Manufacturer”, “Industry Classification” and “Client”, (If you
need to know the more relationship information let me know). In this
transaction table there is all the detail pertaining to the specific asset,
descriptions, model number, specifications, and more importantly the costing
information and a date this costing data was gathered.

My problem begins when I wish to revalue/update the asset; I will lose the
original data. Ideally what I would like to do is to store the historical
cost data in order to be able to develop graphs and future projections.

However there must be a better way than to just have lots and lots of date
and costing fields in the transaction table.

If someone could please help me it would be greatly appreciated.

 

[Rick Brandt]

Firstly let me start by saying that I am a relative beginner to access
database development and a raw beginner to any programming. What I am
trying to do is to work out a way to store multiple data within one
field. I know that the theory is one field equals one entry and a
field that requires more than one entry should have its own table,
however I don’t know how to get this to work with the application I
am looking for in my database.

Detail on my database.
This database is to store costing information on the valuation of
assets. There is a transaction table “Assets” and a number of other
tables “generic asset name”, “Manufacturer”, “Industry
Classification” and “Client”, (If you need to know the more
relationship information let me know). In this transaction table
there is all the detail pertaining to the specific asset,
descriptions, model number, specifications, and more importantly the
costing information and a date this costing data was gathered.

My problem begins when I wish to revalue/update the asset; I will
lose the original data. Ideally what I would like to do is to store
the historical cost data in order to be able to develop graphs and
future projections.

However there must be a better way than to just have lots and lots of
date and costing fields in the transaction table.

If someone could please help me it would be greatly appreciated.

You've already stated it. The costing and date go into a separate table and you
use a subform to enter it. Then you can easily graph the historical data for
each main record. There is not a better way than this. What is the problem
with this strategy that makes you feel a better way should exist?

 

[Christopher W via AccessMonster.com]

Thanks Rick for your response. I guess I don’t understand how it would work,
would I have a historical valuation table and store only old data or have all
the cost information in this table. Also in a report would I be able to list
the different valuations down the page one on top of each other or would they
have to be in a row. Also using the table method you still have to create a
field for every new valuation date and cost instead of having date field as a
heading and a number of revaluations underneath it. Or could you?

Firstly let me start by saying that I am a relative beginner to access
database development and a raw beginner to any programming. What I am

[quoted text clipped - 23 lines]

If someone could please help me it would be greatly appreciated.

You've already stated it. The costing and date go into a separate table and you
use a subform to enter it. Then you can easily graph the historical data for
each main record. There is not a better way than this. What is the problem
with this strategy that makes you feel a better way should exist?

 

[Rick Brandt]

Thanks Rick for your response. I guess I don’t understand how it
would work, would I have a historical valuation table and store only
old data or have all the cost information in this table.

All in one table. The one with the newest date is current while all others are
historical.

Also in a
report would I be able to list the different valuations down the page
one on top of each other or would they have to be in a row.

The natural result would be a column, but you could use a custom function or a
subreport to display them in a row if desired.

Also
using the table method you still have to create a field for every new
valuation date and cost instead of having date field as a heading and
a number of revaluations underneath it. Or could you?

No. The separate table would contain a field or fields that link to the primary
table, a date field and the fields for your cost data. That one structure would
allow for an indefinite number of entries without adding any new fields.

This structure is very much like the classic sales order. There is a main order
record and many related line-item records for the items sold on the order. Any
order can have zero to (however many) line-items. Your structure could have
zero to (however many) cost entries.

 

[Christopher W via AccessMonster.com]

Thanks mate, I understand now.

I have established the new cost table changed the relationship and moved all
the cost data in to this table. I have now reached the point of altering my
forms and can’t seem to get the sub form to work adequately. In the
transaction form I want to add only new asset data, this includes asset info,
manufacturer, client, industry classification and generic assets data coming
from drop down combo boxes and a sub form for the costing information. I
can’t seem to get it to only allow the entry of data; it seems to what to
keep looking up old data from the information put in to the previously
mentioned fields.

In order to overcome this I entered in the Transaction table ID as the Link
Master and Link Child in the sub form preferences. I also entered the go to
new record ‘Do command’ for ‘on open’ (in the sub form preferences). In
addition I programmed an already existing button ‘go to new entry’ to make
the sub form go to a new entry in addition to the master form

However, the sub form still keeps displaying data as I enter in data to the
master form. Please Help?

Thanks for all your help, this is better than gold!!

 

[Christopher W via AccessMonster.com]

Acually don't worry about it i have worked it out. I needed to put data
entry in preferences to yes. Thanks for all your help

Thanks mate, I understand now.

I have established the new cost table changed the relationship and moved all
the cost data in to this table. I have now reached the point of altering my
forms and can’t seem to get the sub form to work adequately. In the
transaction form I want to add only new asset data, this includes asset info,
manufacturer, client, industry classification and generic assets data coming
from drop down combo boxes and a sub form for the costing information. I
can’t seem to get it to only allow the entry of data; it seems to what to
keep looking up old data from the information put in to the previously
mentioned fields.

In order to overcome this I entered in the Transaction table ID as the Link
Master and Link Child in the sub form preferences. I also entered the go to
new record ‘Do command’ for ‘on open’ (in the sub form preferences). In
addition I programmed an already existing button ‘go to new entry’ to make
the sub form go to a new entry in addition to the master form

However, the sub form still keeps displaying data as I enter in data to the
master form. Please Help?

Thanks for all your help, this is better than gold!!
[quoted text clipped - 23 lines]

order can have zero to (however many) line-items. Your structure could have
zero to (however many) cost entries.