Gladimir Posted September 10, 2003 Posted September 10, 2003 I am listing various information about all instances of Adobe Photoshop installed in our network. My current query produces information about 816 unique instances of Adobe Photoshop, including the last user id logged on to the computer when available. However, when I try to list the User Name associated with that user id, all records with no user id are dropped, pairing my listing down to 762 records. The last user id is retrieved from the LastUserID field in the REF_CSDComputers table. I'm attempting to pull the user name form the Username field in the REF_CSDUsers table. The userID field in REF_CSDUsers is related to the LastUserID field in REF_CSDComputers. SELECT REF_MasterNomen.Description, REF_CrossReference.Description, REF_CSDSoftware.ComputerName, REF_CSDComputers.LastUserID FROM ((REF_MasterNomen INNER JOIN REF_CrossReference ON REF_MasterNomen.NomenID = REF_CrossReference.NomenID) INNER JOIN REF_CSDSoftware ON REF_CrossReference.DescID = REF_CSDSoftware.DescID) INNER JOIN REF_CSDComputers ON REF_CSDSoftware.ComputerName = REF_CSDComputers.ComputerName WHERE (((REF_MasterNomen.NomenID)=14) AND ((REF_CrossReference.CategoryCode)=3) AND ((REF_CSDComputers.Billable)=Yes)) OR (((REF_MasterNomen.NomenID)=14) AND ((REF_CrossReference.CategoryCode)=3) AND ((REF_CSDComputers.Refreshed)=Yes)); Where should I be putting REF_CSDComputers.LastUserID and REF_CSDUsers.Username to list Username where LastUserID matches REF_CSDUsers.userID, without excluding records where LastUserID is blank? Any help, comments, or suggestions are greatly appreciated. Quote Never ascribe to malice that which is adequately explained by incompetence. - Napoleon Bonaparte
*Experts* Volte Posted September 10, 2003 *Experts* Posted September 10, 2003 I'm not sure why it's chopping the fields with no user ids, but did you try this?SELECT REF_MasterNomen.Description, REF_CrossReference.Description, REF_CSDSoftware.ComputerName, REF_CSDComputers.LastUserID, REF_CSDUsers.Username FROM ((REF_MasterNomen INNER JOIN REF_CrossReference ON REF_MasterNomen.NomenID = REF_CrossReference.NomenID) INNER JOIN REF_CSDSoftware ON REF_CrossReference.DescID = REF_CSDSoftware.DescID) INNER JOIN REF_CSDComputers ON REF_CSDSoftware.ComputerName = REF_CSDComputers.ComputerName INNER JOIN REF_CSDUsers ON REF_CSDComputer.LastUserID = REF_CSDUsers.UserID WHERE (((REF_MasterNomen.NomenID)=14) AND ((REF_CrossReference.CategoryCode)=3) AND ((REF_CSDComputers.Billable)=Yes)) OR (((REF_MasterNomen.NomenID)=14) AND ((REF_CrossReference.CategoryCode)=3) AND ((REF_CSDComputers.Refreshed)=Yes)); Quote
Gladimir Posted September 10, 2003 Author Posted September 10, 2003 Still a problem... I manually entered your additions and added the extra set of parentheses for the third INNER JOIN statement, and it truncated the output to 762 records. Thanks for your attempt. This is not really a show-stopper, but it is frustrating because having the username would be convenient and I believe it should be possible. Maybe this is a short-coming of Access... Quote Never ascribe to malice that which is adequately explained by incompetence. - Napoleon Bonaparte
JABE Posted September 11, 2003 Posted September 11, 2003 Using Volte Face's code, try changing INNER JOIN to LEFT OUTER JOIN: ... INNER JOIN REF_CSDComputers ON REF_CSDSoftware.ComputerName = REF_CSDComputers.ComputerName LEFT OUTER JOIN REF_CSDUsers ON REF_CSDComputer.LastUserID = REF_CSDUsers.UserID ... Quote
Gladimir Posted September 12, 2003 Author Posted September 12, 2003 SOLUTION... Thanks JABE! The LEFT OUTER JOIN did the trick. Here is what the final query looks like in the Access Query Designer if anyone is interested: SELECT REF_MasterNomen.Description, REF_CrossReference.Description, REF_CSDSoftware.ComputerName, REF_CSDComputers.LastUserID, REF_CSDUsers.Username, REF_CSDComputers.BusinessUnit, REF_CSDComputers.Department, REF_CSDComputers.SiteLocation, REF_CSDComputers.LastDate FROM (((REF_MasterNomen INNER JOIN REF_CrossReference ON REF_MasterNomen.NomenID = REF_CrossReference.NomenID) INNER JOIN REF_CSDSoftware ON REF_CrossReference.DescID = REF_CSDSoftware.DescID) INNER JOIN REF_CSDComputers ON REF_CSDSoftware.ComputerName = REF_CSDComputers.ComputerName) LEFT OUTER JOIN REF_CSDUsers ON REF_CSDComputers.LastUserID = REF_CSDUsers.userID WHERE (((REF_MasterNomen.NomenID)=14) AND ((REF_CrossReference.CategoryCode)=3) AND ((REF_CSDComputers.Billable)=Yes)) OR (((REF_MasterNomen.NomenID)=14) AND ((REF_CrossReference.CategoryCode)=3) AND ((REF_CSDComputers.Refreshed)=Yes)); Quote Never ascribe to malice that which is adequately explained by incompetence. - Napoleon Bonaparte
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.