Prev: Default value problem
Next: Unhide query
From: Balbina on 5 Jan 2010 21:18
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.
From: John W. Vinson on 6 Jan 2010 01:39
On Tue, 5 Jan 2010 18:18:01 -0800, Balbina <Balbina(a)discussions.microsoft.com>
wrote:

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

--

John W. Vinson [MVP]
 | 
Pages: 1
Prev: Default value problem
Next: Unhide query