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.

On Tue, 5 Jan 2010 18:18:01 -0800, Balbina <(E-Mail Removed)>
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]

"John W. Vinson" wrote:

> On Tue, 5 Jan 2010 18:18:01 -0800, Balbina <(E-Mail Removed)>
> 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]
> .

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?

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" wrote:

>
>
> "John W. Vinson" wrote:
>
> > On Tue, 5 Jan 2010 18:18:01 -0800, Balbina <(E-Mail Removed)>
> > 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]
> > .
>
> 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?

Thank you!

On Wed, 6 Jan 2010 14:13:03 -0800, KARL DEWEY
<(E-Mail Removed)> wrote:

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

John W. Vinson [MVP]