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.

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

Any help would be appreciated

 

[xenou]

Hi, you should just post your query. I would guess it should include a left join or an exists clause.

 

[heisenberg_sean]

Hi, you should just post your query. I would guess it should include a left join or an exists clause.

SELECT tblPrep_Record.Media, tblPrep_Record.Water, tblPriceProductList.[Media Long], tblPriceProductList.[Media Abbrev] INTO qryCalculateTEMP2
FROM tblPrep_Record, tblPriceProductList
WHERE (((tblPrep_Record.Media)<>[tblPriceProductList]![Media Abbrev] Or (tblPrep_Record.Media)=[tblPriceProductList]![Media Long]));

 

[Joe4]

Just guessing here, but I think maybe your OR should be an an AND:

[COLOR=#333333]WHERE (((tblPrep_Record.Media)<>[tblPriceProductList]![Media Abbrev] [/COLOR][B][COLOR=#ff0000]Or[/COLOR][/B][COLOR=#333333] (tblPrep_Record.Media)=[tblPriceProductList]![Media Long]));[/COLOR]

should be:

[COLOR=#333333]WHERE (((tblPrep_Record.Media)<>[tblPriceProductList]![Media Abbrev] [/COLOR][B][COLOR=#FF0000]And[/COLOR][/B][COLOR=#333333] (tblPrep_Record.Media)=[tblPriceProductList]![Media Long]));[/COLOR]

With Or, it will return any record where EITHER of those two conditions are met.
With And, it will only return records where BOTH of those conditions are met.

 

[Joe4]

Cross-posted here: http://www.accessforums.net/showthread.php?t=73177

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[xenou]

You also need some kind of ID in there for your media. And when looking for things that aren't there you can't use "equals" or "not equals" or "less than / greater than". Things that aren't there are neither equal to or not equal to anything, because there is no basis for comparison.