Updating Information in New Column

[Lee24]

Hi All…..

I am in the process of developing a small database that will analyse all of our staff and manual labour. As you can imagine there are numerous job titles which I want to simplify somehow.

My initial idea was to use a make table query and manually change the data myself but this would take far too long so this is hopefully where you come in.

Is it possible to use a query to add an additional column to the table and update with relevant info?

Example would be –

Existing Column New Column

Grade 3 Operative Operative
Mechanical Fitter Advanced Mechanical Fitter

Any help much appreciated!

 

[Remou]

How many unique job titles do you have? The best way to go about this is, I think, to set up a Job Titles table:

tblJobTitles
JobTitleID - Primary Key
JobTitle
ShortTitle

The data would look like this:
[tt]JobTitleID JobTitle ShortTitle
1 Grade 3 Operative Operative
2 Mechanical Fitter Advanced Mechanical Fitter[/tt]

Your employee table would then look like this:
tblEmployees
EmployeeID - Primary Key
JobTitleID - Foreign Key
Other Fields

The data would look like this:
[tt]EmployeeID JobTitleID ...
51 1
22 2
77 1[/tt]

Which would mean that queries could show either the full title or the short title and that counts on all, say, operatives regardless of grade would be quite simple.

 

[Lee24]

Hi Remou,

Unfortunately I have got about 500ish different titiles, sounds alot but its not that bad.

Do you have any samples you got send me? As not 100% sure of what I need to do.

Thanks

L

 

[Remou]

What you need to do is create a test database then try this.
Create a query to select all Job Titles from the main table into a 'look-up' table, for example:

[tt]SELECT DISTINCT tblMain.JobTitle INTO tblJobTitles
FROM tblMain;[/tt]

Edit the design of tblJobTitles to add an ID and a short name column. Then either manually add short names or use queries, for example:

[tt]UPDATE tblJobTitles SET tblJobTitles.ShortTitle = "Operative"
WHERE (((tblJobTitles.JobTitle) Like "*Operative*"));[/tt]

Edit the main table to add a column JobTitleID, then create a query to update the main table, for example:

[tt]UPDATE tblMain INNER JOIN tblJobTitles ON tblMain.JobTitle = tblJobTitles.JobTitle SET tblMain.JobTitleID = [tblJobTitles].[JobTitleID];[/tt]

That is what I think.

 

[Lee24]

Ok, I have create a jobtitles table with the current title and also with short title.

What do I do next to join them up?

TRhanks

L

 

[Remou]

Use the query design screen.
One query might be:
SELECT tblMain.PersonName, tblJobTitles.ShortTitle
FROM tblMain INNER JOIN tblJobTitles ON tblMain.JobTitleID = tblJobTitles.JobTitleID;