Turn Numbers Into Words. . ..Sorta

[greysquirl]

I am using an SQL statement to return some values to a table for me. What I have is a Master table where the info is coming from and going to a table called Position Change. This happens on the Before Update Event of a command button. All of that aside, the problem I am having is that if you look down here you will see the TitleID. . .StatusID. . .DepartmentID. . .etc. When this query runs and returns those values to the Position Change table it gives me the corresponding auto number of the column. . . .what I want it the word that matches this number to appear. Does anyone understand this??? If you do please help me. Thanks.

INSERT INTO [Position Change] ( FName, MName, LName, SSN, Title, Status, Department, Supervisor, DateHirePosition )
SELECT Master.FName, Master.MName, Master.LName, Master.SSN, Master.TitleID, Master.StatusID, Master.DepartmentID, Master.SupervisorID, Master.DateHirePosition
FROM Master
WHERE Master.MasterID = Forms!Master!MasterID;

 

[KHerm]

In your [Position Change] table do you want the text "TitleID" to appear in the Master.Title column?

If so, replace Master.TitleID in your Select section with 'TitleID' and so on down the line of each column name.

If not, can you try to explain again?

Ken

 

[KHerm]

Let me try my response again:

In your [Position Change] table do you want the text "TitleID" to appear in the [Position Change].[Title] column?

If so, replace Master.TitleID in your Select section with 'TitleID' and so on down the line of each column name.

If not, can you try to explain again?

Ken

 

[jflachman]

You need to use an INNER JOIN.

Let me see if I can get this right...
assuming you have tables called:[tt]
tDept(DepartmentID,Department)
tTitle(TitleID,Title)[/tt]
etc...

Your SELECT statement should read:[tt]
SELECT Master.Fname, ...,Master.SSN, tTitle.Title,
tDept.Department,...
FROM (tDepartment INNER JOIN (tTitle INNER JOIN Master
ON tTitle.TitleID = Master.TitleID) ON
tDept.DepartmentID = Master.DepartmentID)[/tt]
WHERE ...

Obviously, when you add status and supervisor, your nesting gets deeper, but you get the point.
The easiest way to get the SQL statement is build a query graphically. Then under the VIEW menu, select SQL VIEW. Then cut the SELECT... statement and stick it in your code. Jonathan
________________________________________
It is not fair to ask of others what you are unwilling to do yourself.
-Eleanor Roosevelt

 

[MichaelRed]

" ..what I want it the word that matches this number to appear. Does anyone understand this??? ..."

So where the Number = `, you want to 'see' "ONE"?

If so, you need to add a col to the query (NumberText?) and have a function whiich translated the Value to the Word(s). Thankfully there are a few faq, which will do the 'translation'. Just search the faqs.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over