Combine data from multiple rows onto one row in separate columns

[Balbina]

My table looks like this:

Sample_# data1 data2 data3 data4 etc..
0000001 0.1 0.2
0000001 2.5
0000001 0.5
0000002 0.2 0.2
0000002 3.1
0000003 0.1 0.2

I receive data at different points in time and it thus I end up with
multiple sample_#'s and various data columns filled in. I would like to
combine all the data for each sample into one row.

I would like it to look like this:

Sample_# data1 data2 data3 data4 etc..
0000001 0.1 2.5 0.2 0.5
0000002 0.2 3.1 0.2
0000003 0.1 0.2

Any suggestions for automating amalgamating this data into the format I
would like?

Please help.
Thank you.

 

[John W. Vinson]

My table looks like this:

Sample_# data1 data2 data3 data4 etc..
0000001 0.1 0.2
0000001 2.5
0000001 0.5
0000002 0.2 0.2
0000002 3.1
0000003 0.1 0.2

I receive data at different points in time and it thus I end up with
multiple sample_#'s and various data columns filled in. I would like to
combine all the data for each sample into one row.

I would like it to look like this:

Sample_# data1 data2 data3 data4 etc..
0000001 0.1 2.5 0.2 0.5
0000002 0.2 3.1 0.2
0000003 0.1 0.2

Any suggestions for automating amalgamating this data into the format I
would like?

Please help.
Thank you.

Your data input process is clearly at fault here: it shouldn't be adding
multiple records per sample, it should be updating existing sample records if
they are there and only adding a new one if there isn't!

Will you ever have two different numbers for a given field for a given sample,
e.g.

Sample_# data1 data2 data3 data4 etc..
0000001 0.1 0.2
0000001 2.5
0000001 0.5
0000001 0.5

If so, what do you want to happen? Store 0.1, 0.5, 0.6? Generate an error
message?

If you will NEVER have this situation - each field will have only one non-null
value for all instances - you could create a second identically structured
table and fill it using an Append query:

INSERT INTO newtable
SELECT table.[Sample_#], Max([data1]) AS Data1, Max([data2]) AS Data2,
Max([data3]) AS data3, Max([data4]) AS Data4
FROM table
GROUP BY table.[Sample_#];

 

[Balbina]

My table looks like this:

Sample_# data1 data2 data3 data4 etc..
0000001 0.1 0.2
0000001 2.5
0000001 0.5
0000002 0.2 0.2
0000002 3.1
0000003 0.1 0.2

I receive data at different points in time and it thus I end up with
multiple sample_#'s and various data columns filled in. I would like to
combine all the data for each sample into one row.

I would like it to look like this:

Sample_# data1 data2 data3 data4 etc..
0000001 0.1 2.5 0.2 0.5
0000002 0.2 3.1 0.2
0000003 0.1 0.2

Any suggestions for automating amalgamating this data into the format I
would like?

Please help.
Thank you.

Your data input process is clearly at fault here: it shouldn't be adding
multiple records per sample, it should be updating existing sample records if
they are there and only adding a new one if there isn't!

Will you ever have two different numbers for a given field for a given sample,
e.g.

Sample_# data1 data2 data3 data4 etc..
0000001 0.1 0.2
0000001 2.5
0000001 0.5
0000001 0.5

If so, what do you want to happen? Store 0.1, 0.5, 0.6? Generate an error
message?

If you will NEVER have this situation - each field will have only one non-null
value for all instances - you could create a second identically structured
table and fill it using an Append query:

INSERT INTO newtable
SELECT table.[Sample_#], Max([data1]) AS Data1, Max([data2]) AS Data2,
Max([data3]) AS data3, Max([data4]) AS Data4
FROM table
GROUP BY table.[Sample_#];

Thank you. That works really well. It might happen that I would have more
than one value for a given field, but that would be an error. Any suggestions
for trapping that error?

 

[KARL DEWEY]

Try this --
INSERT INTO newtable
SELECT table.[Sample_#], IIF(Max([data1]) = Min([data1]), Max([data1]),
"Error")AS Data1, IIF(Max([data2]) = Min([data2]), Max([data2]), "Error") AS
Data2, IIF(Max([data3]) = Min([data3]), Max([data3]), "Error") AS Data3,
IIF(Max([data4]) = Min([data4]), Max([data4]), "Error")AS Data4
FROM table
GROUP BY table.[Sample_#];

--
Build a little, test a little.

My table looks like this:

Sample_# data1 data2 data3 data4 etc..
0000001 0.1 0.2
0000001 2.5
0000001 0.5
0000002 0.2 0.2
0000002 3.1
0000003 0.1 0.2

I receive data at different points in time and it thus I end up with
multiple sample_#'s and various data columns filled in. I would like to
combine all the data for each sample into one row.

I would like it to look like this:

Sample_# data1 data2 data3 data4 etc..
0000001 0.1 2.5 0.2 0.5
0000002 0.2 3.1 0.2
0000003 0.1 0.2

Any suggestions for automating amalgamating this data into the format I
would like?

Please help.
Thank you.

Your data input process is clearly at fault here: it shouldn't be adding
multiple records per sample, it should be updating existing sample records if
they are there and only adding a new one if there isn't!

Will you ever have two different numbers for a given field for a given sample,
e.g.

Sample_# data1 data2 data3 data4 etc..
0000001 0.1 0.2
0000001 2.5
0000001 0.5
0000001 0.5

If so, what do you want to happen? Store 0.1, 0.5, 0.6? Generate an error
message?

If you will NEVER have this situation - each field will have only one non-null
value for all instances - you could create a second identically structured
table and fill it using an Append query:

INSERT INTO newtable
SELECT table.[Sample_#], Max([data1]) AS Data1, Max([data2]) AS Data2,
Max([data3]) AS data3, Max([data4]) AS Data4
FROM table
GROUP BY table.[Sample_#];

Thank you. That works really well. It might happen that I would have more
than one value for a given field, but that would be an error. Any suggestions
for trapping that error?

 

[Balbina]

Thank you!

 

[John W. Vinson]

Try this --
INSERT INTO newtable
SELECT table.[Sample_#], IIF(Max([data1]) = Min([data1]), Max([data1]),
"Error")AS Data1, IIF(Max([data2]) = Min([data2]), Max([data2]), "Error") AS
Data2, IIF(Max([data3]) = Min([data3]), Max([data3]), "Error") AS Data3,
IIF(Max([data4]) = Min([data4]), Max([data4]), "Error")AS Data4
FROM table
GROUP BY table.[Sample_#];

Very nice Karl!!! Thanks.