Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Combining feilds (1)

    Is it possible to combine 2 different feilds from 2 different tables to get one data feild?? I have office ID from office table and function code from function type table and would like to combine these 2 to get a Function Ref for Business functions table?? Will I be able to do this? if yes, How?? I need to do this so I know which function which office. Each office have more or less all of the functions. PLEASE HELP ME! See attached excel sheet.
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combining feilds (1)

    You can use the ampersand & to concatenate fields.

    Create a new query in design view.
    Add both tables to the query.
    Add the fields you want to display.
    Add a calculated field:

    Function Ref:[Office ID]&[Func Code]

    Note: unless you have a join between the tables, or an intermediate table that defines the possible combinations, you will end up with the so-called Cartesian product of *all* possible combinations of records from the two tables.

  3. #3
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combining feilds (1)

    Thank you sooooo much,
    U R my star!!

    <img src=/S/kiss.gif border=0 alt=kiss width=34 height=15> <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

  4. #4
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combining feilds (1)

    The ONLY problem with this is that, its creating the value but its not storing the value in the table. How do I store the created value in the data table??? <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combining feilds (1)

    In general, it is not advisable to store calculated information in a table.
    <UL><LI>It is redundant information; it increases database size unnecessarily.
    <LI>It is liable to become out of date; if Office ID or Func Code changes, the concatenated value in Business functions won't be updated automatically, so you end up with invalid entries.[/list]But if you really want to store these values, you can create an update query or an append query.
    <UL><LI>If the Business functions table contains fields for Office ID and Func Code, create a query based on the Business functions, Office and Function types tables, and join them on Office ID and Func Code. Use the Query menu to change it into an update query. In the Change To row for the field to be filled, enter [Office ID]&[Func code].
    <LI>If the Business functions table doesn't contain Office ID and Func Code fields, and is still empty, change the query I described in my previous reply into an append query (using the Query menu). Specify Business functions as the table to append to. Then, specify that the field that must be filled.[/list]In both cases, execute the query by selecting Query/Run.

  6. #6
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combining feilds (1)

    Not to worry, I have put some code behind the field in the form design to create and store the data to the table.
    <img src=/S/bow.gif border=0 alt=bow width=15 height=15>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •