Subquery help?

[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

 

[John Spencer]

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
..

 

[Jay]

*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

 

[Jay]

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