Query returning fewer results than data table

[JL2309]

I have a table (TBL_Mint_Compounds) with 3326 entries (chemical compounds). Each compound is matched to a master list and returned with additional identifying information, so there should be the same number of entries in the final query (QRY_Mint_CompoundsClean) that exist in the original table. This has been 1:1 for all the other compound groups I've worked with. However, the query is only giving 3304 results - 22 are missing. I already ran a mismatch query to see if any of the compound names were missing from the matching table (TBL_MasterListv15), but it returned 0 results.



The query SQL is below; thank you in advance for any help/ideas!

SELECT First(TBL_Mint_Compounds.NCS_FEMA) AS FirstOfNCS_FEMA, First(TBL_MasterListv15.[JECFA Class]) AS [FirstOfJECFA Class], First(TBL_MasterListv15.[DT Class]) AS [FirstOfDT Class], First(TBL_MasterListv15.[FEMA Name]) AS [FirstOfFEMA Name], First(TBL_MasterListv15.[CAS No]) AS [FirstOfCAS No], First(TBL_MasterListv15.[FEMA (derived)]) AS [FirstOfFEMA (derived)], First(TBL_Mint_Compounds.AreaPercentage) AS FirstOfAreaPercentage, First(TBL_Mint_Compounds.AnalysisNo) AS FirstOfAnalysisNo, First(TBL_Mint_Compounds.Company) AS FirstOfCompany, TBL_Mint_Compounds.ID, TBL_Mint_IDnames.FEMAName AS NCSname
FROM TBL_MasterListv15 INNER JOIN (TBL_Mint_Compounds INNER JOIN TBL_Mint_IDnames ON TBL_Mint_Compounds.NCS_FEMA = TBL_Mint_IDnames.FEMA_NASNo) ON TBL_MasterListv15.[Compound Name] = TBL_Mint_Compounds.[CompoundName]
GROUP BY TBL_Mint_Compounds.ID, TBL_Mint_IDnames.FEMAName;

[Perceptus]

Regress the fields that you are joining on for inconsistent data.

[JL2309]

Thanks - what does this mean/how do I do that?

[Perceptus]

Your query joins different tabls together using specific fields. Query those fields on their own, look for issue. You can also try returning the records that are the negative of your current query. To do the 2nd portion requires changing the queries to operate in left join mode. This joins all the data, you can then tell it to return only data that has nulls for portions of the set.

[nhorton79]

I had a similar problem when doing query that referenced multiple tables, records were missing. I used, for example:

SELECT nz([fieldname],"") AS alias

This replaces any null fields in the query as a blank.

[JL2309]

Thank you everyone; I think I may just be too much of a novice to be able to fix this. I am not versed in VBA and think this is a formatting issue/invisible spaces, as most of the data was from different sources and brought in from Excel, but the TRIM function didn't work and I don't know how to implement the suggested fixes