SQL - Question - How to use If Statement

[0110]

I have a problem, my company created a database that lists employees and what office they are from. Well they used an OfficeID number. How do I convert that number to state which office they are from.

Example
If OffID = 1 then I want it to print "Celina"

Is there a way to do this in SQL. I don't know SQL very well, I've been using Dreamweaver to create database driven websites and thats all I know. But if someone can send me the code for this, I'm sure I can figure out what to enter.

Thank you
Carrie

 

[jdegeorge]

Is STATE a field in the table that stores the OfficeID?

Hope this helps!

Jim DeGeorge
Developer

 

[JoyInOK]

'Print' where? Are you saying you want to replace the OfficeID value with the state? Or do you want to have the result another field?
If it's one of these, you need an update query:

UPDATE [NameOfTable] SET [NameOfTable].[NameOfFieldToUpdate] = "Celina"
WHERE ((([NameOfTable].[NameOfFieldContainingStateName])="1&quot);

This assumes the OfficeID field is a text field. If it is a number field, lose the quotes surrounding the 1.

 

[JoyInOK]

Whoops! Change NameOfFieldContianingStateName to NameOfFieldContianingOfficeID

 

[0110]

Will this update the database?
I don't want to change the database, I'm trying to create a database driven website that will show the employees Name, Title, Extension, and where they are located. For some reason our company uses numbers to label our offices, I want to be able to change that number to appear as the "City" on the Web page, but I don't want to actutally change the database.

Carrie Web Developer 3133 3
(I want to look like this)
Carrie Web Developer 3133 Indianapolis

Thanks for your help!
Carrie

 

[jdegeorge]

0110

Do you have a table that stores the city values for the numbers, such as tblCity, with 2 fields, CityID# (the number) and CityDesc (the text)?

If so, create a query using the 2 tables joining on the common field in both (CityID#), but display CityDesc from tblCity. This will work and wont' replace anything. It's the beauty part of normalized tables. Hope this helps.

Hope this helps!

Jim DeGeorge
Developer

 

[jdegeorge]

Forgot to add, then you won't need the IF statment and you can base your report or form on the new query.

Hope this helps!

Jim DeGeorge
Developer

 

[0110]

That is a good idea, but I wouldn't know how to do that, but even if I could, the database does have a table that named Offices and fields OFFID and OfficeName, but I need the field LocID, which is not part of that table.

How do you do the IF statements?

Carrie

 

[jdegeorge]

0110

Post your table names and field names, and a few lines of data for each. I'll help you out with that.

Hope this helps!

Jim DeGeorge
Developer

 

[0110]

I really appreciate all that you are trying to do!
This database is huge, too many tables to list, so here are the ones that I use.
Again, I'm a beginner at this so I hope this is what you meant for me to post.

SELECT FirstName, LastName, PX1, JobTitle, OffID, LocID
FROM dbo.Employees
WHERE FirstName Like'%r_employees%'

I need it to post LocID and have it changed to "text"

Table: Employees
Data: FirstName
LastName
JobTitle
OffID
LocID (the reason for differnet ID is because
some employees work at different offices
so they created another field LocID,
not sure why they just didn't put the
corrected location in as text.)
Table: Offices
Data: OffID
OfficeName

Carrie

 

[jdegeorge]

Carrie

I created the 2 tables and entered 3 employees. I assumed that OffID and LocID were the same...you didn't say.

Employees
FirstName LastName JobTitle OffID LocID
Peter Jones Manager 3 3
John Smith Supervisor 1 1
Mary Doe Secretary 3 3

Offices
OffID OfficeName
3 Boston
1 New York

I then created this query in SQL mode:

SELECT Employees.FirstName, Employees.LastName, Employees.JobTitle, Employees.OffID, Offices.OfficeName
FROM Employees INNER JOIN Offices ON Employees.OffID = Offices.OffID;

Take a look at this is DESIGN mode to see it more clearly.

The results are:

FirstName LastName JobTitle OfficeName
Peter Jones Manager Boston
John Smith Supervisor New York
Mary Doe Secretary Boston

If OffID and LocID are different, the query would be a little different. Hope this helps.

Hope this helps!

Jim DeGeorge
Developer

 

[0110]

Thanks Jim,
But the OffID is different than LocID.
What do I need to do now?

You are a great help
Thanks again.

 

[jdegeorge]

Carrie

Would linking Employees!LocID to Offices.OfficeName work for you?

Please type in some values for your Employees tabel as I did, and then type in the expected results of your query, as I did. It's the best way for me to help at this point.

Hope this helps!

Jim DeGeorge
Developer

 

[grnzbra]

If the person does NOT work at a different location, would OffID and LocID be the same? If this is the case (ie the LocID and the OffID would be the same for the same office or location), create your query with the office table brought in twice so that you have a 3 table query, Employees, Offices, Offices_1 (or some name like that for the second occurrances of the offices table. So the Employees table would be joined to the Offices table by Employees.OffID = Offices.OffID and to the Offices_1 table by Employees.LocID = Offices.OffID. You would select the office name from each office table and give the one from Office_1 table the alias Location. If you use the query grid, it should be very simple.

 

[0110]

OffID and LocID is not always the same, majority of the time it is, But out off 300 employees, about 10 work in a differnt location.


Here is the example
Employees
FirstName LastName JobTitle OffID LocID
Peter Jones Manager 3 5
John Smith Supervisor 1 1
Mary Doe Secretary 3 3

Offices
OffID OfficeName
3 Boston
1 New York
5 Celina

FirstName LastName JobTitle OfficeName
Peter Jones Manager Celina
John Smith Supervisor New York
Mary Doe Secretary Boston

 

[grnzbra]

OK, so the 5 - Celina location appears in the offices table. All you have to do is bring the Offices table in a second time and link to the employees table on LocID = OffID
You could bring in either the office names from both tables as in your first query (so you would have names instead of id numbers). If you want to have just the one location show up, you could use an immediate if statement:

IIf([Employees].[OffID] = [Employees].[LocID], [Offices].[OfficeName],[Offices_1].[OfficeName])AS Office Name.

You would still need the Offices table twice. I think the easist way to do this is in the query grid. If you absloutely MUST have the SQL statement for it, use the query grid and then select SQL and copy and past it.

 

[jdegeorge]

Carrie

Sorry, I was out on Friday. GRNZBRA is correct. Since the values in Offices can be used for both OffID and LocID, link the table twice.

Hope this helps!

Jim DeGeorge
Developer