Adding a suffix

[C Parkins]

I am a relatively new user to access. I am having a problem. I have 2 tables,
each table has the same field and it is formatted as text. I need to be able
to query the data from table A, add a suffix and update at least 2 other
fields. Then I need that record inserted into table B. I need to repeat this
N number of times depending on how many objects I change. I need to find a
way to do this short of changing my tables or adding more tables, I realize
that this may not be the best way to do what I need but I have limited time
and a database that I have to work within what is already in place. Thanks in
advance for any help.

 

[Jeff Boyce]

You've described a "how", as in how you are attempting to solve some
business need.

If you'll post more specifics about the underlying buiness need, folks here
may be able to offer more specific suggestions on "how".

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[C Parkins]

--
C Parkins

You've described a "how", as in how you are attempting to solve some
business need.

If you'll post more specifics about the underlying buiness need, folks here
may be able to offer more specific suggestions on "how".

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[C Parkins]

Thanks for your reply Jeff,
I don't know what happened to my last post, everything I typed went missing.
So I will try to give a better explaination again.

I am in manufacturing and we use steel coils. I have access table that are
not mine but I have adapted. I cannot make great changes but I want to
automate some repetative tasks.

Currently I enter the coil data in a form linked to a table. It contains a
lot of different data like: tag # (a 5 digit number), Vendor, PO #, date in ,
date out, width, decimal, gauge, weight and several others. I don't use all
of this information but others do or I need it for reports. I have a second
form linked to a second table that is for smaller width coils. We purchase
some from outside vendors, so the tag# is a 5 digit number. We also cut the
above mentioned large coils with a tag# already assigned into smaller width
coils so the 5 digit tag# gets a letter suffix. The system of tag # and
suffix is already in place and I cannot change it. So I am looking for a way
to assign suffixes to a tag #.

The large coil could be 36" or 48" wide and we would cut it into 6 6" coils
or maybe 1 12" and 4 6" coils. It varies by inventory need. I need a
subroutine to loop x number of times until all small coils are recorded. I am
doing this for tracabilty back to the large coil and it's source. I hope this
explains it better. Any help is appreciated.

Thanks,

 

[Jeff Boyce]

Instead of looking to add a suffix to an existing field, consider adding a
"suffix" field. One of the basic premises in good database design is "one
fact, one field". Your "add a suffix in the same field" would violate that
basic design premise.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[C Parkins]

I do have a suffix field, how do I several suffixes to one tag # and also the
individual size and weight. This would also seem to defeat my barcode system
as I need the suffix attached to the tag number to query and reference
between my CSV file and access. If this can be done with a suffix field I
would be interested in testing it out. But I am having difficulty seeing how
this would work for my application.
Thanks again,

 

[Jeff Boyce]

If I understood your description, you have a 'one-to-many' situation. In a
relational database like Access, you solve that by adding another table, a
"child" table, to hold the "many" records.

If you need to have a way to show (or use) [Field1] & [Field2] (e.g., Tag# &
Suffix), you can easily concatenate these in a query.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[C Parkins]

Jeff,
I am relatively new to Access but I am learning as I go. I do in fact have
to tables a parent and a child. A one to many relationship might work I just
don't understand how each child coil would have a unique ID. How would I
associate numerous suffixes to a tag #. I don't have a primary key which
maybe part of the issue. I originally could not assign a primary as I had
duplicate data I had to eliminate. However I believe now I could assign a
primary key. I then would be able to link a suffix to a primary key field and
have more than one suffix linked. Is this correct or am I misunderstanding
you?

Thanks for your patience,
--
C Parkins

If I understood your description, you have a 'one-to-many' situation. In a
relational database like Access, you solve that by adding another table, a
"child" table, to hold the "many" records.

If you need to have a way to show (or use) [Field1] & [Field2] (e.g., Tag# &
Suffix), you can easily concatenate these in a query.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jeff Boyce]

When you set up a parent/child relationship, you add a "foreign key" field
to the child table to hold the primary key/ID value of the row in the parent
table to which the child belongs. More than one child? More than one row!
(that's the 'many' part of it).

No primary key in the parent table? So create one!

And since the suffix sounds like it's associated with the child record(s),
you don't need a suffix field in the parent table.

You can use a query to re-connect the two (parent & any children) for
display purposes, say, in a report.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,
I am relatively new to Access but I am learning as I go. I do in fact have
to tables a parent and a child. A one to many relationship might work I
just
don't understand how each child coil would have a unique ID. How would I
associate numerous suffixes to a tag #. I don't have a primary key which
maybe part of the issue. I originally could not assign a primary as I had
duplicate data I had to eliminate. However I believe now I could assign a
primary key. I then would be able to link a suffix to a primary key field
and
have more than one suffix linked. Is this correct or am I misunderstanding
you?

Thanks for your patience,
--
C Parkins

If I understood your description, you have a 'one-to-many' situation. In
a
relational database like Access, you solve that by adding another table,
a
"child" table, to hold the "many" records.

If you need to have a way to show (or use) [Field1] & [Field2] (e.g.,
Tag# &
Suffix), you can easily concatenate these in a query.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I do have a suffix field, how do I several suffixes to one tag # and
also
the
individual size and weight. This would also seem to defeat my barcode
system
as I need the suffix attached to the tag number to query and reference
between my CSV file and access. If this can be done with a suffix field
I
would be interested in testing it out. But I am having difficulty
seeing
how
this would work for my application.
Thanks again,
--
C Parkins


:

Instead of looking to add a suffix to an existing field, consider
adding
a
"suffix" field. One of the basic premises in good database design is
"one
fact, one field". Your "add a suffix in the same field" would violate
that
basic design premise.

Regards

Jeff Boyce
Microsoft Office/Access MVP


Thanks for your reply Jeff,
I don't know what happened to my last post, everything I typed went
missing.
So I will try to give a better explaination again.

I am in manufacturing and we use steel coils. I have access table
that
are
not mine but I have adapted. I cannot make great changes but I want
to
automate some repetative tasks.

Currently I enter the coil data in a form linked to a table. It
contains a
lot of different data like: tag # (a 5 digit number), Vendor, PO #,
date
in ,
date out, width, decimal, gauge, weight and several others. I don't
use
all
of this information but others do or I need it for reports. I have a
second
form linked to a second table that is for smaller width coils. We
purchase
some from outside vendors, so the tag# is a 5 digit number. We also
cut
the
above mentioned large coils with a tag# already assigned into
smaller
width
coils so the 5 digit tag# gets a letter suffix. The system of tag #
and
suffix is already in place and I cannot change it. So I am looking
for
a
way
to assign suffixes to a tag #.

The large coil could be 36" or 48" wide and we would cut it into 6
6"
coils
or maybe 1 12" and 4 6" coils. It varies by inventory need. I need a
subroutine to loop x number of times until all small coils are
recorded. I
am
doing this for tracabilty back to the large coil and it's source. I
hope
this
explains it better. Any help is appreciated.

Thanks,
--
C Parkins


:

You've described a "how", as in how you are attempting to solve
some
business need.

If you'll post more specifics about the underlying buiness need,
folks
here
may be able to offer more specific suggestions on "how".

Regards

Jeff Boyce
Microsoft Office/Access MVP

I am a relatively new user to access. I am having a problem. I
have 2
tables,
each table has the same field and it is formatted as text. I need
to
be
able
to query the data from table A, add a suffix and update at least
2
other
fields. Then I need that record inserted into table B. I need to
repeat
this
N number of times depending on how many objects I change. I need
to
find a
way to do this short of changing my tables or adding more tables,
I
realize
that this may not be the best way to do what I need but I have
limited
time
and a database that I have to work within what is already in
place.
Thanks
in
advance for any help.

 

[C Parkins]

Thanks for your help Jeff,
I am starting to figure this out. I have set up my test database and have it
operating with the parent table primary key and the child table everything
seems to be working. I do have a couple of questions though. First, not all
of my slit coils are child coils from the original table, they are purchased
outside. So for ID purposes they have the 5 digit tag # with no suffix. I
want to treat them the same as the child coils with the suffix added. The
problem seems to be two different forms to add data to the same table. Is
that correct? and is there a way to do this from one table? The format that I
used was the Parent table, a query in a form to retrieve the parent data and
save the child data to the child table. However the query form does not allow
to input new data. for convience one form would be desirable for both
functions. Also, when comparing data against my barcode file I need a single
field with both the tag # and suffix combined. I assume I can concatenate the
data into a data table which I can compare and edit against my csv file. Is
that correct.

Thanks,
--
C Parkins

When you set up a parent/child relationship, you add a "foreign key" field
to the child table to hold the primary key/ID value of the row in the parent
table to which the child belongs. More than one child? More than one row!
(that's the 'many' part of it).

No primary key in the parent table? So create one!

And since the suffix sounds like it's associated with the child record(s),
you don't need a suffix field in the parent table.

You can use a query to re-connect the two (parent & any children) for
display purposes, say, in a report.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,
I am relatively new to Access but I am learning as I go. I do in fact have
to tables a parent and a child. A one to many relationship might work I
just
don't understand how each child coil would have a unique ID. How would I
associate numerous suffixes to a tag #. I don't have a primary key which
maybe part of the issue. I originally could not assign a primary as I had
duplicate data I had to eliminate. However I believe now I could assign a
primary key. I then would be able to link a suffix to a primary key field
and
have more than one suffix linked. Is this correct or am I misunderstanding
you?

Thanks for your patience,
--
C Parkins

If I understood your description, you have a 'one-to-many' situation. In
a
relational database like Access, you solve that by adding another table,
a
"child" table, to hold the "many" records.

If you need to have a way to show (or use) [Field1] & [Field2] (e.g.,
Tag# &
Suffix), you can easily concatenate these in a query.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I do have a suffix field, how do I several suffixes to one tag # and
also
the
individual size and weight. This would also seem to defeat my barcode
system
as I need the suffix attached to the tag number to query and reference
between my CSV file and access. If this can be done with a suffix field
I
would be interested in testing it out. But I am having difficulty
seeing
how
this would work for my application.
Thanks again,
--
C Parkins


:

Instead of looking to add a suffix to an existing field, consider
adding
a
"suffix" field. One of the basic premises in good database design is
"one
fact, one field". Your "add a suffix in the same field" would violate
that
basic design premise.

Regards

Jeff Boyce
Microsoft Office/Access MVP


Thanks for your reply Jeff,
I don't know what happened to my last post, everything I typed went
missing.
So I will try to give a better explaination again.

I am in manufacturing and we use steel coils. I have access table
that
are
not mine but I have adapted. I cannot make great changes but I want
to
automate some repetative tasks.

Currently I enter the coil data in a form linked to a table. It
contains a
lot of different data like: tag # (a 5 digit number), Vendor, PO #,
date
in ,
date out, width, decimal, gauge, weight and several others. I don't
use
all
of this information but others do or I need it for reports. I have a
second
form linked to a second table that is for smaller width coils. We
purchase
some from outside vendors, so the tag# is a 5 digit number. We also
cut
the
above mentioned large coils with a tag# already assigned into
smaller
width
coils so the 5 digit tag# gets a letter suffix. The system of tag #
and
suffix is already in place and I cannot change it. So I am looking
for
a
way
to assign suffixes to a tag #.

The large coil could be 36" or 48" wide and we would cut it into 6
6"
coils
or maybe 1 12" and 4 6" coils. It varies by inventory need. I need a
subroutine to loop x number of times until all small coils are
recorded. I
am
doing this for tracabilty back to the large coil and it's source. I
hope
this
explains it better. Any help is appreciated.

Thanks,
--
C Parkins


:

You've described a "how", as in how you are attempting to solve
some
business need.

If you'll post more specifics about the underlying buiness need,
folks
here
may be able to offer more specific suggestions on "how".

Regards

Jeff Boyce
Microsoft Office/Access MVP

I am a relatively new user to access. I am having a problem. I
have 2
tables,
each table has the same field and it is formatted as text. I need
to
be
able
to query the data from table A, add a suffix and update at least
2
other
fields. Then I need that record inserted into table B. I need to
repeat
this
N number of times depending on how many objects I change. I need
to
find a
way to do this short of changing my tables or adding more tables,
I
realize
that this may not be the best way to do what I need but I have
limited
time
and a database that I have to work within what is already in
place.
Thanks
in
advance for any help.

 

[Jeff Boyce]

I'm at something of a loss to provide a specific description of a data
structure you can use to model the real world situation you're faced with...
probably because I don't understand the real world situation you are faced
with!

It sounds like those "slit coils" you track that are NOT children of a
parent coil (i.e., "purchased outside") may (?!or may not?!) be analogous to
those that are children. Just because you call them the same name ("slit
coil") doesn't make them the same thing.

So, you have parent coils, and children of those parent coils, but you ?also
have coils that you call the same name as the children coils but they aren't
children of any your parent coils.

Are there other real world "exceptions" that you'll need to track?

How you create your relational table structure depends on what you are
modeling.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks for your help Jeff,
I am starting to figure this out. I have set up my test database and have
it
operating with the parent table primary key and the child table everything
seems to be working. I do have a couple of questions though. First, not
all
of my slit coils are child coils from the original table, they are
purchased
outside. So for ID purposes they have the 5 digit tag # with no suffix. I
want to treat them the same as the child coils with the suffix added. The
problem seems to be two different forms to add data to the same table. Is
that correct? and is there a way to do this from one table? The format
that I
used was the Parent table, a query in a form to retrieve the parent data
and
save the child data to the child table. However the query form does not
allow
to input new data. for convience one form would be desirable for both
functions. Also, when comparing data against my barcode file I need a
single
field with both the tag # and suffix combined. I assume I can concatenate
the
data into a data table which I can compare and edit against my csv file.
Is
that correct.

Thanks,
--
C Parkins

When you set up a parent/child relationship, you add a "foreign key"
field
to the child table to hold the primary key/ID value of the row in the
parent
table to which the child belongs. More than one child? More than one
row!
(that's the 'many' part of it).

No primary key in the parent table? So create one!

And since the suffix sounds like it's associated with the child
record(s),
you don't need a suffix field in the parent table.

You can use a query to re-connect the two (parent & any children) for
display purposes, say, in a report.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,
I am relatively new to Access but I am learning as I go. I do in fact
have
to tables a parent and a child. A one to many relationship might work I
just
don't understand how each child coil would have a unique ID. How would
I
associate numerous suffixes to a tag #. I don't have a primary key
which
maybe part of the issue. I originally could not assign a primary as I
had
duplicate data I had to eliminate. However I believe now I could assign
a
primary key. I then would be able to link a suffix to a primary key
field
and
have more than one suffix linked. Is this correct or am I
misunderstanding
you?

Thanks for your patience,
--
C Parkins


:

If I understood your description, you have a 'one-to-many' situation.
In
a
relational database like Access, you solve that by adding another
table,
a
"child" table, to hold the "many" records.

If you need to have a way to show (or use) [Field1] & [Field2] (e.g.,
Tag# &
Suffix), you can easily concatenate these in a query.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I do have a suffix field, how do I several suffixes to one tag # and
also
the
individual size and weight. This would also seem to defeat my
barcode
system
as I need the suffix attached to the tag number to query and
reference
between my CSV file and access. If this can be done with a suffix
field
I
would be interested in testing it out. But I am having difficulty
seeing
how
this would work for my application.
Thanks again,
--
C Parkins


:

Instead of looking to add a suffix to an existing field, consider
adding
a
"suffix" field. One of the basic premises in good database design
is
"one
fact, one field". Your "add a suffix in the same field" would
violate
that
basic design premise.

Regards

Jeff Boyce
Microsoft Office/Access MVP


Thanks for your reply Jeff,
I don't know what happened to my last post, everything I typed
went
missing.
So I will try to give a better explaination again.

I am in manufacturing and we use steel coils. I have access table
that
are
not mine but I have adapted. I cannot make great changes but I
want
to
automate some repetative tasks.

Currently I enter the coil data in a form linked to a table. It
contains a
lot of different data like: tag # (a 5 digit number), Vendor, PO
#,
date
in ,
date out, width, decimal, gauge, weight and several others. I
don't
use
all
of this information but others do or I need it for reports. I
have a
second
form linked to a second table that is for smaller width coils. We
purchase
some from outside vendors, so the tag# is a 5 digit number. We
also
cut
the
above mentioned large coils with a tag# already assigned into
smaller
width
coils so the 5 digit tag# gets a letter suffix. The system of tag
#
and
suffix is already in place and I cannot change it. So I am
looking
for
a
way
to assign suffixes to a tag #.

The large coil could be 36" or 48" wide and we would cut it into
6
6"
coils
or maybe 1 12" and 4 6" coils. It varies by inventory need. I
need a
subroutine to loop x number of times until all small coils are
recorded. I
am
doing this for tracabilty back to the large coil and it's source.
I
hope
this
explains it better. Any help is appreciated.

Thanks,
--
C Parkins


:

You've described a "how", as in how you are attempting to solve
some
business need.

If you'll post more specifics about the underlying buiness need,
folks
here
may be able to offer more specific suggestions on "how".

Regards

Jeff Boyce
Microsoft Office/Access MVP

message
I am a relatively new user to access. I am having a problem. I
have 2
tables,
each table has the same field and it is formatted as text. I
need
to
be
able
to query the data from table A, add a suffix and update at
least
2
other
fields. Then I need that record inserted into table B. I need
to
repeat
this
N number of times depending on how many objects I change. I
need
to
find a
way to do this short of changing my tables or adding more
tables,
I
realize
that this may not be the best way to do what I need but I have
limited
time
and a database that I have to work within what is already in
place.
Thanks
in
advance for any help.

 

[C Parkins]

Jeff,
This is the real problem The construct outside of Access that I am working
with has 3 things Coils, internal slit coils, external slit coil. I need to
track all coils for my purposes, however others are only concerned about the
coils and slit coils that come in from outside. All coils and all slit coils
coming from a vendor get processed with a tag assigning a number. These tags
are already in place. All slit coils I generate internally I assign a alpha
suffix to. This as well was already in place. I have model my access database
based on this process, right or wrong. Hence, the need to assign a suffix
attached to the tag#. Currently I do all of this manually and input it into
access. I was wanting to automatic the data input and save sometime. Again I
am somewhat new to access although I have worked with excel for many years
and have some shared knowledge of the two programs. However, I am new to both
macros and VBA programming. This is my problem.

My frustration is similiar to what you describe. If I was starting from
scratch I could do it the right way. But I have to work within the system
that was here before me.
I can adapt and change my database but there is a lot of data to reformat or
change. Thanks for all of your help so far. If you can make further
suggestions I would appreciate that, if not I will keep going until I can
make it work.

Thanks,

--
C Parkins

I'm at something of a loss to provide a specific description of a data
structure you can use to model the real world situation you're faced with...
probably because I don't understand the real world situation you are faced
with!

It sounds like those "slit coils" you track that are NOT children of a
parent coil (i.e., "purchased outside") may (?!or may not?!) be analogous to
those that are children. Just because you call them the same name ("slit
coil") doesn't make them the same thing.

So, you have parent coils, and children of those parent coils, but you ?also
have coils that you call the same name as the children coils but they aren't
children of any your parent coils.

Are there other real world "exceptions" that you'll need to track?

How you create your relational table structure depends on what you are
modeling.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks for your help Jeff,
I am starting to figure this out. I have set up my test database and have
it
operating with the parent table primary key and the child table everything
seems to be working. I do have a couple of questions though. First, not
all
of my slit coils are child coils from the original table, they are
purchased
outside. So for ID purposes they have the 5 digit tag # with no suffix. I
want to treat them the same as the child coils with the suffix added. The
problem seems to be two different forms to add data to the same table. Is
that correct? and is there a way to do this from one table? The format
that I
used was the Parent table, a query in a form to retrieve the parent data
and
save the child data to the child table. However the query form does not
allow
to input new data. for convience one form would be desirable for both
functions. Also, when comparing data against my barcode file I need a
single
field with both the tag # and suffix combined. I assume I can concatenate
the
data into a data table which I can compare and edit against my csv file.
Is
that correct.

Thanks,
--
C Parkins

When you set up a parent/child relationship, you add a "foreign key"
field
to the child table to hold the primary key/ID value of the row in the
parent
table to which the child belongs. More than one child? More than one
row!
(that's the 'many' part of it).

No primary key in the parent table? So create one!

And since the suffix sounds like it's associated with the child
record(s),
you don't need a suffix field in the parent table.

You can use a query to re-connect the two (parent & any children) for
display purposes, say, in a report.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,
I am relatively new to Access but I am learning as I go. I do in fact
have
to tables a parent and a child. A one to many relationship might work I
just
don't understand how each child coil would have a unique ID. How would
I
associate numerous suffixes to a tag #. I don't have a primary key
which
maybe part of the issue. I originally could not assign a primary as I
had
duplicate data I had to eliminate. However I believe now I could assign
a
primary key. I then would be able to link a suffix to a primary key
field
and
have more than one suffix linked. Is this correct or am I
misunderstanding
you?

Thanks for your patience,
--
C Parkins


:

If I understood your description, you have a 'one-to-many' situation.
In
a
relational database like Access, you solve that by adding another
table,
a
"child" table, to hold the "many" records.

If you need to have a way to show (or use) [Field1] & [Field2] (e.g.,
Tag# &
Suffix), you can easily concatenate these in a query.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I do have a suffix field, how do I several suffixes to one tag # and
also
the
individual size and weight. This would also seem to defeat my
barcode
system
as I need the suffix attached to the tag number to query and
reference
between my CSV file and access. If this can be done with a suffix
field
I
would be interested in testing it out. But I am having difficulty
seeing
how
this would work for my application.
Thanks again,
--
C Parkins


:

Instead of looking to add a suffix to an existing field, consider
adding
a
"suffix" field. One of the basic premises in good database design
is
"one
fact, one field". Your "add a suffix in the same field" would
violate
that
basic design premise.

Regards

Jeff Boyce
Microsoft Office/Access MVP


Thanks for your reply Jeff,
I don't know what happened to my last post, everything I typed
went
missing.
So I will try to give a better explaination again.

I am in manufacturing and we use steel coils. I have access table
that
are
not mine but I have adapted. I cannot make great changes but I
want
to
automate some repetative tasks.

Currently I enter the coil data in a form linked to a table. It
contains a
lot of different data like: tag # (a 5 digit number), Vendor, PO
#,
date
in ,
date out, width, decimal, gauge, weight and several others. I
don't
use
all
of this information but others do or I need it for reports. I
have a
second
form linked to a second table that is for smaller width coils. We
purchase
some from outside vendors, so the tag# is a 5 digit number. We
also
cut
the
above mentioned large coils with a tag# already assigned into
smaller
width
coils so the 5 digit tag# gets a letter suffix. The system of tag
#
and
suffix is already in place and I cannot change it. So I am
looking
for
a
way
to assign suffixes to a tag #.

The large coil could be 36" or 48" wide and we would cut it into
6
6"
coils
or maybe 1 12" and 4 6" coils. It varies by inventory need. I
need a
subroutine to loop x number of times until all small coils are
recorded. I
am
doing this for tracabilty back to the large coil and it's source.
I
hope
this
explains it better. Any help is appreciated.

Thanks,
--
C Parkins


:

You've described a "how", as in how you are attempting to solve
some
business need.

If you'll post more specifics about the underlying buiness need,
folks
here
may be able to offer more specific suggestions on "how".

Regards

Jeff Boyce
Microsoft Office/Access MVP

message
I am a relatively new user to access. I am having a problem. I
have 2
tables,
each table has the same field and it is formatted as text. I
need
to
be
able
to query the data from table A, add a suffix and update at
least
2
other
fields. Then I need that record inserted into table B. I need
to
repeat
this
N number of times depending on how many objects I change. I
need
to
find a
way to do this short of changing my tables or adding more
tables,
I
realize
that this may not be the best way to do what I need but I have
limited
time
and a database that I have to work within what is already in
place.
Thanks
in
advance for any help.

 

[Jeff Boyce]

For some folks, previous experience with Excel proves to be more a wall than
a door <g>!

I'm not clear on how much lattitude you have to make improvements ... it
sounds like you are able to modify as needed.

What are the constraints of the "system that was here before me"?

I'm getting the impression that the tag# (and suffix) are being used for
more than one purpose (i.e., to uniquely identify each object, and to
indicate parentage, and ...?). Using a tag# and suffix are a solution to a
business need ... but whether they are the solution to YOUR business need
is something only you will be able to determine.

If every object (coil, slit coil) needs a unique identifier, AND if you need
to track 'parentage', perhaps you can keep the existing tag#s and add a
unique identifier field. If you did that, I could imagine adding one more
field to hold 'parentage' (i.e., the tag# of the parent coil, if any). This
approach would NOT require a child table, as each object would have a type
(i.e., "parent", "slit"), and might have a value in the [ParentTag#] field.

I'm sorta rambling here, wondering if any of these ideas offer you a
different perspective.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[C Parkins]

Jeff,
I think you understand what I am working with. The database is wholly mine
and I can do with it what I want. The problem stems from the barcode and tag
number and suffix. the tag number and suffix are not up for discussion, Hence
the best solution in my mind is to add a suffix to the tag number as this is
how the company tracks the coils. This would seem to be relatively simple to
do in access with VB I just don't kow VB that well. I understand my database
design may not be the best design but it is designed with in the constraints
of the way the company operates. My biggest limitation is the barcode. I can
change and reformat data but I need a tag# with a suffix. How I get there
doesn't matter.

I would like to query a tag# from the Coil table and add a suffix, and
change the width and weight and do this in a loop until all cuts have been
indentified. I have the query and I am able to make updates I just can't
figure out how to add the suffix and do this process numerous times. Any help
along this line would be greatly appreciated.

Thanks Jeff,

--
C Parkins

For some folks, previous experience with Excel proves to be more a wall than
a door <g>!

I'm not clear on how much lattitude you have to make improvements ... it
sounds like you are able to modify as needed.

What are the constraints of the "system that was here before me"?

I'm getting the impression that the tag# (and suffix) are being used for
more than one purpose (i.e., to uniquely identify each object, and to
indicate parentage, and ...?). Using a tag# and suffix are a solution to a
business need ... but whether they are the solution to YOUR business need
is something only you will be able to determine.

If every object (coil, slit coil) needs a unique identifier, AND if you need
to track 'parentage', perhaps you can keep the existing tag#s and add a
unique identifier field. If you did that, I could imagine adding one more
field to hold 'parentage' (i.e., the tag# of the parent coil, if any). This
approach would NOT require a child table, as each object would have a type
(i.e., "parent", "slit"), and might have a value in the [ParentTag#] field.

I'm sorta rambling here, wondering if any of these ideas offer you a
different perspective.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jeff Boyce]

I hadn't caught that the "parent" coil was being repeatedly subdivided until
done.

As I don't have experience with this kind of process, I'm going to suggest
that you re-post, using some of the clarifications we've done, to get fresh
"eyes" on your situation.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,
I think you understand what I am working with. The database is wholly mine
and I can do with it what I want. The problem stems from the barcode and
tag
number and suffix. the tag number and suffix are not up for discussion,
Hence
the best solution in my mind is to add a suffix to the tag number as this
is
how the company tracks the coils. This would seem to be relatively simple
to
do in access with VB I just don't kow VB that well. I understand my
database
design may not be the best design but it is designed with in the
constraints
of the way the company operates. My biggest limitation is the barcode. I
can
change and reformat data but I need a tag# with a suffix. How I get there
doesn't matter.

I would like to query a tag# from the Coil table and add a suffix, and
change the width and weight and do this in a loop until all cuts have been
indentified. I have the query and I am able to make updates I just can't
figure out how to add the suffix and do this process numerous times. Any
help
along this line would be greatly appreciated.

Thanks Jeff,

--
C Parkins

For some folks, previous experience with Excel proves to be more a wall
than
a door <g>!

I'm not clear on how much lattitude you have to make improvements ... it
sounds like you are able to modify as needed.

What are the constraints of the "system that was here before me"?

I'm getting the impression that the tag# (and suffix) are being used for
more than one purpose (i.e., to uniquely identify each object, and to
indicate parentage, and ...?). Using a tag# and suffix are a solution to
a
business need ... but whether they are the solution to YOUR business
need
is something only you will be able to determine.

If every object (coil, slit coil) needs a unique identifier, AND if you
need
to track 'parentage', perhaps you can keep the existing tag#s and add a
unique identifier field. If you did that, I could imagine adding one
more
field to hold 'parentage' (i.e., the tag# of the parent coil, if any).
This
approach would NOT require a child table, as each object would have a
type
(i.e., "parent", "slit"), and might have a value in the [ParentTag#]
field.

I'm sorta rambling here, wondering if any of these ideas offer you a
different perspective.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,
This is the real problem The construct outside of Access that I am
working
with has 3 things Coils, internal slit coils, external slit coil. I
need
to
track all coils for my purposes, however others are only concerned
about
the
coils and slit coils that come in from outside. All coils and all slit
coils
coming from a vendor get processed with a tag assigning a number. These
tags
are already in place. All slit coils I generate internally I assign a
alpha
suffix to. This as well was already in place. I have model my access
database
based on this process, right or wrong. Hence, the need to assign a
suffix
attached to the tag#. Currently I do all of this manually and input it
into
access. I was wanting to automatic the data input and save sometime.
Again
I
am somewhat new to access although I have worked with excel for many
years
and have some shared knowledge of the two programs. However, I am new
to
both
macros and VBA programming. This is my problem.

My frustration is similiar to what you describe. If I was starting from
scratch I could do it the right way. But I have to work within the
system
that was here before me.
I can adapt and change my database but there is a lot of data to
reformat
or
change. Thanks for all of your help so far. If you can make further
suggestions I would appreciate that, if not I will keep going until I
can
make it work.

Thanks,