HELP 2

[mo2783]

Hi

I have created a query which will return all applications installed on a particular computer, i am trying to show all applications that are not installed on a particular Computer.

I have the following tables:

tbl_Computer_Main_Applications
Computer_Main_Application_ID
Computer_Main_App_Name
Computer_Main_App_Version

tbl_Computer_Identification
Computer_ID
Computer_Asset_Number
Computer_ID_Make
Computer_ID_Model
Computer_ID_Serial_Number
Computer_ID_Warranty_Expiry
Computer_ID_In_Stock
Computer_ID_Out_Of_Service

tbl_Application_Computer_JoinTable
Computer_ID
Application_ID

The query that returns Applications instaleld on a particular Computer looks like this:

SELECT tbl_Computer_Identification.Computer_Asset_Number, [Computer_Main_App_Name] & " " & [Computer_Main_App_Version] AS Application, tbl_Application_Computer_JoinTable.Computer_ID, tbl_Application_Computer_JoinTable.Application_ID

FROM tbl_Computer_Main_Applications INNER JOIN (tbl_Computer_Identification INNER JOIN tbl_Application_Computer_JoinTable ON tbl_Computer_Identification.Computer_ID = tbl_Application_Computer_JoinTable.Computer_ID) ON tbl_Computer_Main_Applications.Computer_Main_Application_ID = tbl_Application_Computer_JoinTable.Application_ID

WHERE (((tbl_Computer_Identification.Computer_Asset_Number)=[Enter Asset Number]));

Please can someone help.

Thanks

Mo

 

[Remou]

You can try:

SELECT q1.CompID, q1.AppID
FROM tbl_Application_Computer_JoinTable j 
RIGHT JOIN (
       SELECT ca.Computer_Main_Application_ID AS AppID,
              j.Computer_ID As CompID
       FROM tbl_Computer_Main_Applications ca,
       tbl_Application_Computer_JoinTable j) AS q1 
ON (j.Computer_ID = q1.CompID) 
AND (j.Application_ID = q1.AppID)
WHERE j.Application_ID Is Null

Please use more descriptive titles than 'Help'.

 

[mo2783]

Remou

Sorry remou about the title, i realize when i posted didnt know how to edit the title or delete the question and start again.

Mo

 

[mo2783]

Remou

I take it q1 is Query1?

Mo

 

[Remou]

q1 is an alias for the subquery

[tt]SELECT ca.Computer_Main_Application_ID AS AppID,
j.Computer_ID As CompID
FROM tbl_Computer_Main_Applications ca,
tbl_Application_Computer_JoinTable j[/tt]

The whole thing should work on a cut-and-paste to SQL view of the query design window, though you can break it down into the component parts if you wish.


Yes, not being able to edit is a bit of a killer.

 

[mo2783]

Remou

Sorry but not to familier with Subqueries, how do i get the user to enter and Computer Asset number and display the end results according to the asset number?

Mo

 

[Remou]

Like so:

SELECT ci.Computer_Asset_Number, q1.CompID, q1.AppID
FROM (tbl_Application_Computer_JoinTable j 

RIGHT JOIN (
     SELECT ca.Computer_Main_Application_ID AS AppID,
            j.Computer_ID AS CompID
     FROM tbl_Computer_Main_Applications ca, tbl_Application_Computer_JoinTable j) q1 
ON (j.Application_ID = q1.AppID) AND (j.Computer_ID = q1.CompID)) 

LEFT JOIN tbl_Computer_Identification ci
ON q1.CompID = ci.Computer_ID
WHERE ci.Computer_Asset_Number=[Enter Asset No: ] AND j.Application_ID Is Null

 

[mo2783]

Thanks a lot Remou, that works fine can you breifly explain how this works if its not to much for you as i am not familier with subqueries.

Thanks once again

Mo

 

[Remou]

I have used aliases to make things easier to read, for the purposes of this query:

tbl_Application_Computer_JoinTable ... is called ... j
tbl_Computer_Main_Applications ... is called ... ca
tbl_Computer_Identification ... is called ... ci

The subquery ...
SELECT ca.Computer_Main_Application_ID AS AppID,
j.Computer_ID AS CompID
FROM tbl_Computer_Main_Applications ca, tbl_Application_Computer_JoinTable j
... is called ... q1

The subquery uses a cartesian join (

http://www.fluffycat.com/SQL/Cartesian-Joins/),

these are often deprecated, but I think it works quite well here as there are not too many records. This returns a row for each computer and application.

There is a problem with the query in that a computer that does not have any applications installed will not be returned. You can get these by using the wizard to created a "find unmatched records query" using the computer list and the join table.

 

[dhookom]

Remou deserves a star for the excellent job of posting the solution and an explanation.

Duane
Hook'D on Access
MS Access MVP

 

[mo2783]

Thanks Remou

Remou has already been given a star for the Excellent job, much appreciated Remou. Well Deserved Star.

http://www.1keydata.com/sql/sqljoins.html