[xtreme666]

SELECT Property.ID, Property.Location, Property.Status, IF propety.location > 0 And property.location < 10 THEN 
		50 * (property.status - 1)
	 ELSE IFproperty.location > 10 AND property.location < 20 THEN 
		100 * (property.status - 1)
	 ELSE IF property.location > 20 AND property.location < 30 THEN
		 150 * (property.status - 1)
	 ELSE IF property.location > 30 AND property.location < 40 THEN 
		200 * (property.status - 1)
 END IF AS [value]
FROM Property;

i need to add this is query

[modi123_1]

What?

[Blasterman007]

I would suggest you create a function in a module. Pass in the Property Location and return the result.

Roughly coded:

Function ProcessLocation(byval myLoc as integer, byval mystatus as integer) as integer
  Select case myLoc
     case 0 to 10
         ProcessLocation = (mystatus-1) * 50
     case...

End Function

You can find specific language for select case logic here:
My linkhttp://msdn.microsoft.com/en-us/library/cy37t14y.aspx

In query you simply call the function:

Select ProcessLocation(Property.Location, Property.Status) as myField, 

Hope this helps.

[immeraufdemhund]

your best bet is to link a table together and have it be able to do the math for you.

but since we don't know your table structure it will be very hard for us to help you.

What tables do you have? what relationships do you have?

if you have a location table and a property table then in your location table you'd add your multiplyer to that list by hand. then you can in your select statment have something like

SELECT Property.ID, Property.Location, Property.Status, (Location.Multi * (Property.Status-1)) AS Variable 
FROM Property, Location
WHERE Property.Location = Location.ID;

mind you this code won't work but it is an idea

[xtreme666]

my table is property
and fields are
ID
Location
Status.

id is primary key from 1 to 28
location is number from 1 - 39
status is number from 0 - 5

now i need a column with calculations like
is the location is between 1 to 10
then value should be 50 * (status - 1)
if location is 10 to 20 then 100 * status - 1
upto 40 where 200 * status - 1


i dont have experience about access. so i am totally stuck..

i am sorry

[June7]

Try:

SELECT ID, Location, Status,
(Status - 1) * IIf(Location > 30, 200,
IIf(Location > 20, 150,
IIf(Location > 10, 100,
IIf(Location > 0, 50, 0)))) AS [AdjValue]
FROM Property;

Or

SELECT ID, Location, Status,
(Status - 1) * Switch(Location > 30, 200,
Location > 20, 150,
Location > 10, 100,
Location > 0, 50) AS [AdjValue]
FROM Property;

Never exceeds 40 and 0 not possible? Need to handle possible null in Location field?

Value might be a reserved word, avoid as a field name.

This post has been edited by June7: 05 April 2012 - 04:06 PM

[xtreme666]

thanks will try it.

its 4 am, calling it night.

nope never exceeds 40. 39 is last.

only 0 to 39 blocks
and its select and update table only.

no delete or insert

[June7]

If 0 is a location, need to handle: >= 0

[xtreme666]

well, location 0 is present, but not required in database..
so i can only use >