Access Query Returning Duplicate Records

[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.

drive.google.com/file/d/1B_WH6L-PpQIO-t9m9_abKaCgQVoOPzvm/view?usp=sharing ^Link to database

https://files.engineering.com/getfi...file=MOS_-Media_Allocation_3_Drive_Copy.accdb

Any help would be appreciated

 

[dhookom]

If you are looking for record from tblPrep_Record that aren't in tblPriceProductList, how could you expect to use [tblPriceProductList]![Cost Per Litre] to calculate the cost in the query?

I would first create a union query of all Abbrev and Long like:

SELECT tblPriceProductList.[Media ID], tblPriceProductList.[Media Abbrev] 
FROM tblPriceProductList
WHERE [Media Abbrev] is not null
UNION SELECT tblPriceProductList.[Media ID], tblPriceProductList.[Media Long]
FROM tblPriceProductList
WHERE [Media Long] is not null;

Then use an unmatched query between quniMedia and tblPrep_Record:

SELECT quniMedia.[Media ID], tblPrep_Record.Media, 
tblPrep_Record.Water, (Val([tblPrep_Record]![Water]))*[tblPriceProductList]![Cost Per Litre] AS Cost, 
AlphaOnly([Water]) AS Alphas INTO qryCalculateTEMP2
FROM quniMedia RIGHT JOIN tblPrep_Record ON quniMedia.[Media Abbrev] = tblPrep_Record.Media
WHERE (((quniMedia.[Media ID]) Is Null) AND ((tblPrep_Record.Media) Is Not Null));

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016