Populate field with multiple data?

[Tom McMillion]

I am exploring the idea of populating a text field with
mulitple bits of data appended together, based on the
selections of check boxes made on a form. Each bit of
data will be shorthand for a packaging line number, for
example A1, A2, A3 etc. Each check box on a Scheduling
form will select one packaging line such as line A1.

A large packaging order can be split up to run on several
lines. In my database I cannot create duplicate records
sharing the same order number, and then select one
packaging line for each record. I have to share multiple
packaging lines within one record. Also I cannot create a
separate field for each individual packaging line because
there would be a huge number of fields in the table, as
well as a very large number of controls on at least one
of the forms.

I would like to populate my "Pkg Line" text field with
the ID's of, in some cases, several packaging lines
selected on a form via the selected check boxes.

Ideally I would like to pull together or append A1 and A2
for example into one text field with a space or some
other separator between A1 and A4 for easier viewing. But
at this point I would even settle for A1A4 to be
populated in the "Pkg Line" field, especially if the VBA
code is much easier to write versus A1 [space] A4.

Once again, thanks in advance for any help. These forums
and the people who monitor them and answer questions
provide a tremendous service to folks like me.

Kind Regards,
Tom

 

[HSalim]

It is quite easy to do what you want, but don't.
Except in a few rare cases, this approach is a bad idea.
The few cases where it is acceptable is in the case of reports where
you take data from a table and join it together for display purposes only.

See more comments inline

HS

I am exploring the idea of populating a text field with
mulitple bits of data appended together, based on the
selections of check boxes made on a form. Each bit of
data will be shorthand for a packaging line number, for
example A1, A2, A3 etc. Each check box on a Scheduling
form will select one packaging line such as line A1.

Do you plan to store this concatenated value in a table or display in
reports only.
If storing, how do you plan to handle updates/changes

A large packaging order can be split up to run on several
lines. In my database I cannot create duplicate records
sharing the same order number, and then select one
packaging line for each record. I have to share multiple
packaging lines within one record.

Also I cannot create a
separate field for each individual packaging line because
there would be a huge number of fields in the table, as
well as a very large number of controls on at least one
of the forms.

You are right but for the wrong reasons.
In other words, it would be a bad idea to do this even if you had a
manageable number (10?) of packaging lines,
because it violates the fundamentals of database design. (More on this
later if you need it.)
INstead, each packaging line should be a row in a new table and have a
1-many relationship with the orders table

With that, you do not need to touch the orders table except as a reference.
You can have as many rows as you want and manage them easily
You can still display (on a form) or report on them as columns using a cross
tab query.

I would like to populate my "Pkg Line" text field with
the ID's of, in some cases, several packaging lines
selected on a form via the selected check boxes.

Ideally I would like to pull together or append A1 and A2
for example into one text field with a space or some
other separator between A1 and A4 for easier viewing. But
at this point I would even settle for A1A4 to be
populated in the "Pkg Line" field, especially if the VBA
code is much easier to write versus A1 [space] A4.


Once again, thanks in advance for any help. These forums
and the people who monitor them and answer questions
provide a tremendous service to folks like me.

Kind Regards,
Tom