attaching a suffix

[C Parkins]

I am reposting,
I am new to access and need some help programming an inventory process. Let
me start by saying I don't need help with data table design I have
constraints that don't allow complete re design also I have a large amount of
existing data. I work with steel coils and I use a barcode system to keep up
with inventory. We have a tag system where each coils is assigned a tag #
based on a physical tag when the coil is received. The tag # is 5 digits. In
our processes we make narrower coils from the large coils i.e. a 36 inch wide
coil cuts into 6-six inch coils. each child coil retains the original 5 digit
number but is assigned a alpha suffix so my six coils would be; 12345A,
12345B, 12345C, 12345D, 12345E, 12345F. This I cannot change. I have 2 tables
each with it's own form, one for the large coils and one for the smaller
coils. I want to be able to query for the tag # and then automatically take
the large coil data and add a suffix and change the width and weight and loop
the process until all cuts are recorded. The process varies based on the
width of the large coil and the need for different sizes of small coils we
could cut 2 18 inch coils or 6 - 6 inch coils or 3 - 6 inch and 6 -3 inch. So
the number of coils is not the same everytime.

Because of the barcode system I have to have 5 digits and an alpha
character, so a seperate field for a suffix doesn't work well. I have tried
several database changes suggested but I really need to be able to do this
with the existing tables I have. The 2 tables have the same fields I just
need to change or update 3 fields in the cutting process. any help would be
appreciated.

Thanks in advance.

 

[Larry Daugherty]

I saw your original post and decided I didn't want to touch it. There
was obviously going to be an awful lot of Q & A. I later saw that
Jeff Boyce had joined you in a dialogue and believed that he would
help you to a solution or explain to you why you would have difficulty
achieving a solution to your current issue until you remediate
fundamental schema issues.

Is that about how it went? I didn't look at any of the subsequent
posts between you.

The idea of these microsoft.public.access* newsgroups is for peer
support for Access developers. When developers or users are faced
with a *technical* issue or strategy issue they can post it here and
usually get a satisfactory response. Those who respond are unpaid
volunteers. They are not required to provide free programming
services nor to do anything else. However, the standard of the
responses is pretty high.

None of us that I'm aware of would intentionally lead you astray.
However, in the effort to provide true benefit most of us insist that
the basic schema meet certain standards. A good schema is fundamental
to a maintainable and enhanceable application. The rule of thumb is
Normalization to 3rd Normal Form. An unnormalized schema is awkward,
difficult and expensive to maintain or enhance. Your schema has a few
obvious flaws.

By the way, when a poster, customer, client says "Please fix my
problem but don't look at or change [you fill in the blanks - you said
'tables']'" then you have a pretty good idea where the worst of the
problems lie. If a prospective client were to approach me with your
issue and the constraints you insist be true then I'd reject that
opportunity. Before I'd try to enhance something I'd first fix all
that was truly broken.

You may be able to pay someone by the hour to provide the
functionality you seek without making the fundamental changes. In all
likelihood it can be done. The path to success would be expensive.
Succeeding enhancements would keep getting more expensive.

HTH

 

[C Parkins]

Yes Larry that is about right. Jeff was very helpful. I don't have any
programming knowledge or I might be able to get to where I want to go. I did
purchase a VB book and will start learning. I did have a VB program written
by a former emplyee that did all of the things I am looking for. The problem
was the database had not been maintained and nobody new how to fix it. It
also was not an access database.
I can change my tables and I did try some of what Jeff and others suggested.
I did a seperate field for a suffix but I could not get it to combine with
the tag number in such a way to work with the barcode file. The barcode file
is a program I am stuck with for now but the access database I have control
over. I just don't want to reformat thousands of records. I appreciate all of
the help I have received and If anyone else can help I would appreciate it.
Larry, thanks for your reply and assistance.
--
C Parkins

I saw your original post and decided I didn't want to touch it. There
was obviously going to be an awful lot of Q & A. I later saw that
Jeff Boyce had joined you in a dialogue and believed that he would
help you to a solution or explain to you why you would have difficulty
achieving a solution to your current issue until you remediate
fundamental schema issues.

Is that about how it went? I didn't look at any of the subsequent
posts between you.

The idea of these microsoft.public.access* newsgroups is for peer
support for Access developers. When developers or users are faced
with a *technical* issue or strategy issue they can post it here and
usually get a satisfactory response. Those who respond are unpaid
volunteers. They are not required to provide free programming
services nor to do anything else. However, the standard of the
responses is pretty high.

None of us that I'm aware of would intentionally lead you astray.
However, in the effort to provide true benefit most of us insist that
the basic schema meet certain standards. A good schema is fundamental
to a maintainable and enhanceable application. The rule of thumb is
Normalization to 3rd Normal Form. An unnormalized schema is awkward,
difficult and expensive to maintain or enhance. Your schema has a few
obvious flaws.

By the way, when a poster, customer, client says "Please fix my
problem but don't look at or change [you fill in the blanks - you said
'tables']'" then you have a pretty good idea where the worst of the
problems lie. If a prospective client were to approach me with your
issue and the constraints you insist be true then I'd reject that
opportunity. Before I'd try to enhance something I'd first fix all
that was truly broken.

You may be able to pay someone by the hour to provide the
functionality you seek without making the fundamental changes. In all
likelihood it can be done. The path to success would be expensive.
Succeeding enhancements would keep getting more expensive.

HTH
--
-Larry-
--

I am reposting,
I am new to access and need some help programming an inventory process. Let
me start by saying I don't need help with data table design I have
constraints that don't allow complete re design also I have a large amount of
existing data. I work with steel coils and I use a barcode system to keep up
with inventory. We have a tag system where each coils is assigned a tag #
based on a physical tag when the coil is received. The tag # is 5 digits. In
our processes we make narrower coils from the large coils i.e. a 36 inch wide
coil cuts into 6-six inch coils. each child coil retains the original 5 digit
number but is assigned a alpha suffix so my six coils would be; 12345A,
12345B, 12345C, 12345D, 12345E, 12345F. This I cannot change. I have 2 tables
each with it's own form, one for the large coils and one for the smaller
coils. I want to be able to query for the tag # and then automatically take
the large coil data and add a suffix and change the width and weight and loop
the process until all cuts are recorded. The process varies based on the
width of the large coil and the need for different sizes of small coils we
could cut 2 18 inch coils or 6 - 6 inch coils or 3 - 6 inch and 6 -3 inch. So
the number of coils is not the same everytime.

Because of the barcode system I have to have 5 digits and an alpha
character, so a seperate field for a suffix doesn't work well. I have tried
several database changes suggested but I really need to be able to do this
with the existing tables I have. The 2 tables have the same fields I just
need to change or update 3 fields in the cutting process. any help would be
appreciated.

Thanks in advance.