help with sql in access

[Guest]

Hi

I have a table call 'factors' for which i wish to create a query.

The table has the following columns:
From (integer),
To (integer),
Price (currency)

example of the table looks:
ID screen FROM TO PRICE
1 0 1 1 R 10,50
2 0 2 5 R 20,63
3 1 1 1 R 30,23
4 1 2 5 R 40,10
5 2 1 1 R 50,34
6 2 2 5 R 60,34



I have a form in which the user enters a two values, one for SCREEN and one
for the ranges between FROM and TO columns . The From and TO columns
represent quantitys for a certain items.

If the user enters 1 for the screen value and the value 3 for quantity .
The query then needs to find the range the value 3 falls between when the
screen value is 1. The price of the correct row needs to be returned if the
range is found. In this example the price R40.10 is returned because the
value 3 is between range 2 and 5 when the screen value is 1 as when entered
by the user

Unfortunately for myself I am not proficient with Access sql How ever I do
achieve the results with mySQL. I need help so i achieve the results in
access.

thanks for helping

 

[Marshall Barton]

I have a table call 'factors' for which i wish to create a query.

The table has the following columns:
From (integer),
To (integer),
Price (currency)

example of the table looks:
ID screen FROM TO PRICE
1 0 1 1 R 10,50
2 0 2 5 R 20,63
3 1 1 1 R 30,23
4 1 2 5 R 40,10
5 2 1 1 R 50,34
6 2 2 5 R 60,34



I have a form in which the user enters a two values, one for SCREEN and one
for the ranges between FROM and TO columns . The From and TO columns
represent quantitys for a certain items.

If the user enters 1 for the screen value and the value 3 for quantity .
The query then needs to find the range the value 3 falls between when the
screen value is 1. The price of the correct row needs to be returned if the
range is found. In this example the price R40.10 is returned because the
value 3 is between range 2 and 5 when the screen value is 1 as when entered
by the user

SELECT Price
FROM tblPrices
WHERE Screen = Forms!theform.txtScreen
AND Forms!theform.txtQty >= tblPrices.[From]
AND Forms!theform.txtQty <= To

The reason for all the syntax on the From field is that From
is an obvious keyword in an SQL statement so you should try
to obscure it so it doesn't confuse the SQL parser.

Depending on how/where you are using the price value, you
**might** find it easier to use a the DLookup function.

TotPrice = Me.txtQty * DLookup("Price", "tblPrices", _
"Screen = " & Me.txtScreen & _
" AND " & Me.txtQty & ">= tblPrices.[From] " & _
" AND " & Me.txtQty & "<= To ")