Add column of second record to first record

[Vera]

Hi all,



I have a problem that I feel is not so difficult but I don't see it.

I have a table with records:
sample1; date1; reviewer1; result1
sample1; date1; reviewer2; result2
sample2, date2; reviewer1; result1
sample2; date2; reviewer2; result2
sample2; date2, reviewer3; result3
sample3; date3, reveiwer1, result1
sample4; date4, reviewer1, result1

What I would like to get as result:
sample1; date1; reviewer1; result1; reviewer2; result2
sample2; date2; reviewer1; result1; reviewer2; result2; reveiwer3; result3
sample3; date3; reviewer1; result1
sample4; date4; reveiwer1; result1

When the samplenumber is the same and the reviewer is different, the name of the reviewer and the result should come in an extra column.
I can have maximum 3 records per sample.

What I have so far is:
SELECT T1.sample, T1.Reviewer, T1.Result, T2.Reviewer, T2.Result
FROM Samples T1, Samples AS T2
WHERE (((T1.sample)=[T2].sample)
AND ((T1.Reviewer)<>[T2].Reviewer));

but that gives me too many records.

Any help would be appriciated.
Thanks.
Vera

[ajetrumpet]

I would guess that this would be much easier if you just took the plunge and wrote a function to create a new querydef in the database. You can let it run over and over by capturing an error if the def doesn't already exist. for instance:

dim qDef as querydef
dim db as dao.database
set db = currentdb

on error resume next 

db.querydefs.delete("your query") 'if already exists, eliminate old
set qDef = db.querydefs.add("your query")

'etc....

[oldman]

I will start by assuming that I really don't care if reviewer 1 reviewed the same record 5 times thus the date becomes meaningless for each record.
I would set up a table where I designated the sample# and reviewer ID as a unique combination by creating a combination field that holds a designation of both combined identities combined by using a concatenated field. (Combo: Sample# & Reviewer1 ID)
Now since you are only going to allow each sample to exist once, run an append query that looks for an unmatched Sample number and an unmatched combination number. If the Table has a KEY associated with the sample number, you will never see a duplicate. That takes care of the first reviewer being duplicated. Run another query that looks like the first one but places the result in the reviewer 2 column and does not allow a combo matching the result of the first query. Now you have the reviewer 1 and reviewer 2 columns populated. This query is followed by an update query to populate a concatenated combo 2 field that contains the concatenation of Sample # and Reviewer2 ID.
Finally run another update query that verifies that combo and combo 2 are not being duplicated and adds a record of reviewer 3 into the third reviewer column.
Each time that you add a reviewer, update the date based on the most recent reviewer, but only with the most recent reviewer. It will take 2 update queries to accomplish that. The first matches the addition of the first update where you added in the second reviewer and adds the date that matches that reviewer and that sample number. The second date update looks for a match between sample number and reviewer 3 and updates the date again. That means that the date carried forward in the review process will necessarily be the date of the reviewer 3 date for a completed review.

[Vera]

Thanks oldman, that did the trick.