Access Query Returning 95,000 results (1 Viewer)

[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


Or use attached file


Any help would be appreciated :banghead:

 

[RuralGuy]

Cross posted at: http://www.accessforums.net/showthread.php?t=73177
Please read: https://www.excelguru.ca/content.php?184

 

[plog]

When you create a query without a JOIN, that's called a cartesian product. Every record in one table gets matched with every record in the other table. So that means your starting world is 161,504 records (784 * 206).

Now, you applied some criteria which narrows that down a little. Of those 161,504 records 64,762 met your criteria (tblPrep_Record <>[tblPriceProductList]![Media Abbrev] Or [tblPriceProductList]![Media Long]). Thus your result.

I honestly don't know what you expect or are trying to do. But I do know that negation with OR statements are hard for people to wrap there mind around. Let's use a symplified example:

tbl1_Fruits
Orange
Banana
Apple

tbl2_BadFruits
Bad1, Bad2
Orange, Raspberry
Banana, Banana
Orange, Apple

If you wrote a query that said "I want a list of tbl1_Fruits that are not in Bad1 or Bad2 of tbl2_BadFruits" you would get the following results:

Orange
Orange
Orange
Banana
Banana
Apple
Apple
Apple


Here's why: It's a cartesian product so our world is 9 records (3 records in tbl1_Fruits * 3 records in tbl2_BadFruits). Then we apply our criteria to kick out 1 Banana record and we end up with 8 records in our query.

Now, you're asking why did Orange show up 3 times instead of once? Because your not/OR criteria:

if Orange Not Orange OR Orange Not Raspberry Then Show The Record

Replace the individual comparisons above with if they are True or False and the computer sees this:

If False Or True Then Show The Record

With an Or statement any one True makes the whole thing true. So that record gets shown. So you kind of made a few logical errors and I can't really understand what it is you hope to end up with.

 

[lpapad]

https://www.essentialsql.com/learn-to-use-union-intersect-and-except-clauses

You might find some answers...

 

[mike60smart]

Hi

Take a look at the 2 unmatched queries in the attached.

Do these give you anything like a result you are expecting?

View attachment MOS -Media Allocation 3 Drive Copy.zip