Query to compare numbers to check within range?

[emamekyd]

Hello,

I need a sort of auditing query that checks to see if numbers are within a range. Here is an example of the data.

Lets say I am selling fruits and giving (selling) certain fruits a barcode range that they can apply to their fruits

BCLog (barcode start, barcode finish, product)
100000, 400000, Apples
400001, 600000, Bananas
600001, 900000, Oranges
900001, 999999, Watermelon

And then I have a separate table that contains info of the barcodes that were ACTUALLY placed on the fruits (on certain times, etc.)

BCUsed (barcode start, barcode finish, product, day applied)
200000, 300000, Apples, Monday
500000, 505000, Bananas, Tuesday
800000, 900000, Oranges, Thursday
600000, 700000, Watermelon, Thursday

How could I write a query that calls out any errors if the fruit placed barcodes in the wrong range?

 

[plog]

Try this:

SELECT BCUsed.*
FROM BCUsed
INNER JOIN BCLog ON BCUsed.product = BCLog.product
WHERE (BCLog.BarcodeStart>=BCUsed.BarcodeFinish) OR (BCLog.BarcodeFinish<=BCUsed.BarcodeStart)

I changed your field names to not include spaces because that makes life easier.

 

[emamekyd]

Hmm, I tried it and I like that logic! But I have multiples of the entries in the BCLog that are showing up, for instance

100, 400, Bananas
401, 500, Apples
501, 700, Bananas

So I need to figure out a way I can talk to only that range hmm.

BTW I can use excel as well to help me with this

 

[plog]

I don't understand. Each of those records should fail based on the initial data you provided. Please provide better sample data to demonstrate your issue.

 

[emamekyd]

So using that formula you gave me, using the following data (im adding a 'key' at the end - just as a point of reference) :

BCLog
100, 400, Bananas, 1
401, 500, Apples, 2
501, 700, Bananas, 3

BCUsed
105, 200, Bananas, 1

It is pointing that BCUsed entry out because it is less than entry '3' on the BCLog but technically it is correct because it fits entry '1'

 

[plog]

Can BCUsed contain duplicate products? Might Bananas show up more than once in BCUsed?

If so, do you have an autonumber primary key on BCUSED assigned to each record?

 

[emamekyd]

Yes both can have multiples (Sorry for not clarifying that further)
Almost all of the barcodes should be used up so the table would look like

BCUsed
100, 104, Bananas, Monday
105, 200, Bananas, Tuesday
201, 400, Bananas, Friday

And yes there is a key and also a date.

 

[plog]

Ok, this is now going to take a sub-query. I am assuming you have an autonumber primary key in BCUsed called ID. This is the sql for the sub-query:

SELECT BCUsed.ID, IIf([BCUSED].[BarcodeStart]<[BCLog].[BarcodeStart],1,0)+IIf([BCUsed].[BarcodeFinish]>[BCLog].[BarcodeFinish],1,0) AS Invalid
FROM BCUsed INNER JOIN BCLog ON BCUsed.product = BCLog.product;

Name that sub-query 'sub1'. It compares individual product record matches between BCUsed/BCLog and sees if those BCUsed records are valid based on that specific BCLog record.

This query will tell you which BCUsed records were invalid when matched against all matching product values in BCLog:

SELECT BCUsed.ID, BCUsed.product, BCUsed.BarcodeStart, BCUsed.BarcodeFinish, Min(sub1.Invalid) AS MinOfInvalid
FROM sub1 INNER JOIN BCUsed ON sub1.ID = BCUsed.ID
GROUP BY BCUsed.ID, BCUsed.product, BCUsed.BarcodeStart, BCUsed.BarcodeFinish
HAVING (((Min(sub1.Invalid))>0));

 

[emamekyd]

For some reason this is returning all the records. I have thought through the logic and it all makes sense to me. Hmmm. :banghead:

 

[plog]

It works on the test data I have:

BCLog
BarcodeStart, BarcodeFinish, product
100, 400, Bananas
401, 500, Apples
501, 700, Bananas
600, 750, Watermelon

BCUsed
BarcodeStart, BarcodeFinish, product, ID
105, 200, Bananas, 1
399, 402, Apples, 2
10, 90, Bananas, 3
660, 670, Watermelon,4

It returns ID=2 & ID=3. Can you post sample data to demonstrate how its failing for you?

 

[emamekyd]

Okay so it turns out its my data that's giving me issues. Something i've run across is this:

BCLog
BarcodeStart, BarcodeFinish, product
100, 400, Bananas
401, 500, Bananas

BCUsed
BarcodeStart, BarcodeFinish, product, ID
380, 450 , Bananas, 1

So its pulling it out as an error but technically its within range (but we just have multiple entries grrr)