Searching for the highest applicable cell

[Hatsuwr]

Hello everyone,



Sorry to be so vague with this, but here is essentially what I am trying to do.

I have two tables. The first table contains a list of objects and their attributes, with none of the objects repeated. The second table contains a list of attributes with no object yet paired with them. There are expected repeats here.

Name	Attribute 1	Attribute 2	Attribute 3
Object A	Xa	Ya	Za
Object B	Xb	Yb	Zb
Object C	Xc	Yc	Zc

Attribute 4	Attribute 5	Attribute 6	Attribute 7
Xa	F2	F2	F3
Xa	G1	G2	G3
Xb	H1	H2	H3
Xb	I1	I2	I3
Xb	J1	J2	J3
Xc	K1	K2	K3

What I need to do is, in a new table, list each object with the highest applicable Attribute 7 value. To be applicable, Attribute 1 of the object must match the corresponding Attribute 4 value. In addition, a function containing Attributes 2, 3, 5, and 6 must be less than a certain value.

For example, if looking at Object B, because its Attribute 1 value is Xb, the only Attribute 7 values that are possible are H3, I3, and J3. From there, say we perform the function with Yb, Zb, H/I/J1, and H/I/J2 and only the results with rows I and J are low enough, that leaves us with just I3 and J3. I3 happens to be larger than J3, so Object B is listed with I3.


Thank you for any help!

[ItsMe]

It seems you are looking for records in one table that do not match perfectly with records in another table. There is a way to address this. You can use the Query Wizard and select "Unmatched". Having said that, it seems there is some additional criteria, regarding "Highest". Perhaps you can address this first.

I would start with a SELECT query and add a Sort Order. Then, switch to SQL view and add TOP 10 or TOP 1 or TOP 39. The idea is to create a query that only retrieves the first records based on your Sort/ORDER BY.
SELECT TOP 1
http://www.w3schools.com/sql/sql_top.asp