Combining data from two fields into one field

[C Parkins]

I am working on an inventory project with parent data that is used to create
child data fields. I would like to take the parent ID# ( a five digit number)
and add a alphabetic suffix ( A for the first sub part, B for the second...)
While still maintaining the original ID #. Currently the parent assembly is
in one table and the sub assembly data is created and placed into a second
table. All data fields are the same but some other data needs to be updated.
what is the best way to do this.

 

[Ken Sheridan]

In the SubAssemblies table use separate columns for the ID# and the letter
suffix. It’s a trivial task to concatenate the values when required. Make
both columns the composite primary key of the table. Consequently each
sub-assembly row will have an ID# which references its parent row in the
Assemblies table.

In a form bound to the SubAssemblies table you can compute the suffix in the
AfterUpdate event procedure of the ID# control (you'll have to use another
event, however, if the sub-assemblies are being entered via a linked subform
in which the ID# value is automatically inserted; the subform's Current event
procedure would probably do. In either event the code would be as follows:

Dim varLastChr As Variant
Dim strCriteria As String

' look up last suffix, if any, for current ID#
strCriteria = "[ID#] = " & Me.[ID#]
varLastChr = DMax("Suffix", "SubAssemblies", strCriteria)

' get ASCII code of last letter, add 1 and then
' get letter for that ASCII value
Me.Suffix = Chr(Asc(Nz(varLastChr, Chr(64)))+1)

'A' is Chr(65) so if no row yet exists for the ID# 1 (the DMax function
returns a Null) the ASCII code of Chr(64) is obtained and 1 added to give 'A'
as the first suffix.

The above assumes that the ID# column is a number data type. If its text
data type use:

strCriteria = "[ID#] = """ & Me.[ID#] & """"

What do you mean by "All data fields are the same but some other data needs
to be updated"? If you are duplicating non-key values in the two tables then
that introduces redundancy and gives rise to the risk of inconsistent data.
Only the ID# keys should have common values in both tables. Other columns
should represent attribute types specific to the assembly or sub-assembly.
You might have columns in SubAssemblies with the same names as those in
Assemblies, but only if the values for those columns in the former will
differ from those in the latter. It’s a question of what's known in the
jargon as 'functional dependency'. An analogous situation can be found in
the sample Northwind database where both the Products and OrderDetails tables
have UnitPrice columns. This is because the unit price of a product in the
latter is the price at the time of the order, whereas that in the former is
the current price, i.e. each is functionally dependent on the key of its
table.

Ken Sheridan
Stafford, England

 

[C Parkins]

Thanks for the information, I guess on some of the description I wasn't as
clear I thought I was being. I am using a barcode system for my inventory and
I have a 5 digit ID# for the primary part and a 5 digit and 1 alpha character
for each of the sub parts. This is a system I inherited and I have to work
within the system. what I would like to do is query for the tag#, and change
size and weight fields plus add a alpha character to the end of the tag#. I
have two existing tables that the field catagories are all the same but some
of the data i.e. size and weight change going from the original part to the
sub part. I am not very familiar with VBA but I am learning from necessity.
Thanks for your reply and any future help you can give.
--
C Parkins

In the SubAssemblies table use separate columns for the ID# and the letter
suffix. It’s a trivial task to concatenate the values when required. Make
both columns the composite primary key of the table. Consequently each
sub-assembly row will have an ID# which references its parent row in the
Assemblies table.

In a form bound to the SubAssemblies table you can compute the suffix in the
AfterUpdate event procedure of the ID# control (you'll have to use another
event, however, if the sub-assemblies are being entered via a linked subform
in which the ID# value is automatically inserted; the subform's Current event
procedure would probably do. In either event the code would be as follows:

Dim varLastChr As Variant
Dim strCriteria As String

' look up last suffix, if any, for current ID#
strCriteria = "[ID#] = " & Me.[ID#]
varLastChr = DMax("Suffix", "SubAssemblies", strCriteria)

' get ASCII code of last letter, add 1 and then
' get letter for that ASCII value
Me.Suffix = Chr(Asc(Nz(varLastChr, Chr(64)))+1)

'A' is Chr(65) so if no row yet exists for the ID# 1 (the DMax function
returns a Null) the ASCII code of Chr(64) is obtained and 1 added to give 'A'
as the first suffix.

The above assumes that the ID# column is a number data type. If its text
data type use:

strCriteria = "[ID#] = """ & Me.[ID#] & """"

What do you mean by "All data fields are the same but some other data needs
to be updated"? If you are duplicating non-key values in the two tables then
that introduces redundancy and gives rise to the risk of inconsistent data.
Only the ID# keys should have common values in both tables. Other columns
should represent attribute types specific to the assembly or sub-assembly.
You might have columns in SubAssemblies with the same names as those in
Assemblies, but only if the values for those columns in the former will
differ from those in the latter. It’s a question of what's known in the
jargon as 'functional dependency'. An analogous situation can be found in
the sample Northwind database where both the Products and OrderDetails tables
have UnitPrice columns. This is because the unit price of a product in the
latter is the price at the time of the order, whereas that in the former is
the current price, i.e. each is functionally dependent on the key of its
table.

Ken Sheridan
Stafford, England

I am working on an inventory project with parent data that is used to create
child data fields. I would like to take the parent ID# ( a five digit number)
and add a alphabetic suffix ( A for the first sub part, B for the second...)
While still maintaining the original ID #. Currently the parent assembly is
in one table and the sub assembly data is created and placed into a second
table. All data fields are the same but some other data needs to be updated.
what is the best way to do this.

 

[C Parkins]

Thanks Ken,
I am working with this but have a question or two about table names. In the
strCriteria what does Me.[ID#] refer to? Is the Me. a table name? I know the
ID is a fileld name, I am using [TAG#] instead and the field is formatted as
text so I substituted the line as you suggest. but the module stops at the
strCriteria line. Any help we but appreciated.

Thanks again,
Chuck

 

[Douglas J. Steele]

"Me" is a way of referring to the form in which the code is running.
Me.[ID#] can refer to either the value in a control named ID# on the form,
or a field named ID# in the recordset to which the form is bound.

 

[C Parkins]

Ken,
Thanks for your help. I tried the code you suggested but all I can get it to
do is replace the tag number with a letter it isn't combining the two
together. Here is a copy of what I wrote:
Private Sub TAG___AfterUpdate()

Dim varLastChr As Variant
Dim strCriteria As String

' look up last suffix, if any, for current ID#
strCriteria = "[TAG #] = "" & Me.[Suffix]"""
varLastChr = DMax("SUFFIX", "SLIT NEW", strCriteria)

' get ASCII code of last letter, add 1 and then
' get letter for that ASCII value
Me.[TAG #] = Chr(Asc(Nz(varLastChr, Chr(64))) + 1)


End Sub
What I would like to do is query for a specific tag # copy that information
into the SLIT NEW table and add a suffix to the tag. I also need to change
the width and weight and for each individual tag# I might do this ten to
fifteen times. Can this be done? Any help would be appreciated. Thanks again,
--
C Parkins

In the SubAssemblies table use separate columns for the ID# and the letter
suffix. It’s a trivial task to concatenate the values when required. Make
both columns the composite primary key of the table. Consequently each
sub-assembly row will have an ID# which references its parent row in the
Assemblies table.

In a form bound to the SubAssemblies table you can compute the suffix in the
AfterUpdate event procedure of the ID# control (you'll have to use another
event, however, if the sub-assemblies are being entered via a linked subform
in which the ID# value is automatically inserted; the subform's Current event
procedure would probably do. In either event the code would be as follows:

Dim varLastChr As Variant
Dim strCriteria As String

' look up last suffix, if any, for current ID#
strCriteria = "[ID#] = " & Me.[ID#]
varLastChr = DMax("Suffix", "SubAssemblies", strCriteria)

' get ASCII code of last letter, add 1 and then
' get letter for that ASCII value
Me.Suffix = Chr(Asc(Nz(varLastChr, Chr(64)))+1)

'A' is Chr(65) so if no row yet exists for the ID# 1 (the DMax function
returns a Null) the ASCII code of Chr(64) is obtained and 1 added to give 'A'
as the first suffix.

The above assumes that the ID# column is a number data type. If its text
data type use:

strCriteria = "[ID#] = """ & Me.[ID#] & """"

What do you mean by "All data fields are the same but some other data needs
to be updated"? If you are duplicating non-key values in the two tables then
that introduces redundancy and gives rise to the risk of inconsistent data.
Only the ID# keys should have common values in both tables. Other columns
should represent attribute types specific to the assembly or sub-assembly.
You might have columns in SubAssemblies with the same names as those in
Assemblies, but only if the values for those columns in the former will
differ from those in the latter. It’s a question of what's known in the
jargon as 'functional dependency'. An analogous situation can be found in
the sample Northwind database where both the Products and OrderDetails tables
have UnitPrice columns. This is because the unit price of a product in the
latter is the price at the time of the order, whereas that in the former is
the current price, i.e. each is functionally dependent on the key of its
table.

Ken Sheridan
Stafford, England

I am working on an inventory project with parent data that is used to create
child data fields. I would like to take the parent ID# ( a five digit number)
and add a alphabetic suffix ( A for the first sub part, B for the second...)
While still maintaining the original ID #. Currently the parent assembly is
in one table and the sub assembly data is created and placed into a second
table. All data fields are the same but some other data needs to be updated.
what is the best way to do this.