Fill a column during an Append Query

[echorley]

A testing company has changed the format in which they send us our results. Before, every student would have ONE row with multiple testing information as the column headers. For example:

StudentId, TestName1, Test1Score, TestName2, TestScore2, etc

Now, we receive the data where students have multiple rows and each row contains a different, but only one test name and one test score. For example:

StudentID TestName1 TestScore1
StudentID TestName2 TestScore2
StudentID TestName3 TestScore3

I want to use an Append query to add the old data to the new. Using an Append query, I can get the names, scores and IDs in just fine, but the issue is the testing name. In the old version, the test names were the column header names. Now, they are part of the student records.

Long story short, can I create an expression during an Append query that will fill a column with the same value (the tesing name), but that value is NOT part of the records of the original table? Somewhat like an Update query.

 

[jzwp22]

Yes, you can have an expression with a constant value as part of an append query. Of course, you will have to only run the append query for one test name at a time.

 

[echorley]

How? Everything I have tried doesn't work! For example, if I want to fill the column with "ORF_Benchmark"?

 

[vbaInet]

Sounds to me you are trying to transpose your old data into the new format. The old format is not normalized and I'm suprised that the test company was sending that through. Unless it was in .csv format?

 

[jzwp22]

Could you provide the SQL text of the append query that failed? Were any errors returned when you tried to run the query?

 

[echorley]

Wow. Very simple: Expr1: "ORF_Benchmark" and then choose the field to append to. I swear I tried this exact technique the first time. Thanks for your replies and help!