I am working with an Access Database and using VB/VBA for programming. I have two tbl's as shown below. The first one is to get information and price from vendors. The second one is my own company price range. I want to check for 3 Prices from 3 different Types (1,2,3) of Fruit falling into Min and Max and return the Vendor name based on the Fruit Symbol.
For example: If the prices for Apple are 2.1, 2.23, 2.47 falling into the range: 2.1 - 2.55, then accept that vendor's price and return FreshFruit Inc. If not, reject all 3 prices of that fruit Type/Symbol and show an alert message for rejection.
I have tried many different ways with queries, but it is kind of too complex. I am thinking about using scripts instead, but I am not sure about that.
FreshFruit Inc. |Apple |1 |Ap |2.1
FreshFruit Inc. |Apple |2 |Ap |2.23
FreshFruit Inc. |Apple |3 |Ap |2.47
GreenProd LLC. |Banana |1 |Ba |0.65
GreenProd LLC. |Banana |2 |Ba |0.67
GreenProd LLC. |Banana |3 |Ba |0.63
UrFruit Inc. |Papaya |1 |Py |1.86
UrFruit Inc. |Papaya |2 |Py |1.62
UrFruit Inc. |Papaya |3 |Py |1.73
Here is another tbl:
Apple |Ap |2.1 |2.55
Banana |Ba |0.6 |0.65
Papaya |Py |1.6 |1.75
Thank you for your help
What you'll need to do is look into using Left Outer Joins on your tables. That way you can find your non-matches (or rejected vendors).
Once you find your non-matches, then you'd have to do another match to exclude your vendors which appear in your non-match list, giving you your matches (or accepted vendors).
If you want to be complex and use imbedded Selects within Selects, you can write it all in one query.
But if you are using VBA and want to keep it readable for future programmers who might encounter your code, you can write a few different queries that you run one after another. (ie the first query finds your non-matches (Rejects) and your second query matches to the first query to create a list of Accepted vendors.)
Some other things to keep in mind:
It might help you to index the tables you have and create primary keys.
Read up about 3rd Normal Form. It'll help you design better tables. (You don't have to be a slave to 3rd Normal Form... but knowing it can help you solve a lot of problems by making the data structures easier to understand.)
Best of luck :)
Edit 8/5/2015:
Ok... I've had time to help with the details. Using Left Outer Joins to find non-matches can be difficult to figure out. So here is my best shot at explaining them:
First, You will need to make your [Symbol] field in your [Rangetbl] into a Primary Key so that it's Indexed with No Duplicates allowed (if it's not already). You can't allow duplicates in this field or the Left Outer Join will not work properly. (Should you need duplicates in there, you would need to restructure your tables... and that's another discussion that's out of scope for this solution.) Also set it's "Allow Zero Length" to "No". You can not have Null values in the data set either.
You will need to create a new Query in your Access Database Project. (Cancel past the Wizards so you have a blank query.)
You can save it as [qry01NonMatches].
Swicth to "SQL View" (using the button on the top left Ribbon.)
Copy in this SQL code:
SELECT DISTINCT Vendortbl.Vendor
FROM Vendortbl LEFT OUTER JOIN Rangetbl ON
(Vendortbl.Symbol = Rangetbl.Symbol)
AND (Vendortbl.Price <= Rangetbl.Max)
AND (Vendortbl.Price >= Rangetbl.Min)
WHERE isnull(Rangetbl.Symbol) = true;
This is your Left Outer Join to find all the Vendors that have at least one record that is outside the min/max range for a given Symbol. This gives a list of Vendors that are your Rejects.
How is this done (you might ask)?
The Left Outer Join tells the Database to return ALL the Vendortbl records no matter what. But it joins to the Rangetbl and will return Rangetbl data if there is a match found. The trick we are using here is if there is No Match we will still get the Vendortbl record, but since there is no Rangetbl record, the Rangetbl fields will be populated with Null values. (Obviously! since there is no data to match to.) Remember this for later in the discussion.
But what are we matching on?
We want to match records based on the Symbol fields in both tables. (If your logic becomes more complex in the future, you can match on multiple fields if you need.) But since we want to force the Left Outer Join to give us Null values for Non-Matches, we also have to include the criteria for testing Min and Max at the table join level. Thus we use "AND" statments and test the Vendortbl Price against the Rangetbl Min and the Rangetbl Max. These three tests will match on Symbol and test for the Price being in the Min Max Range.
Vendortbl Matches will have valid data in the fields grabbed from the Rangetbl. And Vendortbl Non-Mathces will have Null values in the fields grabbed from the Rangetbl.
But we want only Non-Matches!
Well that's easy. You just have to add a WHERE clause that tests for the Null values in the Fields from the Rangetbl. A Null value in that field means there was no Match found. You can choose any Rangetbl field to test, but I chose Symbol since it's guaranteed to not have Nulls when a Match occurs. That's important! Read it again! The magic here is that if you have a Null, you know that record didn't match your test criteria! You've found your Non-Matches (or rejects).
But in Access, it doesn't test for null values well. So you have to use a function called "IsNull()" to test the field you are interested in. If IsNull is true, the field you tested for has a Null value. Thus, isnull(Rangetbl.Symbol) = true will return your Rangetbl Null records which are your Non-Matches.
But wait! We are not done yet!
We only want One record for each Vendor. What happens when all three Symbols fail the test? You would get a vendor record for each Non-Match. Thus you could possibly have three "UrFruit Inc." records. That would be annoying. So... to make sure you only get One record in the instance of a failure, you have to put DISTINCT after the SELECT clause. This ensures your Rejected Vendor will only appear once in the list.
Ta Da! You can now create a report or form (or both) that calls [qry01NonMatches] and lists all your Rejects.
Well... that solved your Reject list problem. But what about your Accepts?
You can use the same Left Outer Join logic to find your Matched records (which means the records met all your tests and fell in your range). Since you know who all your Non-Matches are... the ones that are not on this list are the ones that Matched. So find the records that are Not Matching the Non-Match list we just created.
Once again, You will need to create a new Query in your Access Database Project. (Cancel past the Wizards so you have a blank query.)
You can save it as [qry02Matches].
Swicth to "SQL View" (using the button on the top left Ribbon.)
Copy in this SQL code:
SELECT DISTINCT Vendortbl.Vendor
FROM Vendortbl LEFT OUTER JOIN qry01NonMatches ON
Vendortbl.Vendor = qry01NonMatches.Vendor
WHERE IsNull(qry01NonMatches.Vendor) = true;
Notice... we are Joining the Vendortbl to the previous query we made to find non-matched vendors.
Once again, we want All records from the Vendortbl but this time we match to the Query of rejects. But we want to test on the Vendor Field this time. Thus Matches will have data in the qry01NonMatches Vendor field and Non-matches will have Null values in the qry01NonMatches Vendor. But since the Matches have our Rejects, we want to save the Non-Matches (which are our non-rejects or "accepts"). So to save the non-matches we need a WHERE clause that checks for qry01NonMatches.Vendor being Null. Which is: IsNull(qry01NonMatches.Vendor) = true
And once again, we need to use DISTINCT after the SELECT clause so we only get one record for each unique vendor. This is really important here since the logic dictates there will always be three records for each Accepted Vendor.
Wah laa! Now you can create a report or form (or both) that calls [qry02Matches] and lists all your Accepted Vendors.
(And should you want to list all your accepted Prices, you can remove the DISTINCT from the query and display any of the Vendortbl fields that you'd like.)
It's amazing how powerful Left Outer Joins can be and how simple they are to use once you understand them. Hope this explanation helped. :)
One last bit of advise: You should consider making a [VendorSymbol] field so you can match on that in the second query. This is an important database design issue. You will find that people will misspell things in a Description field like [Vendor]. Some entries might be abbreviated and other spelled out completely. Thus, the different spellings won't match even though they represent the same entity. Third Normal Form is your friend in this instance. It's better database design and will save you tons of headaches.