Primary and Alternate project managers

  • Thread starter Thread starter MeSteve
  • Start date Start date
M

MeSteve

I am trying to set up my database so I can show primary and alternate project
managers. I have both as a columns in ProjectInfo as Integers linking to the
PK in ProjectManagers. I cannot retrieve names for both sets. So far I can
only retrieve the names for project manager and the Alt_PM that is stored as
an integer. I am fairly new to DBA so talk slow, Thanks.
 
Is the PK in ProjectManagers also an Integer? A common approach is to use
an Autonumber for a PK ... if that's what you've done, you need to use a
LongInteger as your foreign key.

Are you trying to use a "lookup" datatype directly in a table? If so,
reconsider. In Access, tables store data, forms display it. Use a form to
display the data, and use a combo box for each person, with a separate query
behind each combo box as a Row Source.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Yes, ProjectManager PK is Autonumber, All FK are long int's, I am trying to
make a query show me the F/L name of primary project manager, project title,
and FL name of alternate project manager. As soon as I try to associate the
alternate in any fashion, the query goes blank. Just showing primary Proj
Mgr I can get working no problems.
 
Post the SQL statement of your query. I'm guessing you are trying to join
as an inner join (show me all matching records). These will only show the
matches, so if you DON'T have any matches in the AlternatePM, you'd get
none!

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
I am trying to set up my database so I can show primary and alternate project
managers. I have both as a columns in ProjectInfo as Integers linking to the
PK in ProjectManagers. I cannot retrieve names for both sets. So far I can
only retrieve the names for project manager and the Alt_PM that is stored as
an integer. I am fairly new to DBA so talk slow, Thanks.
Click to expand...

try this using your names, but also refer to this for a better design.
http://allenbrowne.com/AppHuman.html

SELECT P.Project_id,
P.ProjectTitle,
E.EmployeeName AS [Project Manager],
E1.EmployeeName AS [Asst Manager]
FROM Employees AS E
RIGHT JOIN (Projects AS P
INNER JOIN Employees AS E1
ON P.AsstManager = E1.EmployeeNumber)
ON E.EmployeeNumber = P.Manager;
 
I haven't used SQL statements, just the built in query. Here's what I have
so far, hopefully it makes sense:

tbl_Projects
-ProjectID (auto) PK
-name (str)
-PM (long)
-AltPM (long)
tbl_ProjectManager
-contactID (long) FK
-projectID (long) FK
-ProjectMangerID (Auto) PK
tbl_Contacts
-ContactID (auto) PK
-FirstName (string)
-LastName (string)

I use query wizard to set up a query to show project name, F/L name project
manager and this works.
If I try to add anything that has AltPM anywhere and it goes blank
 
Where do I script a SQL statement? I think I would have better luck that way.

Michael Gramelspacher said:
I am trying to set up my database so I can show primary and alternate project
managers. I have both as a columns in ProjectInfo as Integers linking to the
PK in ProjectManagers. I cannot retrieve names for both sets. So far I can
only retrieve the names for project manager and the Alt_PM that is stored as
an integer. I am fairly new to DBA so talk slow, Thanks.
Click to expand...

try this using your names, but also refer to this for a better design.
http://allenbrowne.com/AppHuman.html

SELECT P.Project_id,
P.ProjectTitle,
E.EmployeeName AS [Project Manager],
E1.EmployeeName AS [Asst Manager]
FROM Employees AS E
RIGHT JOIN (Projects AS P
INNER JOIN Employees AS E1
ON P.AsstManager = E1.EmployeeNumber)
ON E.EmployeeNumber = P.Manager;
Click to expand...
 
Open the query in design view. Click on the view button, changing the view
to SQL view. Copy it. Post it back here.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
This is what is working (and doesn't include the Alt PM)

SELECT tbl_Projects.ProjectNameShort, Contacts.Contact_LastName,
Contacts.Contact_FirstName
FROM tbl_Projects INNER JOIN (Contacts INNER JOIN ProjectManagers ON
Contacts.ContactID = ProjectManagers.ContactID) ON tbl_Projects.ProjectID =
ProjectManagers.ProjectID;
 
Where do I script a SQL statement? I think I would have better luck that way.
Click to expand...

Copy the SQL script from the newsread window into the clipboard.

In the database window select Queries.

Then select New, then select Design View.

Close the Show Table window without selecting any tables.

Click SQL on left end of icon bar.

Paste the SQL script from the Clipboard into the window.

Click the icon on left end of the icon bar to run query.

Close the SQL window and save query.

Obviously any table names and column names in any SQL script offered in a
newsgroup message must agree with your actual table and column names, or the SQL
script will not work. It is the responsibility of the user to edit the script
if necessary. What I have attempted to describe above is most rudimentary.

Hopefully I understood what you were asking.
 
I think I am almost there, but after changing to fit my columns I get an error:
The SELECT statment includes a reserved word or an argument... , and
highlights FROM. Here is what I have so far:

SELECT tbl_Projects.ProjectID,
tbl_Projects.ProjectNameShort,
tbl_Contacts.LastName AS PM,
tbl_Contacts.LastName AS AltPM,
FROM tbl_Contacts
RIGHT JOIN (tbl_Projects
INNER JOIN tbl_Contacts
ON tbl_Projects.AltPM = tbl_Contacts.ContactID)
ON tbl_Contacts.ContactID = tbl_Projects.PM

I had to take out the abbreviations to get this far. I will tackle those at
a later date. Thanks for your help so far.
 
Back
Top