Group Data/Copy Row Two Field to Row One Field

[develguy]

I am having trouble figuring this one out... Hopefully someone can
help!

I have a table ("tbl1")

It has five (5) fields:
1- "Item1"
2- "Item2"
3- "Item3"
4- "ScanImage" (stores scan path/filename)
5- "ID"

I can have an "order" with up to three Items. We scan the order and
create a "ScanImage" filename (row). In the past each order that had
multiple "Items" was "Cloned" and a duplicate "ScanImage" record was
created.

A sample of two orders with three items on each:

Item1 Item2 Item3 ScanImage ID
x1 scan1 1
y1 scan1 2
z1 scan1 3
a1 scan2 4
b1 scan2 5
c1 scan2 6

What I need to do is turn these six (6) records into two (2) by
populating the "Item2" and "Item3" fields using the "ScanImage" as the
unique id.

So the set should look like this:

Item1 Item2 Item3 ScanImage
x1 y1 z1 scan1
a1 b1 c1 scan2

I know this is not an ideal schema (far from it) but I am required to
report the data in this format. I am trying to do this after the fact
(last year's data which was not keyed in correctly).

There are over 3000 individual records that need this "cleansing".

Thanks to all in advance.

-Bob

[CDMAPoster]

de******@gmail.com wrote:

I am having trouble figuring this one out... Hopefully someone can
help!

I have a table ("tbl1")

It has five (5) fields:
1- "Item1"
2- "Item2"
3- "Item3"
4- "ScanImage" (stores scan path/filename)
5- "ID"

I can have an "order" with up to three Items. We scan the order and
create a "ScanImage" filename (row). In the past each order that had
multiple "Items" was "Cloned" and a duplicate "ScanImage" record was
created.

A sample of two orders with three items on each:

Item1 Item2 Item3 ScanImage ID
x1 scan1 1
y1 scan1 2
z1 scan1 3
a1 scan2 4
b1 scan2 5
c1 scan2 6

What I need to do is turn these six (6) records into two (2) by
populating the "Item2" and "Item3" fields using the "ScanImage" as the
unique id.

So the set should look like this:

Item1 Item2 Item3 ScanImage
x1 y1 z1 scan1
a1 b1 c1 scan2

I know this is not an ideal schema (far from it) but I am required to
report the data in this format. I am trying to do this after the fact
(last year's data which was not keyed in correctly).

There are over 3000 individual records that need this "cleansing".

Thanks to all in advance.

-Bob

Your schema disclaimer has been noted.

The first thing I tried was:

qryScanImage:
SELECT DISTINCT ScanImage AS theScanImage FROM tblClones;

qryItems:
SELECT (SELECT FIRST(A.Item1) FROM tblClones As A WHERE A.ScanImage =
qryScanImage.theScanImage) AS Item1, (SELECT FIRST(A.Item1) FROM
tblClones As A WHERE A.ScanImage = qryScanImage.theScanImage AND
A.Item1 <> (SELECT FIRST(B.Item1) FROM tblClones As B WHERE B.ScanImage
= qryScanImage.theScanImage)) AS Item2, (SELECT FIRST(A.Item1) FROM
tblClones As A WHERE A.ScanImage = qryScanImage.theScanImage AND
A.Item1 <> (SELECT FIRST(B.Item1) FROM tblClones As B WHERE B.ScanImage
= qryScanImage.theScanImage) AND A.Item1 <> (SELECT FIRST(B.Item1) FROM
tblClones As B WHERE B.ScanImage = qryScanImage.theScanImage AND
B.Item1 <> (SELECT FIRST(C.Item1) FROM tblClones As C WHERE C.ScanImage
= qryScanImage.theScanImage))) AS Item3, theScanImage AS ScanImage FROM
qryScanImage;

I didn't make any attempt to optimize this. The ID field will suggest
to Access to use that for a default order when viewing tblClones so
that's the order you should get for Item1, Item2 and Item3. Item2
finds the first item, if any, that is not equal to the first. Item3
finds the first item, if any, that is not equal to the first or to the
second. A more general solution might use a ranking within a ScanImage
based on ID. Post back if you need something better.

James A. Fortune
CD********@FortuneJames.com

[develguy]

Thank you James! That did the trick.

Bob