Query values for trading

[GreenWizard]

Based on the table(s) below, with 1 equaling most valued & 8 equaling the least valued, I'm looking to identify differences in values that will help facilitate a trade.

Owner	Tool	Joe Smith Rank	Tom Brown Rank
Joe Smith	Axe	1	1
Joe Smith	Nails	8	7
Joe Smith	Saw	5	6
Joe Smith	Wrench	3	2
Tom Brown	Bolts	7	8
Tom Brown	Hammer	2	3
Tom Brown	Plyers	6	5
Tom Brown	Screwdriver	4	4

For example, based on the information above, Joe Smith values a Hammer more than a Wrench AND Tom Brown values a Wrench more than a Hammer. As a result, the query should identify this and report a 'Hammer FOR Wrench' trade.

A full query should output the following data below. I calculated this manually, but I'm looking to write a query to do this automatically and on a bigger scale with more data.



Query Results:
Nails (Joe Smith) FOR Bolts (Tom Brown)
Wrench (Joe Smith) FOR Hammer (Tom Brown)

[alansidman]

SELECT Sheet1.Owner, Sheet1.Tool, Sheet1.[Joe Smith Rank], Sheet1.[Tom Brown Rank]
FROM Sheet1
WHERE (((Sheet1.[Joe Smith Rank])=3)) OR (((Sheet1.[Tom Brown Rank])=3));

Change criteria for each rank you wish to test.

[GreenWizard]

Thank you, but I'm confused about the "=3".

I'm looking to match objects that have perceived values that are greater than AND less than 'certain variables'.

I need to identify these variables.

Thanks again.

[alansidman]

If you run this query changing the criteria, ie. "3" to whatever values you want, it will return the items in each of the two ranking fields that match that value. Showing for an equalization of values. In the case of three it returns exactly as you showed in the original thread. Did you try this out in your db.

If your example is not what is your reality, then perhaps you should upload a sample database with dummy data that shows reality. Otherwise, I am confused by your request re: perceived values.