Connecting tables where there is not an exact match

  • Thread starter Thread starter Smudger
  • Start date Start date
S

Smudger

Can you please help?

I am trying to connect 2 tables eg.

Table1
Name Sales Percentage
Mr A 1.25
Mr B 2.4
Mr C 1.75
Mr D 1.5
Mr E 2

Table 2
Sales Percentage Bonus
1 10
1.5 12
2 15
2.5 17

I want to connect the 2 tables on Sales Percentage and
return Name and Bonus amount but as you can see the
figures on Sales Percentage are not exact matches.

Is there anything I can do to make table 1 look for
the "Nearest Match" in Table 2

Kind Regards

Smudger
 
First, Add a field to table 1, call it something like
rounded sales percentage.

Next make a new query, bring in the sales percentage and
rounded sales percentage fields.

Change the query type to Update Query.
In the Update To field of the Rounded Percentage field
copy the following code:

IIf(([SalesPercentage]-Int([SalesPercentage])<0.5),Int
([SalesPercentage]),Int([SalesPercentage])+0.5)

Check to make sure my spelling matches yours.]

Run the query
Now you will have matching fields in each table.
 
Can you please help?

I am trying to connect 2 tables eg.

Table1
Name Sales Percentage
Mr A 1.25
Mr B 2.4
Mr C 1.75
Mr D 1.5
Mr E 2

Table 2
Sales Percentage Bonus
1 10
1.5 12
2 15
2.5 17

I want to connect the 2 tables on Sales Percentage and
return Name and Bonus amount but as you can see the
figures on Sales Percentage are not exact matches.

Is there anything I can do to make table 1 look for
the "Nearest Match" in Table 2
Click to expand...

A "Non Equi Join" will do this. It's a lot simpler if your Table2 has
*ranges* - for instance:

SalesPctLow SalesPctHi Bonus
0 1.25 10
1.25 1.75 12
1.75 2.25 15
2.25 100 17

A Query

SELECT [Name], [Sales Percentage], Bonus
FROM Table1 INNER JOIN Table2
ON Table1.[Sales Percentage] >= Table2.SalesPctLow
AND Table1.[Sales Percentage] < Table2.SalesPctHi;

will do what you want.
 
The disadvantages of adding a field to contain calculated values has been
discussed in this 'group (tablesdbdesign) before. Besides, why create a
table when a query could, on the fly, generate that calculated value? You
could then use the query as input to the join you described.
 
Back
Top