Need help for updating a field

[2624]

I have been trying to automate my update query. I have a database that imports data from excel file. One of the field captures test scores and they are in a text format. I would like to update these values into a numeric values in a new field. Like:


TestScore TestScoreNumeric (NEW Field with numeric values)
SUPR 10
ADVH 9
ADVM 8
ADVL 7
INTH 6
INTM 5
INTL 4

How can I update TestScoreNumeric field each time I import new sets of data?

Thanks for any help...

 

[PHV]

Create a lookup table with a TestScore field as PrimaryKey and a corresponding TestScoreNumeric field.
Then you can join this table in your update query.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[lespaul]

you need a table that has that information in it:

[tt]
tblNumericScores
TestScore NumericValue
SUPR 10
ADVH 9
ADVM 8
ADVL 7
INTH 6
INTM 5
INTL 4 [/tt]

then you just need to join that in a query....

SELECT * FROM OriginalTable
INNER JOIN tblNumericScores ON OriginalTable.TestScoreField = tblNumericScores.TestScore

now you can get the number value into your query without having to do anything!

HTH

Leslie

Leslie

Have you met Hardy Heron?

 

[2624]

Thank you so much lespaul. I have created a new table and an update query;

CODE:

UPDATE LANGUAGESCORES LEFT JOIN tblnumericscores ON LANGUAGESCORES.[Test Score] = tblnumericscores.TestScore SET LANGUAGESCORES.TESTSCORENumeric = [TBLNUMERICSCORES].[NUMERICVALUE];

It works fine. Thanks again.