SQL Syntax Problem...

G

Gladimir

Well-known member
Joined
Mar 29, 2003
Messages
60
Location
San Diego, CA
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. Im 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.

Code: 
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.
 
Im not sure why its chopping the fields with no user ids, but did you try this?
Code: 
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));
 
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...
 
Using Volte Faces 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
...
 
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:
Code: 
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));
 
Back
Top