Multiple data in a single field

Discussion in 'Microsoft Access External Data' started by Christopher W via AccessMonster.com, Aug 10, 2005.

  1. 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.


    --
    Message posted via AccessMonster.com
    http://www.accessmonster.com/Uwe/Forums.aspx/access-externaldata/200508/1
     
    Christopher W via AccessMonster.com, Aug 10, 2005
    #1
    1. Advertisements

  2. Christopher W via AccessMonster.com

    Rick Brandt Guest

    Christopher W via AccessMonster.com wrote:
    > 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?

    --
    I don't check the Email account attached
    to this message. Send instead to...
    RBrandt at Hunter dot com
     
    Rick Brandt, Aug 10, 2005
    #2
  3. 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?

    Rick Brandt wrote:
    >> 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?
    >



    --
    Message posted via http://www.accessmonster.com
     
    Christopher W via AccessMonster.com, Aug 10, 2005
    #3
  4. Christopher W via AccessMonster.com

    Rick Brandt Guest

    Christopher W via AccessMonster.com wrote:
    > 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.

    --
    I don't check the Email account attached
    to this message. Send instead to...
    RBrandt at Hunter dot com
     
    Rick Brandt, Aug 10, 2005
    #4
  5. 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!!


    Rick Brandt wrote:
    >> 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.
    >



    --
    Message posted via http://www.accessmonster.com
     
    Christopher W via AccessMonster.com, Aug 11, 2005
    #5
  6. 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

    Christopher W wrote:
    >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!!
    >
    >>> 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

    >[quoted text clipped - 23 lines]
    >>order can have zero to (however many) line-items. Your structure could have
    >>zero to (however many) cost entries.



    --
    Message posted via AccessMonster.com
    http://www.accessmonster.com/Uwe/Forums.aspx/access-externaldata/200508/1
     
    Christopher W via AccessMonster.com, Aug 11, 2005
    #6

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. John Nurick

    Re: Combining fields from multiple records into a single memo field

    John Nurick, Feb 25, 2004, in forum: Microsoft Access External Data
    Replies:
    0
    Views:
    239
    John Nurick
    Feb 25, 2004
  2. judith

    Importing excel fields to a single memo field

    judith, Jun 7, 2004, in forum: Microsoft Access External Data
    Replies:
    1
    Views:
    184
    Alex Ivanov
    Jun 7, 2004
  3. Gilbert Noetzel

    During Import-force single data field from text to numeric - Help?!

    Gilbert Noetzel, Apr 26, 2007, in forum: Microsoft Access External Data
    Replies:
    1
    Views:
    373
    Jeff Boyce
    Apr 26, 2007
  4. Sekhar

    Appending data into a single field in MS Access

    Sekhar, Oct 7, 2007, in forum: Microsoft Access External Data
    Replies:
    1
    Views:
    254
    Roger Carlson
    Oct 8, 2007
  5. William

    IMPORTING A SINGLE EXCEL FIELD

    William, Aug 1, 2008, in forum: Microsoft Access External Data
    Replies:
    5
    Views:
    199
    William
    Aug 4, 2008
Loading...

Share This Page