Query to combine columns....

[Fletcher]

Is there a query or command somehwere that will take, say, six columns
and combine them into one column, but keep associated data the same?

To give a simple expample, lets say I have a table with a DATE, TIME,
MEASUREMENT, and three columns called SAMPLE1, SAMPLE2, SAMPLE3.

I would like the three sample columns to combine to a single column but
keep the DATE, TIME, and MEASUREMENT that was associated with it in the
first place.

If anyone could help me with this, I would greatly appreciate it.
Thanks

Fletcher

 

[strive4peace]

Hi Fletcher,

make another field in your table and do an update query

1. make a query based on your table

2. first, we will select the info before we update it to make sure that
is what you want


field --> DATE_fieldname

field --> TIME_fieldname

field --> MEASUREMENT

field --> Sample: (Sample1 + " ") & (Sample2 + " ") & Sample3

2. if this is what you want, change it to an update query
from the menu --> Query, Update

3. add the field you will use to concatenate fields to your grid

field --> newFieldname
UpdateTo --> ([Sample1] + " ") & ([Sample2] + " ") & [Sample3]

make sure to put brackets around the fieldnames in the UpdateTo cell

then, Run ! your query to make the changes
~~~~~~~~~~~~~~~~~~~~`

DATE and TIME are reserved words, they should not be used for names


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*

 

[John Vinson]

Is there a query or command somehwere that will take, say, six columns
and combine them into one column, but keep associated data the same?

To give a simple expample, lets say I have a table with a DATE, TIME,
MEASUREMENT, and three columns called SAMPLE1, SAMPLE2, SAMPLE3.

I would like the three sample columns to combine to a single column but
keep the DATE, TIME, and MEASUREMENT that was associated with it in the
first place.

If anyone could help me with this, I would greatly appreciate it.
Thanks

I'm not absolutely clear what you want here. Do you want to take the
100 records in this table and generate 300 records with each different
SAMPLE value as a single field? If so, a "Normalizing Union Query"
would be the way to go. You'll need to go into SQL view to do it, you
can't do it in the grid. In your example:

SELECT [DATE], [TIME], MEASUREMENT, [SAMPLE1] AS SAMPLE
FROM yourtable
WHERE [SAMPLE1] IS NOT NULL
UNION ALL
SELECT [DATE], [TIME], MEASUREMENT, [SAMPLE2]
FROM yourtable
WHERE [SAMPLE2] IS NOT NULL
UNION ALL
SELECT [DATE], [TIME], MEASUREMENT, [SAMPLE3]
FROM yourtable
WHERE [SAMPLE3] IS NOT NULL;

Note a couple of things: DATE and TIME are reserved words, for the
built-in date and time functions, and are not good choices of
fieldnames; and if you're storing the date in one field and the time
in another, you may want to reconsider; you can store both in a single
Date/Time field and find it easier to sort and search.

John W. Vinson[MVP]

 

[strive4peace]

good point, John... I wondered about normalization myself...

Fletcher, the answer I provided would combine all the values into one
field on the same record -- that is probably NOT what you want to
do...combining seperate data is never a good idea because you can always
combine it for reporting...

To build on what John said, you can define another table

*Measurements*
MeasID, autonumber
MeasDate, date
Measurement, number
(if it is not a whole number, make sure to change the size to something
appropriate like single or double)
NOTE: if Measurement is calculated, it should not be stored

I left out TIME since date and time can (and should) be stored together

apparently, you have 3 samples (or more or less) that the measurement is
based upon... which leads me to a question: is Measurement the average
of your Samples? If so, it should not be stored as it can be calculated
anytime. If Sample1, Sample2, Sample3 are sample numbers, or some other
identifier, then it is ok to be storing Measurement

You will now have a RELATED table with the sample info:

*Samples*
SampleID, autonumber
MeasID, Long Integer -- corresponds to MeasID in Measurements
Sample -- don't know what your data type is...

John gave you the SQL to combine all the values, but not to actually
transfer that to another table...

In order to help you further, we need to know exactly what Measurement
and Sample1, Sample2, Sample 3 actually are... and this will affect. of
course, the table structure I gave you


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*

Is there a query or command somehwere that will take, say, six columns
and combine them into one column, but keep associated data the same?

To give a simple expample, lets say I have a table with a DATE, TIME,
MEASUREMENT, and three columns called SAMPLE1, SAMPLE2, SAMPLE3.

I would like the three sample columns to combine to a single column but
keep the DATE, TIME, and MEASUREMENT that was associated with it in the
first place.

If anyone could help me with this, I would greatly appreciate it.
Thanks

I'm not absolutely clear what you want here. Do you want to take the
100 records in this table and generate 300 records with each different
SAMPLE value as a single field? If so, a "Normalizing Union Query"
would be the way to go. You'll need to go into SQL view to do it, you
can't do it in the grid. In your example:

SELECT [DATE], [TIME], MEASUREMENT, [SAMPLE1] AS SAMPLE
FROM yourtable
WHERE [SAMPLE1] IS NOT NULL
UNION ALL
SELECT [DATE], [TIME], MEASUREMENT, [SAMPLE2]
FROM yourtable
WHERE [SAMPLE2] IS NOT NULL
UNION ALL
SELECT [DATE], [TIME], MEASUREMENT, [SAMPLE3]
FROM yourtable
WHERE [SAMPLE3] IS NOT NULL;

Note a couple of things: DATE and TIME are reserved words, for the
built-in date and time functions, and are not good choices of
fieldnames; and if you're storing the date in one field and the time
in another, you may want to reconsider; you can store both in a single
Date/Time field and find it easier to sort and search.

John W. Vinson[MVP]

 

[Fletcher]

Okay, I guess I should clarify on a couple of points. So here goes:

Absolutely right.

apparently, you have 3 samples (or more or less) that the measurement is
based upon... which leads me to a question: is Measurement the average
of your Samples? If so, it should not be stored as it can be calculated
anytime. If Sample1, Sample2, Sample3 are sample numbers, or some other
identifier, then it is ok to be storing Measurement

The three samples are really called "lots" in our facility and the
machine that we're talking about can have multiple lots run through it
at the same time. The measurement is a measurement on a "dummy"
product in one of the lots and is assumed to be constant across the
lots.

Actually the column names are DateIn and TimeOut, I just put date and
time for quickness when I first posted the question. The DateIn is of
course the date that the lots went into the machine and the TimeOut is
even easier, the time that the lots came out of the machine. Since the
machines run timed processes, we always know when it is that it goes in
and out. I haven't used the Date() or Time() at all because the
operators enter this data by hand and it would be inconvenient to make
them delete the current date and time on the form to write new ones.

I realize that storing date and time in separate columns is probably
not the best idea, but it is how this facility has been reporting for
20 years and most employees have been here for at least 10 and get
frustrated when change occurs, so for now we're just trying to phase
out the paper logging for these certain machines. To do this we've
recreated the "log sheet" on a computer screen to make the operators
more confortable with it. I'm sorry if this is bad form, but it's a
temporary thing until the operators can adjust. Thanks for your
understanding.

And to further clarify on my desire to combine these fields to one
(make 100 records 300) is to be able to pull out a certain sample with
it's DateIn, TimeOut, and Measurement.

Thanks for all your help.

good point, John... I wondered about normalization myself...

Fletcher, the answer I provided would combine all the values into one
field on the same record -- that is probably NOT what you want to
do...combining seperate data is never a good idea because you can always
combine it for reporting...

To build on what John said, you can define another table

*Measurements*
MeasID, autonumber
MeasDate, date
Measurement, number
(if it is not a whole number, make sure to change the size to something
appropriate like single or double)
NOTE: if Measurement is calculated, it should not be stored

I left out TIME since date and time can (and should) be stored together

apparently, you have 3 samples (or more or less) that the measurement is
based upon... which leads me to a question: is Measurement the average
of your Samples? If so, it should not be stored as it can be calculated
anytime. If Sample1, Sample2, Sample3 are sample numbers, or some other
identifier, then it is ok to be storing Measurement

You will now have a RELATED table with the sample info:

*Samples*
SampleID, autonumber
MeasID, Long Integer -- corresponds to MeasID in Measurements
Sample -- don't know what your data type is...

John gave you the SQL to combine all the values, but not to actually
transfer that to another table...

In order to help you further, we need to know exactly what Measurement
and Sample1, Sample2, Sample 3 actually are... and this will affect. of
course, the table structure I gave you


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*

Is there a query or command somehwere that will take, say, six columns
and combine them into one column, but keep associated data the same?

To give a simple expample, lets say I have a table with a DATE, TIME,
MEASUREMENT, and three columns called SAMPLE1, SAMPLE2, SAMPLE3.

I would like the three sample columns to combine to a single column but
keep the DATE, TIME, and MEASUREMENT that was associated with it in the
first place.

If anyone could help me with this, I would greatly appreciate it.
Thanks

I'm not absolutely clear what you want here. Do you want to take the
100 records in this table and generate 300 records with each different
SAMPLE value as a single field? If so, a "Normalizing Union Query"
would be the way to go. You'll need to go into SQL view to do it, you
can't do it in the grid. In your example:

SELECT [DATE], [TIME], MEASUREMENT, [SAMPLE1] AS SAMPLE
FROM yourtable
WHERE [SAMPLE1] IS NOT NULL
UNION ALL
SELECT [DATE], [TIME], MEASUREMENT, [SAMPLE2]
FROM yourtable
WHERE [SAMPLE2] IS NOT NULL
UNION ALL
SELECT [DATE], [TIME], MEASUREMENT, [SAMPLE3]
FROM yourtable
WHERE [SAMPLE3] IS NOT NULL;

Note a couple of things: DATE and TIME are reserved words, for the
built-in date and time functions, and are not good choices of
fieldnames; and if you're storing the date in one field and the time
in another, you may want to reconsider; you can store both in a single
Date/Time field and find it easier to sort and search.

John W. Vinson[MVP]

 

[Fletcher]

*Update: I talked to my boss and he decided that it was okay to give
the operators a slight shock and we will be combining the date and time
into a date/timeout field with Now() as the default value to encourage
the operators to log out the data immediately after running the
process. So you can disregard my comments about date and time and the
necessity to have them separate. Sorry if I've frustrated you.

Fletcher

Okay, I guess I should clarify on a couple of points. So here goes:

Absolutely right.

apparently, you have 3 samples (or more or less) that the measurement is
based upon... which leads me to a question: is Measurement the average
of your Samples? If so, it should not be stored as it can be calculated
anytime. If Sample1, Sample2, Sample3 are sample numbers, or some other
identifier, then it is ok to be storing Measurement

The three samples are really called "lots" in our facility and the
machine that we're talking about can have multiple lots run through it
at the same time. The measurement is a measurement on a "dummy"
product in one of the lots and is assumed to be constant across the
lots.

Actually the column names are DateIn and TimeOut, I just put date and
time for quickness when I first posted the question. The DateIn is of
course the date that the lots went into the machine and the TimeOut is
even easier, the time that the lots came out of the machine. Since the
machines run timed processes, we always know when it is that it goes in
and out. I haven't used the Date() or Time() at all because the
operators enter this data by hand and it would be inconvenient to make
them delete the current date and time on the form to write new ones.

I realize that storing date and time in separate columns is probably
not the best idea, but it is how this facility has been reporting for
20 years and most employees have been here for at least 10 and get
frustrated when change occurs, so for now we're just trying to phase
out the paper logging for these certain machines. To do this we've
recreated the "log sheet" on a computer screen to make the operators
more confortable with it. I'm sorry if this is bad form, but it's a
temporary thing until the operators can adjust. Thanks for your
understanding.

And to further clarify on my desire to combine these fields to one
(make 100 records 300) is to be able to pull out a certain sample with
it's DateIn, TimeOut, and Measurement.

Thanks for all your help.

good point, John... I wondered about normalization myself...

Fletcher, the answer I provided would combine all the values into one
field on the same record -- that is probably NOT what you want to
do...combining seperate data is never a good idea because you can always
combine it for reporting...

To build on what John said, you can define another table

*Measurements*
MeasID, autonumber
MeasDate, date
Measurement, number
(if it is not a whole number, make sure to change the size to something
appropriate like single or double)
NOTE: if Measurement is calculated, it should not be stored

I left out TIME since date and time can (and should) be stored together

apparently, you have 3 samples (or more or less) that the measurement is
based upon... which leads me to a question: is Measurement the average
of your Samples? If so, it should not be stored as it can be calculated
anytime. If Sample1, Sample2, Sample3 are sample numbers, or some other
identifier, then it is ok to be storing Measurement

You will now have a RELATED table with the sample info:

*Samples*
SampleID, autonumber
MeasID, Long Integer -- corresponds to MeasID in Measurements
Sample -- don't know what your data type is...

John gave you the SQL to combine all the values, but not to actually
transfer that to another table...

In order to help you further, we need to know exactly what Measurement
and Sample1, Sample2, Sample 3 actually are... and this will affect. of
course, the table structure I gave you


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*

Is there a query or command somehwere that will take, say, six columns
and combine them into one column, but keep associated data the same?

To give a simple expample, lets say I have a table with a DATE, TIME,
MEASUREMENT, and three columns called SAMPLE1, SAMPLE2, SAMPLE3.

I would like the three sample columns to combine to a single column but
keep the DATE, TIME, and MEASUREMENT that was associated with it in the
first place.

If anyone could help me with this, I would greatly appreciate it.
Thanks

I'm not absolutely clear what you want here. Do you want to take the
100 records in this table and generate 300 records with each different
SAMPLE value as a single field? If so, a "Normalizing Union Query"
would be the way to go. You'll need to go into SQL view to do it, you
can't do it in the grid. In your example:

SELECT [DATE], [TIME], MEASUREMENT, [SAMPLE1] AS SAMPLE
FROM yourtable
WHERE [SAMPLE1] IS NOT NULL
UNION ALL
SELECT [DATE], [TIME], MEASUREMENT, [SAMPLE2]
FROM yourtable
WHERE [SAMPLE2] IS NOT NULL
UNION ALL
SELECT [DATE], [TIME], MEASUREMENT, [SAMPLE3]
FROM yourtable
WHERE [SAMPLE3] IS NOT NULL;

Note a couple of things: DATE and TIME are reserved words, for the
built-in date and time functions, and are not good choices of
fieldnames; and if you're storing the date in one field and the time
in another, you may want to reconsider; you can store both in a single
Date/Time field and find it easier to sort and search.

John W. Vinson[MVP]

 

[strive4peace]

Hi Fletcher,

Have you set up tables to hold the data you wish to transfer? If so,
what are the tablenames and fieldnames and data types?

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*

*Update: I talked to my boss and he decided that it was okay to give
the operators a slight shock and we will be combining the date and time
into a date/timeout field with Now() as the default value to encourage
the operators to log out the data immediately after running the
process. So you can disregard my comments about date and time and the
necessity to have them separate. Sorry if I've frustrated you.

Fletcher

Okay, I guess I should clarify on a couple of points. So here goes:

I'm not absolutely clear what you want here. Do you want to take the
100 records in this table and generate 300 records with each different
SAMPLE value as a single field?

Absolutely right.

apparently, you have 3 samples (or more or less) that the measurement is
based upon... which leads me to a question: is Measurement the average
of your Samples? If so, it should not be stored as it can be calculated
anytime. If Sample1, Sample2, Sample3 are sample numbers, or some other
identifier, then it is ok to be storing Measurement

The three samples are really called "lots" in our facility and the
machine that we're talking about can have multiple lots run through it
at the same time. The measurement is a measurement on a "dummy"
product in one of the lots and is assumed to be constant across the
lots.

Note a couple of things: DATE and TIME are reserved words, for the
built-in date and time functions, and are not good choices of
fieldnames; and if you're storing the date in one field and the time
in another, you may want to reconsider; you can store both in a single
Date/Time field and find it easier to sort and search.

Actually the column names are DateIn and TimeOut, I just put date and
time for quickness when I first posted the question. The DateIn is of
course the date that the lots went into the machine and the TimeOut is
even easier, the time that the lots came out of the machine. Since the
machines run timed processes, we always know when it is that it goes in
and out. I haven't used the Date() or Time() at all because the
operators enter this data by hand and it would be inconvenient to make
them delete the current date and time on the form to write new ones.

I realize that storing date and time in separate columns is probably
not the best idea, but it is how this facility has been reporting for
20 years and most employees have been here for at least 10 and get
frustrated when change occurs, so for now we're just trying to phase
out the paper logging for these certain machines. To do this we've
recreated the "log sheet" on a computer screen to make the operators
more confortable with it. I'm sorry if this is bad form, but it's a
temporary thing until the operators can adjust. Thanks for your
understanding.

And to further clarify on my desire to combine these fields to one
(make 100 records 300) is to be able to pull out a certain sample with
it's DateIn, TimeOut, and Measurement.

Thanks for all your help.

good point, John... I wondered about normalization myself...

Fletcher, the answer I provided would combine all the values into one
field on the same record -- that is probably NOT what you want to
do...combining seperate data is never a good idea because you can always
combine it for reporting...

To build on what John said, you can define another table

*Measurements*
MeasID, autonumber
MeasDate, date
Measurement, number
(if it is not a whole number, make sure to change the size to something
appropriate like single or double)
NOTE: if Measurement is calculated, it should not be stored

I left out TIME since date and time can (and should) be stored together

apparently, you have 3 samples (or more or less) that the measurement is
based upon... which leads me to a question: is Measurement the average
of your Samples? If so, it should not be stored as it can be calculated
anytime. If Sample1, Sample2, Sample3 are sample numbers, or some other
identifier, then it is ok to be storing Measurement

You will now have a RELATED table with the sample info:

*Samples*
SampleID, autonumber
MeasID, Long Integer -- corresponds to MeasID in Measurements
Sample -- don't know what your data type is...

John gave you the SQL to combine all the values, but not to actually
transfer that to another table...

In order to help you further, we need to know exactly what Measurement
and Sample1, Sample2, Sample 3 actually are... and this will affect. of
course, the table structure I gave you


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*



John Vinson wrote:

Is there a query or command somehwere that will take, say, six columns
and combine them into one column, but keep associated data the same?

To give a simple expample, lets say I have a table with a DATE, TIME,
MEASUREMENT, and three columns called SAMPLE1, SAMPLE2, SAMPLE3.

I would like the three sample columns to combine to a single column but
keep the DATE, TIME, and MEASUREMENT that was associated with it in the
first place.

If anyone could help me with this, I would greatly appreciate it.
Thanks
I'm not absolutely clear what you want here. Do you want to take the
100 records in this table and generate 300 records with each different
SAMPLE value as a single field? If so, a "Normalizing Union Query"
would be the way to go. You'll need to go into SQL view to do it, you
can't do it in the grid. In your example:

SELECT [DATE], [TIME], MEASUREMENT, [SAMPLE1] AS SAMPLE
FROM yourtable
WHERE [SAMPLE1] IS NOT NULL
UNION ALL
SELECT [DATE], [TIME], MEASUREMENT, [SAMPLE2]
FROM yourtable
WHERE [SAMPLE2] IS NOT NULL
UNION ALL
SELECT [DATE], [TIME], MEASUREMENT, [SAMPLE3]
FROM yourtable
WHERE [SAMPLE3] IS NOT NULL;

Note a couple of things: DATE and TIME are reserved words, for the
built-in date and time functions, and are not good choices of
fieldnames; and if you're storing the date in one field and the time
in another, you may want to reconsider; you can store both in a single
Date/Time field and find it easier to sort and search.

John W. Vinson[MVP]