create a field with text joined from two other fields

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

 

[John Vinson]

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]

 

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

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]