Amending a Field

[cgolds]

I need help figuring out how to add a prefix to an existing description in a project table, generated from the project number.
Here's a sample of my data:

PROJECT NUMBER DESCRIPTION
01200000 Completed Projects
01601530 Steele Sub

I would like the Descriptions to read:
01200000 MO-20 Completed Projects
01601530 MO-60 Steele Sub

The state, “MO,” comes from the first two digits “01” and the coop, “60,” comes from the second two digits. I have a table of a thousand or so projects in an Access database that I need to amend the descriptions of to include these prefixes. Is there a simple way to do this in Access or in Excel without writing code?

[alansidman]

Build another table with a list of the State ID (first two numbers) and second field with State Name. In your query, create a new field "StateID:Left(Project,2)". This will give you the state code. Save this query. Create a new query and make the first query the source. Join the new table to the old query on the StateID. Create a new field "ProjectID: [NewTable.State Name] &"-"& Mid([Project],3,2)". Run the query. It should give you the new Project ID.

Alan