update a sequence of records to seperate fields in new table

[efandango]

How can I update a sequence of field's contents from one table to a sequence
of seperate fields in another table.

For example

Master Table

[Job No] 211
[Field X] Apples
Oranges
Lemons

Table 2
[Job No] 211
Field 1: Apples
Field 2: Oranges
Field 3: Lemons


Each Job has a different number of records, but with a known maximum of 30,
therefore I want to be able to take the field contents from 30 records and
place each one in order into 30 seperate fields in the new table.

How can I do this with code?

 

[Jeff Boyce]

Don't.

Access is not a spreadsheet.

What you described involves embedding data in field/columnnames. You'd
pretty much have to do this if you were using a spreadsheet, but Access is a
relational database.

You've described "how" you want to solve some problem. If you'll describe
the underlying problem/business need, folks here may be able to offer more
specific suggestions.

If you MUST do what you've described, try exporting to Excel and using the
Transpose function.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[efandango]

Jeff,

(it all began with the limitations of Continous Forms...)

I understand your rationale about Access not being a spreadsheet, but I have
a fundemental need to do it this way. The reason is because I want to have
control over each 'seperate' field within the form, so that I can make them
'visible' & 'Invisible' (and other stuff) depending on other actions. The
underlying reason for this is because access will not allow me sufficent
control over any single field record when using continous forms.

This all began when I had the requirement to allow a user to see a form of
seemingly blank records (max 30) and hit a button to make them visible; the
form is being used to help young people memorize certain lists of items in a
fixed order. I tried experimenting with conditional formatting which worked
to a point, in that if you selected a 'blank' field, upon focus it would
reveal its contents; the only flaw with this method was as soon as the field
lost focus, it would dissapear; I need it to stay revealed, gradually
reveling a full list.

So, I figured the only way around this problem is to somehow get access to
go through a table of master (groups of ) records and copy them into the
(max) 30 fields in a new table; then hey presto!, I can have control over
them. I want to beable to do all this in Access without user intervention, so
exporting to Excel, manual transposing, etc are not an option.

I hope someone can help me solve this problem.

Don't.

Access is not a spreadsheet.

What you described involves embedding data in field/columnnames. You'd
pretty much have to do this if you were using a spreadsheet, but Access is a
relational database.

You've described "how" you want to solve some problem. If you'll describe
the underlying problem/business need, folks here may be able to offer more
specific suggestions.

If you MUST do what you've described, try exporting to Excel and using the
Transpose function.

Regards

Jeff Boyce
Microsoft Office/Access MVP

How can I update a sequence of field's contents from one table to a
sequence
of seperate fields in another table.

For example

Master Table

[Job No] 211
[Field X] Apples
Oranges
Lemons

Table 2
[Job No] 211
Field 1: Apples
Field 2: Oranges
Field 3: Lemons


Each Job has a different number of records, but with a known maximum of
30,
therefore I want to be able to take the field contents from 30 records and
place each one in order into 30 seperate fields in the new table.

How can I do this with code?

 

[Jeff Boyce]

You realize, right, that if ANYTHING changes about the number of rows/fields
you are working with, you will have to rebuild tables, forms, queries,
reports, code, ... - a real maintenance nightmare.

Since it sounds like you have to have something that's like a spreadsheet,
could you just export the data to Excel and use a spreadsheet? I ask
because what you are trying to do with Access doesn't sound like a good
match for the tool. Can it be done, probably ... easily?

Best of luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

(it all began with the limitations of Continous Forms...)

I understand your rationale about Access not being a spreadsheet, but I
have
a fundemental need to do it this way. The reason is because I want to have
control over each 'seperate' field within the form, so that I can make
them
'visible' & 'Invisible' (and other stuff) depending on other actions. The
underlying reason for this is because access will not allow me sufficent
control over any single field record when using continous forms.

This all began when I had the requirement to allow a user to see a form of
seemingly blank records (max 30) and hit a button to make them visible;
the
form is being used to help young people memorize certain lists of items in
a
fixed order. I tried experimenting with conditional formatting which
worked
to a point, in that if you selected a 'blank' field, upon focus it would
reveal its contents; the only flaw with this method was as soon as the
field
lost focus, it would dissapear; I need it to stay revealed, gradually
reveling a full list.

So, I figured the only way around this problem is to somehow get access to
go through a table of master (groups of ) records and copy them into the
(max) 30 fields in a new table; then hey presto!, I can have control over
them. I want to beable to do all this in Access without user intervention,
so
exporting to Excel, manual transposing, etc are not an option.

I hope someone can help me solve this problem.

Don't.

Access is not a spreadsheet.

What you described involves embedding data in field/columnnames. You'd
pretty much have to do this if you were using a spreadsheet, but Access
is a
relational database.

You've described "how" you want to solve some problem. If you'll
describe
the underlying problem/business need, folks here may be able to offer
more
specific suggestions.

If you MUST do what you've described, try exporting to Excel and using
the
Transpose function.

Regards

Jeff Boyce
Microsoft Office/Access MVP

How can I update a sequence of field's contents from one table to a
sequence
of seperate fields in another table.

For example

Master Table

[Job No] 211
[Field X] Apples
Oranges
Lemons

Table 2
[Job No] 211
Field 1: Apples
Field 2: Oranges
Field 3: Lemons


Each Job has a different number of records, but with a known maximum of
30,
therefore I want to be able to take the field contents from 30 records
and
place each one in order into 30 seperate fields in the new table.

How can I do this with code?

 

[efandango]

Jeff,

perhaps I should have pointed out this is read-only data, just to be used on
the basis of a sequentially revealing list. As I mentioned, I don't want the
user having to deal with exports or spreadsheets. I was hoping to just beable
to create the table ultimately from a make table query. It is not so much a
question of whether it is a good match for the tool, more a question of needs
must. I realise that the process is not a straight forward query & code,
which is why I am seeking help; can you assist?

You realize, right, that if ANYTHING changes about the number of rows/fields
you are working with, you will have to rebuild tables, forms, queries,
reports, code, ... - a real maintenance nightmare.

Since it sounds like you have to have something that's like a spreadsheet,
could you just export the data to Excel and use a spreadsheet? I ask
because what you are trying to do with Access doesn't sound like a good
match for the tool. Can it be done, probably ... easily?

Best of luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

(it all began with the limitations of Continous Forms...)

I understand your rationale about Access not being a spreadsheet, but I
have
a fundemental need to do it this way. The reason is because I want to have
control over each 'seperate' field within the form, so that I can make
them
'visible' & 'Invisible' (and other stuff) depending on other actions. The
underlying reason for this is because access will not allow me sufficent
control over any single field record when using continous forms.

This all began when I had the requirement to allow a user to see a form of
seemingly blank records (max 30) and hit a button to make them visible;
the
form is being used to help young people memorize certain lists of items in
a
fixed order. I tried experimenting with conditional formatting which
worked
to a point, in that if you selected a 'blank' field, upon focus it would
reveal its contents; the only flaw with this method was as soon as the
field
lost focus, it would dissapear; I need it to stay revealed, gradually
reveling a full list.

So, I figured the only way around this problem is to somehow get access to
go through a table of master (groups of ) records and copy them into the
(max) 30 fields in a new table; then hey presto!, I can have control over
them. I want to beable to do all this in Access without user intervention,
so
exporting to Excel, manual transposing, etc are not an option.

I hope someone can help me solve this problem.

Don't.

Access is not a spreadsheet.

What you described involves embedding data in field/columnnames. You'd
pretty much have to do this if you were using a spreadsheet, but Access
is a
relational database.

You've described "how" you want to solve some problem. If you'll
describe
the underlying problem/business need, folks here may be able to offer
more
specific suggestions.

If you MUST do what you've described, try exporting to Excel and using
the
Transpose function.

Regards

Jeff Boyce
Microsoft Office/Access MVP

How can I update a sequence of field's contents from one table to a
sequence
of seperate fields in another table.

For example

Master Table

[Job No] 211
[Field X] Apples
Oranges
Lemons

Table 2
[Job No] 211
Field 1: Apples
Field 2: Oranges
Field 3: Lemons


Each Job has a different number of records, but with a known maximum of
30,
therefore I want to be able to take the field contents from 30 records
and
place each one in order into 30 seperate fields in the new table.

How can I do this with code?

 

[Jeff Boyce]

I'm not aware of a Transpose function in Access (perhaps for the reasons
discussed earlier).

Another approach, for you, not the user, would be to export it to Excel, use
Excel's Transpose function, then reimport into Access.

Perhaps one of the other newsgroup readers has experience with doing
something like this, or perhaps you and your users could reconsider how you
get this done...

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

perhaps I should have pointed out this is read-only data, just to be used
on
the basis of a sequentially revealing list. As I mentioned, I don't want
the
user having to deal with exports or spreadsheets. I was hoping to just
beable
to create the table ultimately from a make table query. It is not so much
a
question of whether it is a good match for the tool, more a question of
needs
must. I realise that the process is not a straight forward query & code,
which is why I am seeking help; can you assist?

You realize, right, that if ANYTHING changes about the number of
rows/fields
you are working with, you will have to rebuild tables, forms, queries,
reports, code, ... - a real maintenance nightmare.

Since it sounds like you have to have something that's like a
spreadsheet,
could you just export the data to Excel and use a spreadsheet? I ask
because what you are trying to do with Access doesn't sound like a good
match for the tool. Can it be done, probably ... easily?

Best of luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

(it all began with the limitations of Continous Forms...)

I understand your rationale about Access not being a spreadsheet, but I
have
a fundemental need to do it this way. The reason is because I want to
have
control over each 'seperate' field within the form, so that I can make
them
'visible' & 'Invisible' (and other stuff) depending on other actions.
The
underlying reason for this is because access will not allow me
sufficent
control over any single field record when using continous forms.

This all began when I had the requirement to allow a user to see a form
of
seemingly blank records (max 30) and hit a button to make them visible;
the
form is being used to help young people memorize certain lists of items
in
a
fixed order. I tried experimenting with conditional formatting which
worked
to a point, in that if you selected a 'blank' field, upon focus it
would
reveal its contents; the only flaw with this method was as soon as the
field
lost focus, it would dissapear; I need it to stay revealed, gradually
reveling a full list.

So, I figured the only way around this problem is to somehow get access
to
go through a table of master (groups of ) records and copy them into
the
(max) 30 fields in a new table; then hey presto!, I can have control
over
them. I want to beable to do all this in Access without user
intervention,
so
exporting to Excel, manual transposing, etc are not an option.

I hope someone can help me solve this problem.

:

Don't.

Access is not a spreadsheet.

What you described involves embedding data in field/columnnames.
You'd
pretty much have to do this if you were using a spreadsheet, but
Access
is a
relational database.

You've described "how" you want to solve some problem. If you'll
describe
the underlying problem/business need, folks here may be able to offer
more
specific suggestions.

If you MUST do what you've described, try exporting to Excel and using
the
Transpose function.

Regards

Jeff Boyce
Microsoft Office/Access MVP

How can I update a sequence of field's contents from one table to a
sequence
of seperate fields in another table.

For example

Master Table

[Job No] 211
[Field X] Apples
Oranges
Lemons

Table 2
[Job No] 211
Field 1: Apples
Field 2: Oranges
Field 3: Lemons


Each Job has a different number of records, but with a known maximum
of
30,
therefore I want to be able to take the field contents from 30
records
and
place each one in order into 30 seperate fields in the new table.

How can I do this with code?

 

[efandango]

Ok, picking up on your last point...

Have you any ideas on how to make a continuous form field visible/invisible
with a button. The user presses it, and it becomes visible, her presses
again, and the next record becomes visible, and so on?...

I'm not aware of a Transpose function in Access (perhaps for the reasons
discussed earlier).

Another approach, for you, not the user, would be to export it to Excel, use
Excel's Transpose function, then reimport into Access.

Perhaps one of the other newsgroup readers has experience with doing
something like this, or perhaps you and your users could reconsider how you
get this done...

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

perhaps I should have pointed out this is read-only data, just to be used
on
the basis of a sequentially revealing list. As I mentioned, I don't want
the
user having to deal with exports or spreadsheets. I was hoping to just
beable
to create the table ultimately from a make table query. It is not so much
a
question of whether it is a good match for the tool, more a question of
needs
must. I realise that the process is not a straight forward query & code,
which is why I am seeking help; can you assist?

You realize, right, that if ANYTHING changes about the number of
rows/fields
you are working with, you will have to rebuild tables, forms, queries,
reports, code, ... - a real maintenance nightmare.

Since it sounds like you have to have something that's like a
spreadsheet,
could you just export the data to Excel and use a spreadsheet? I ask
because what you are trying to do with Access doesn't sound like a good
match for the tool. Can it be done, probably ... easily?

Best of luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

(it all began with the limitations of Continous Forms...)

I understand your rationale about Access not being a spreadsheet, but I
have
a fundemental need to do it this way. The reason is because I want to
have
control over each 'seperate' field within the form, so that I can make
them
'visible' & 'Invisible' (and other stuff) depending on other actions.
The
underlying reason for this is because access will not allow me
sufficent
control over any single field record when using continous forms.

This all began when I had the requirement to allow a user to see a form
of
seemingly blank records (max 30) and hit a button to make them visible;
the
form is being used to help young people memorize certain lists of items
in
a
fixed order. I tried experimenting with conditional formatting which
worked
to a point, in that if you selected a 'blank' field, upon focus it
would
reveal its contents; the only flaw with this method was as soon as the
field
lost focus, it would dissapear; I need it to stay revealed, gradually
reveling a full list.

So, I figured the only way around this problem is to somehow get access
to
go through a table of master (groups of ) records and copy them into
the
(max) 30 fields in a new table; then hey presto!, I can have control
over
them. I want to beable to do all this in Access without user
intervention,
so
exporting to Excel, manual transposing, etc are not an option.

I hope someone can help me solve this problem.

:

Don't.

Access is not a spreadsheet.

What you described involves embedding data in field/columnnames.
You'd
pretty much have to do this if you were using a spreadsheet, but
Access
is a
relational database.

You've described "how" you want to solve some problem. If you'll
describe
the underlying problem/business need, folks here may be able to offer
more
specific suggestions.

If you MUST do what you've described, try exporting to Excel and using
the
Transpose function.

Regards

Jeff Boyce
Microsoft Office/Access MVP

How can I update a sequence of field's contents from one table to a
sequence
of seperate fields in another table.

For example

Master Table

[Job No] 211
[Field X] Apples
Oranges
Lemons

Table 2
[Job No] 211
Field 1: Apples
Field 2: Oranges
Field 3: Lemons


Each Job has a different number of records, but with a known maximum
of
30,
therefore I want to be able to take the field contents from 30
records
and
place each one in order into 30 seperate fields in the new table.

How can I do this with code?

 

[Jeff Boyce]

You can gain a great deal of control over the ... controls on a form if you
don't use the continuous form. Instead, consider building a single form
that is one "row" tall, without labels for your controls on that form. Now
embed that form (as a subform) in a main form and make the subform control
"taller" than one row. Multiple rows will display, and will look like a
continuous form, but it will be a single record on each "form".

Regards

Jeff Boyce
Microsoft Office/Access MVP

Ok, picking up on your last point...

Have you any ideas on how to make a continuous form field
visible/invisible
with a button. The user presses it, and it becomes visible, her presses
again, and the next record becomes visible, and so on?...

I'm not aware of a Transpose function in Access (perhaps for the reasons
discussed earlier).

Another approach, for you, not the user, would be to export it to Excel,
use
Excel's Transpose function, then reimport into Access.

Perhaps one of the other newsgroup readers has experience with doing
something like this, or perhaps you and your users could reconsider how
you
get this done...

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

perhaps I should have pointed out this is read-only data, just to be
used
on
the basis of a sequentially revealing list. As I mentioned, I don't
want
the
user having to deal with exports or spreadsheets. I was hoping to just
beable
to create the table ultimately from a make table query. It is not so
much
a
question of whether it is a good match for the tool, more a question of
needs
must. I realise that the process is not a straight forward query &
code,
which is why I am seeking help; can you assist?


:

You realize, right, that if ANYTHING changes about the number of
rows/fields
you are working with, you will have to rebuild tables, forms, queries,
reports, code, ... - a real maintenance nightmare.

Since it sounds like you have to have something that's like a
spreadsheet,
could you just export the data to Excel and use a spreadsheet? I ask
because what you are trying to do with Access doesn't sound like a
good
match for the tool. Can it be done, probably ... easily?

Best of luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

(it all began with the limitations of Continous Forms...)

I understand your rationale about Access not being a spreadsheet,
but I
have
a fundemental need to do it this way. The reason is because I want
to
have
control over each 'seperate' field within the form, so that I can
make
them
'visible' & 'Invisible' (and other stuff) depending on other
actions.
The
underlying reason for this is because access will not allow me
sufficent
control over any single field record when using continous forms.

This all began when I had the requirement to allow a user to see a
form
of
seemingly blank records (max 30) and hit a button to make them
visible;
the
form is being used to help young people memorize certain lists of
items
in
a
fixed order. I tried experimenting with conditional formatting which
worked
to a point, in that if you selected a 'blank' field, upon focus it
would
reveal its contents; the only flaw with this method was as soon as
the
field
lost focus, it would dissapear; I need it to stay revealed,
gradually
reveling a full list.

So, I figured the only way around this problem is to somehow get
access
to
go through a table of master (groups of ) records and copy them into
the
(max) 30 fields in a new table; then hey presto!, I can have control
over
them. I want to beable to do all this in Access without user
intervention,
so
exporting to Excel, manual transposing, etc are not an option.

I hope someone can help me solve this problem.

:

Don't.

Access is not a spreadsheet.

What you described involves embedding data in field/columnnames.
You'd
pretty much have to do this if you were using a spreadsheet, but
Access
is a
relational database.

You've described "how" you want to solve some problem. If you'll
describe
the underlying problem/business need, folks here may be able to
offer
more
specific suggestions.

If you MUST do what you've described, try exporting to Excel and
using
the
Transpose function.

Regards

Jeff Boyce
Microsoft Office/Access MVP

How can I update a sequence of field's contents from one table to
a
sequence
of seperate fields in another table.

For example

Master Table

[Job No] 211
[Field X] Apples
Oranges
Lemons

Table 2
[Job No] 211
Field 1: Apples
Field 2: Oranges
Field 3: Lemons


Each Job has a different number of records, but with a known
maximum
of
30,
therefore I want to be able to take the field contents from 30
records
and
place each one in order into 30 seperate fields in the new table.

How can I do this with code?