Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

create a field with text joined from two other fields

Discussion in 'Information Technology' started by JiAngelo, Jul 28, 2006.

  1. JiAngelo

    JiAngelo
    Expand Collapse
    Guest

    We have a home building database which has

    1. a Community_Data: Table which contains fields [Subdiv_Name] and
    [Subdiv_Code] (which is a two letter abbreviation of the subdivision name.)

    2. a Lots: Table, in which Lot numbers are entered and then we select the
    [Subdiv_Name] from a drop down list based on [Community_Data.Subdiv_Name]

    In Lots: Table we would like to create another field named [Site_ID] which
    is automatically the sum of [Community_Data.Subdiv_Code] + [Lots.Lot_No]
    based on the [Subdiv_Name] that has been selected.

    I'm new to using access beyond straight forward listing/viewing of data and
    would appreciate a detailed explanation of how to accomplish this.

    Thanks.
     
  2. Loading...


  3. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Sun, 18 Jun 2006 07:56:02 -0700, JiAngelo
    <JiAngelo@discussions.microsoft.com> wrote:

    >We have a home building database which has
    >
    >1. a Community_Data: Table which contains fields [Subdiv_Name] and
    >[Subdiv_Code] (which is a two letter abbreviation of the subdivision name.)
    >
    >2. a Lots: Table, in which Lot numbers are entered and then we select the
    >[Subdiv_Name] from a drop down list based on [Community_Data.Subdiv_Name]


    Don't.

    The Lots table should contain the Subdiv_Code, as a link to the
    Community_Data table. It is NOT necessary or appropriate to store the
    name redundantly in Lots.

    I'd avoid the misleading, inefficient, and almost useless "Lookup
    Field" type. Don't do ANY entry in table datasheets; use a form, and
    put a Combo Box on the form based on Community_Data. This could store
    the Subdiv_Code while displaying the Subdiv_Name.

    >In Lots: Table we would like to create another field named [Site_ID] which
    >is automatically the sum of [Community_Data.Subdiv_Code] + [Lots.Lot_No]
    >based on the [Subdiv_Name] that has been selected.


    That's not a good idea, and it's not necessary. You can select up to
    TEN fields and click the Key icon to ensure that the combination is
    unique. Storing this data redundantly in another field in your table
    is a Bad Idea.

    Instead, you can dynamically concatenate the fields on demand - base a
    Form or Report on a query with a calculated field:

    ShowLotName: [Subiv_Code] & [Lot_No]

    or perhaps better, formatted:

    ShowLotName: [Subdiv_Code] & "-" & Format([Lot_No], "000")

    to display KL-003 for lot 3 in subdivision KL.

    John W. Vinson[MVP]
     
  4. JiAngelo

    JiAngelo
    Expand Collapse
    Guest

    I appreciate your response. Perhaps I'm looking at the database format all
    wrong.

    Understand that in our business, a house being built on Lot 1622 in Sherman
    Lakes Subdivision (SL), is given [Site_ID] = SL1622, which is the sum of the
    [Community_Data.Subdiv_Code]+[Lots.Lot_No] --Similarly, another Lot_No =
    1622, but in Highland Lakes (HL) would be uniquely assigned HL1622. This
    [Site_ID] is used by all subcontractors, clients, and our accounting system.

    I believe that selecting a project's [Site_ID] from a drop-down list on a
    report or form should instantly populate the report/form with the current
    database's tables or groups of information which are minimally organized as
    follows:

    [Clients] = Prospective Buyers interested in our [Lots] & [Floorplans]
    below
    [Floorplans] = Info regarding predefined house plans we are able to build.
    [Lots] = Sites available to build our [Floorplans] on,
    [Community_Data] = A subgroup of [Lots] containing the general
    subdivision info.

    Additional tables will be added to track client selections, and assignment
    of trades and suppliers to each project or [Site_ID]

    Based on your response, should I pull (lookup) the
    [Community_Data.Subdiv_Code] into the Lots: Table and then create a key based
    on [Lots.Subdiv_Code]+[Lots.Lot_No] ???

    Any insight you could provide would be greatly appreciated.

    JiAngelo.


    "John Vinson" wrote:

    > On Sun, 18 Jun 2006 07:56:02 -0700, JiAngelo
    > <JiAngelo@discussions.microsoft.com> wrote:
    >
    > >We have a home building database which has
    > >
    > >1. a Community_Data: Table which contains fields [Subdiv_Name] and
    > >[Subdiv_Code] (which is a two letter abbreviation of the subdivision name.)
    > >
    > >2. a Lots: Table, in which Lot numbers are entered and then we select the
    > >[Subdiv_Name] from a drop down list based on [Community_Data.Subdiv_Name]

    >
    > Don't.
    >
    > The Lots table should contain the Subdiv_Code, as a link to the
    > Community_Data table. It is NOT necessary or appropriate to store the
    > name redundantly in Lots.
    >
    > I'd avoid the misleading, inefficient, and almost useless "Lookup
    > Field" type. Don't do ANY entry in table datasheets; use a form, and
    > put a Combo Box on the form based on Community_Data. This could store
    > the Subdiv_Code while displaying the Subdiv_Name.
    >
    > >In Lots: Table we would like to create another field named [Site_ID] which
    > >is automatically the sum of [Community_Data.Subdiv_Code] + [Lots.Lot_No]
    > >based on the [Subdiv_Name] that has been selected.

    >
    > That's not a good idea, and it's not necessary. You can select up to
    > TEN fields and click the Key icon to ensure that the combination is
    > unique. Storing this data redundantly in another field in your table
    > is a Bad Idea.
    >
    > Instead, you can dynamically concatenate the fields on demand - base a
    > Form or Report on a query with a calculated field:
    >
    > ShowLotName: [Subiv_Code] & [Lot_No]
    >
    > or perhaps better, formatted:
    >
    > ShowLotName: [Subdiv_Code] & "-" & Format([Lot_No], "000")
    >
    > to display KL-003 for lot 3 in subdivision KL.
    >
    > John W. Vinson[MVP]
    >
     

Share This Page