Access Query Returning 95,000 results

[heisenberg_sean]

Hi everyone,


I am having an issue with my Access Database returning duplicates.


Context: At my workplace we prepare microbiological 'Media' (Agars etc.), which is held in tblPrep_Records. There is a table containing information about the media, including the price per litre.


I have created a few queries which check to see if the Media on tblPrep_Records matches either the Media Abbrev or Media Long on tblProductPriceList, these work fine.




However, bespoke media is sometimes made that isn't on tblPriceProductList, so I tried to make a query (qryCalculate2) that returns records from tblPrep_Record if they are not listed on tblPriceProductList but this instead returns ~95,000 records.



^Link to database





Any help would be appreciated

[ranman256]

Set the query property to UNIQUE VALUES =true.

depending on the fields in the qry, you may get dupes regardless.

[davegri]

Try this:
Create this query and name it qInterimUnmatched

SELECT DISTINCT tblPriceProductList.[Media ID], tblPrep_Record.Media, tblPrep_Record.water
FROM tblPrep_Record INNER JOIN tblPriceProductList ON tblPrep_Record.Media = tblPriceProductList.[Media Long]
WHERE (((tblPriceProductList.[Media Long]) Is Null)) OR (((tblPriceProductList.[Media Abbrev]) Is Null));

then change existing qryCalculate2

SELECT  tblPriceProductList.[Media Long], tblPriceProductList.[Media Abbrev], (Val([qInterimUnmatched].[Water]))*[tblPriceProductList]![Cost Per Litre] AS Cost, AlphaOnly([Water]) AS Alphas INTO qryCalculateTEMP2
FROM tblPriceProductList INNER JOIN qInterimUnmatched ON tblPriceProductList.[Media ID] = qInterimUnmatched.[Media ID];

It will create 11 records in the new table with the data you posted.

[Micron]

Called out for cross posting here
https://www.mrexcel.com/forum/micros...0-results.html
a few days ago, but no acknowledgement there by OP on that