|
| |
|
|
freeman24147
Posts: 8
Joined: 1/25/2008
Status: offline
 |
What's wrong with this code? - 1/25/2008 3:00:19
I am trying to use a normalized database method. I realized this is different from other attempts that have been successful, but I don't know why this does not work. Situation is this, I have a record with fields for Project_Manager, Program_Manager, and Project_Lead. All fields capture the Emp Id. I am using the follwoing sample code to pull back the Names of the employees. Works fine when I use only one field, say Program_Manager, but when I use Project_manager and Program_Manager fields, I get either blank fields, or with some tweaking, I get the same name in all fields. The code below results in the employee number displaying, but the Name field is blank:
SELECT Projects.Program_Name, Projects.Program_Manager, Projects.Project_Manager, Projects.Proj_ID, [FName] & " " & [Lname] AS [Prog Mgr], [FName] & " " & [Lname] AS [Proj Mgr]
FROM Projects LEFT JOIN Resource ON (Projects.Program_Manager = Resource.Emp_ID) AND (Projects.Project_Manager = Resource.Emp_ID);
I am creating this in Access, and then importing to FP2003. I think I need to specify something in the each specific type name field, but I don't know what.
Thanks in advance for any help.
Chris
|
|
|
|
rdouglass
Posts: 9228
From: Biddeford, ME USA
Status: offline
|
RE: What's wrong with this code? - 1/25/2008 8:12:05
Hi and Welcome to OutFront.
You would need to use the Resource table twice. Something like this comes to mind:
SELECT Projects.Program_Name, Projects.Program_Manager, Projects.Project_Manager, Projects.Proj_ID, Resource.[FName] & " " & Resource.[Lname] AS [Prog Mgr], Resource_1.[FName] & " " & Resource_1.[Lname] AS [Proj Mgr]
FROM Resource AS Resource_1 RIGHT JOIN (Resource RIGHT JOIN Projects ON Resource .Emp_ID = Projects.Program_Manager) ON Resource_1.Emp_ID = Projects.Project_Manager
I havent tested this exact syntax but essentially you use the Resource table twice; once with just "Resource" and the other with "Resource AS Resource_1".
That help any?
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
freeman24147
Posts: 8
Joined: 1/25/2008
Status: offline
|
RE: What's wrong with this code? - 1/27/2008 0:34:03
Roger,
No go on this one. Generates an error on Resource table identification. I think I'll just resort to multiple database results, one for each employee id
| |
|
|
|
rdouglass
Posts: 9228
From: Biddeford, ME USA
Status: offline
|
RE: What's wrong with this code? - 1/27/2008 1:16:50
quote:
No go on this one.
That's funny; it worked for me (or at least something pretty close to that). Maybe I don't fully understand the DB.
Sorry..
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
