Subquery help?

  • Thread starter Thread starter Jay
  • Start date Start date
J

Jay

I have a table with the following fields:

fr_mileage
fr_24
fr_36
fr_48

What I want is a query that returns

all fr_24 values where fr_mileage=40
all fr_36 values where fr_mileage=60
all fr_48 values where fr_mileage=80

I appreciate this is probably going to involve subqueries but can't seem to
get it right.

ANY help greatly appreciated......thanks, Jason
 
SQL view for your queries would look like the following

SELECT IIF(fr_Mileage = 40,fr_24,Null) as fr40
, IIF(fr_Mileage = 60,fr_36,Null) as fr60
, IIF(fr_Mileage = 80,fr_48,Null) as fr80
FROM YourTable
WHERE fr_Mileage in (40,60,80)

Or perhaps, if you want the results all in one field
SELECT IIF(fr_Mileage = 40, fr_24, IIF(fr_Mileage = 60,fr_36, IIF(fr_Mileage
= 80,fr_48,Null))) as Result
FROM YourTable
WHERE fr_Mileage in (40,60,80)

In Design view just use a calculate expression
Field: Result: IIF(fr_Mileage = 40, fr_24, IIF(fr_Mileage = 60,fr_36,
IIF(fr_Mileage = 80,fr_48,Null)))

Obligatory comment on table structure. You have repeating fields containing
essentially the same information. You table should probably be structured
more like
fr_mileage
fr_Type (Containing 24, 36, 48, and other values to determine the type)
fr_Value (Containing whatever is currently stored in Fr_24, Fr_36, and
Fr_48)

If you have Fr24 and fr36 then you would have two records. That would make
the above queries simpler

SELECT fr_Value, fr_Type
FROM YourTable
WHERE fr_mileage in (40,60,80)

OR

SELECT fr_Value
FROM YourTable
WHERE fr_mileage in (40,60,80)
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
*MANY* thanks John, I really appreciate your help. Looking at it, I just
realised I use the same caluclated field technique elsewhere - I just
completely forgot!

And I can't do anything about the table structure. It's an .adp so the SQL
tables structure are the I.T team's responsibility:-)

Best Regards, Jason
 
Hi John,

Actually my .adp doesn't like the syntax, do you happen to know how to write
the first SELECT statement in T-SQL?

Thanks,....Jason
 
Instead of IIF use a Case statement. I think the following should work. If
not, check the T_SQL help on Case

SELECT
CASE fr_Mileage = 40 Then Fr_24 END
, CASE fr_Mileage = 60 Then Fr_36 END
, CASE fr_Mileage = 80 Then Fr_48 END
FROM YourTable
WHERE fr_Mileage in (40,60,80)


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Back
Top