0
votes

Question: Is there a way to make the unmatched query return the records that have multiple items but the same information?

I am running an unmatched query against a table and an SQL query. Everything works for the records that do not exist in the query. My issue lies in that records that should be repeated are not recognized when more than one record exists in the query.

These are aircraft parts that are required to be installed and tracked. Each part has code associated with its location LOC. In some cases the parts have the same LOC code but multiple parts are installed.

Example: In one case we have 8 bolts that are installed. The table lists each bolt individually and they all have the same LOC. If only one bolt is installed, the unmatched query does not return a value for the 7 remaining bolts that are not installed. It sees the one bolt and moves on.

Is there a way to make the unmatched query return the records that have multiple items but the same LOC and Name?

SELECT [MCDS-E].MODEL, [MCDS-E].LOC, [MCDS-E].NOMENCLATURE, [MCDS-E].CHANGE_TYPE,
       [MCDS-E].TBO, [MCDS-E].CHANGE_NUMBER, [MCDS-E].DA2410, 
       [MCDS-E].LOCAL_TRACKED, [MCDS-E].COMMENTS    
FROM [MCDS-E] 
LEFT JOIN Select_ACFT ON [MCDS-E].[LOC] = Select_ACFT.[LOC]    
WHERE ((([MCDS-E].MODEL)=Forms!Index!Text5) 
   AND ((Select_ACFT.LOC) Is Null));

My table might have:

    LOC        NOMENCLATURE

 - ABCD123     Bolt
 - ABCD123     Bolt 
 - ABCD123     Bolt 
 - ABCD123     Bolt

The query might have :

    LOC        NOMENCLATURE
 - ABCD123     Bolt

The match query would not show any results for the other three ABCD123 Bolts that are not installed.

Any assistance would be GREATLY appreciated.

Thanks

1
Please edit your question and provide sample data and desired results.Gordon Linoff
GROUP BY loc and name and SUM or COUNT the partgeeFlo
I added the code from the access sql queryJames Benton
In your example data, what field is Bolt -the Nomenclature, LOC or even Model? I have an answer but might be confusing how pieces relate. Unmatched query should return all LOC unless this it the field that indicates installed.Parfait
Basically, we have components installed on aircraft and each component has a serial number. I am trying to find where components are not installed (no record) in the same location. Like I mentioned above. like an aircraft may have 4 rotor blades and all have the same location 123ABC but only three are installed in the computer. I am trying to match a table of all the components against a query of installed components and find those that are not installed.James Benton

1 Answers

0
votes

Consider joining back your query resultset to original table as current query returns unmatched records. Once those particular LOC are found, join back to full [MCDS-E] table for related items.

Below assumes Name is the NOMENCLATURE field used as the join back o main:

SELECT main.MODEL, main.LOC, main.NOMENCLATURE, main.CHANGE_TYPE,
       main.TBO, main.CHANGE_NUMBER, main.DA2410, 
       main.LOCAL_TRACKED, main.COMMENTS  
FROM [MCDS-E] main
INNER JOIN
  (SELECT m.[LOC], m.NOMENCLATURE
   FROM [MCDS-E] m
   LEFT JOIN Select_ACFT s ON m.[LOC] = s.[LOC]    
   WHERE ((([MCDS-E].MODEL)=Forms!Index!Text5) 
      AND ((Select_ACFT.LOC) Is Null))) AS dT
ON dT.NOMENCLATURE = main.NOMENCLATURE

Alternatively, saving above nested SELECT as its own query:

SELECT main.MODEL, main.LOC, main.NOMENCLATURE, main.CHANGE_TYPE,
       main.TBO, main.CHANGE_NUMBER, main.DA2410, 
       main.LOCAL_TRACKED, main.COMMENTS  
FROM [MCDS-E] main
INNER JOIN [UnmatchedQuery] u
ON main.NOMENCLATURE = u.NOMENCLATURE