Combine data from multiple rows onto one row in separate columns


B

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.
 
Ad

Advertisements

J

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_#];
 
B

Balbina

John W. Vinson said:
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?
 
K

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.


Balbina said:
John W. Vinson said:
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?
 
Ad

Advertisements

J

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.
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top