CODEWITHSUNDEEP
ms-access
Gali
Gali

Reputation: 14953

help with access query

i have this table Discount:

from | to | Dis
1    | 50 | 10
51   | 80 | 20
81   | 99 | 30
99   |120 | 40

and i have this work table MyTable:

id | sum | dis
123| 22  | 
222| 85  |
131| 85  |

how to fill the dis field in MyTable ? that the table will look like this:

(if the sum between the from and the to value)

id | sum | dis
123| 22  | 10
222| 85  | 30
131| 95  | 40

i need it in access

thanks in advance

Upvotes: 1

Views: 78

Answers (2)

Fionnuala
Fionnuala

Reputation: 91306

You can use a fairly standar solution, which is:

SELECT MyTable.id, MyTable.sum, Discount.Dis
FROM Discount, MyTable
WHERE MyTable.sum Between [Discount].[From] And [Discount].[To]

This can be set up in the query design window.

Upvotes: 0

Kevin Ross
Kevin Ross

Reputation: 7215

You have to use an unequal join, most people thing that joins have to be where sometime equals something but that is not the case

SELECT tblMyTable.ID, tblMyTable.Sum, tblDiscount.Dis
FROM tblMyTable INNER JOIN tblDiscount ON tblMyTable.Sum <= tblDiscount.To AND tblMyTable.Sum >= tblDiscount.From

Access does not support viewing them in design mode so you have to write the SQL yourself

EDIT: Here is a slighlty different version that is an outer join so if you have not setup a discount range for that value it will default to 0

SELECT tblMyTable.ID, tblMyTable.Sum, NZ(tblDiscount.Dis,0) AS Discount
FROM tblMyTable LEFT OUTER JOIN tblDiscount ON tblMyTable.Sum <= tblDiscount.To AND tblMyTable.Sum >= tblDiscount.From

Upvotes: 1

Related Questions