8 Replies - 4353 Views - Last Post: 06 April 2012 - 01:03 AM

#1 xtreme666  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 30
  • Joined: 05-April 12

how to do this query?

Posted 05 April 2012 - 02:04 PM

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

Is This A Good Question/Topic? 0
  • +

Replies To: how to do this query?

#2 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9057
  • View blog
  • Posts: 34,001
  • Joined: 12-June 08

Re: how to do this query?

Posted 05 April 2012 - 02:15 PM

What?
Was This Post Helpful? 0
  • +
  • -

#3 Blasterman007  Icon User is offline

  • New D.I.C Head

Reputation: 2
  • View blog
  • Posts: 42
  • Joined: 15-September 09

Re: how to do this query?

Posted 05 April 2012 - 02:26 PM

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.
Was This Post Helpful? 0
  • +
  • -

#4 immeraufdemhund  Icon User is offline

  • D.I.C Regular

Reputation: 79
  • View blog
  • Posts: 495
  • Joined: 29-March 10

Re: how to do this query?

Posted 05 April 2012 - 02:30 PM

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
Was This Post Helpful? 0
  • +
  • -

#5 xtreme666  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 30
  • Joined: 05-April 12

Re: how to do this query?

Posted 05 April 2012 - 02:45 PM

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
Was This Post Helpful? 0
  • +
  • -

#6 June7  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 104
  • View blog
  • Posts: 904
  • Joined: 09-December 08

Re: how to do this query?

Posted 05 April 2012 - 03:40 PM

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

Was This Post Helpful? 0
  • +
  • -

#7 xtreme666  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 30
  • Joined: 05-April 12

Re: how to do this query?

Posted 05 April 2012 - 03:53 PM

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
Was This Post Helpful? 0
  • +
  • -

#8 June7  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 104
  • View blog
  • Posts: 904
  • Joined: 09-December 08

Re: how to do this query?

Posted 05 April 2012 - 04:09 PM

If 0 is a location, need to handle: >= 0
Was This Post Helpful? 0
  • +
  • -

#9 xtreme666  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 30
  • Joined: 05-April 12

Re: how to do this query?

Posted 06 April 2012 - 01:03 AM

well, location 0 is present, but not required in database..
so i can only use >
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1