need to combine duplicate records in a table

[ldappa]

Hi,

I have a table "Table1" that has unique Id's and scores that are imported daily from a table that may have additional scores or changes in scores in the current table.

Exp: Table1
00000154 Bob Smith Score1 Score2 Score3 etc.

Table2

00000154 Bob smith Score1
00000154 Bob smith Score2

I need code that places the new scores into Table1 's individual record(no dups) in order to get placements.

I have tried union queries and Concatenate with no success.

Thanks,Lorna

[June7]

You want data from Table2 rearranged like Table1? Certainly not with UNION or concatenation. A CROSSTAB might accomplish. Need a unique ID field in Table2, autonumber type will serve. Like:

TRANSFORM First(Table2.Score) AS FirstOfScore
SELECT Table2.GID, Table2.PName
FROM Table2
GROUP BY Table2.GID, Table2.PName
PIVOT DCount("*","Table2","GID='" & [GID] & "' AND ID<" & [ID])+1;

Otherwise, use VBA manipulating recordsets to read and write data.

[ldappa]

I need to move the score from second dup record to first empty cell. This will be the same when running this table against the other which has one record for each student.

Student ID	Last Name	First Name	Test Date	Birth Date	Arithmetic	Elementary Algebra	College Level Math	Reading Comprehension	Sentence Skills	ESL Reading Skills	Writeplacer pilot
00142855	ayala	luis	3/26/2018	10/18/1970					98		4
00142855	ayala	luis	3/26/2018	10/18/1970				43

Table2(Importing from Table 1 )

SSN	LNAME	FNAME	TESTDATE	DOB	ARSCORE	EASCORE	CLM	RCSCORE	SSSCORE	LE	WPSCORE
142855	AYALA	LUIS	2/21/2018	10/18/1970	64	23	0	43	98	0	4

0

[June7]

You appear to have switched the table designations.

The source table is described differently now. Originally showed all scores in one field, now they are distributed in multiple fields.

Did you try an aggregate (GROUP BY) query? Use Max() function for each score field. Unfortunately, AFAIK an aggregate query cannot be used in an UPDATE sql action although does work in INSERT sql.

DMax() could be used in UPDATE sql.

[ldappa]

June7 it is exactly what I was looking for....took me a little time to pull it together but its perfect.

Thank you so much for your help