Jump to content
Xtreme .Net Talk

Recommended Posts

Posted

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.

Never ascribe to malice that which is adequately explained by incompetence. - Napoleon Bonaparte
  • *Experts*
Posted
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));

Posted

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...

Never ascribe to malice that which is adequately explained by incompetence. - Napoleon Bonaparte
Posted

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

...

Posted

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));

Never ascribe to malice that which is adequately explained by incompetence. - Napoleon Bonaparte

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...