how to query

[sriram]

i have a single table.it contains some 5 fields.for example a filed name is CONDITION.i have to write a query such that if the value of CONDITION contains A certain fileds should display out of 5 fields.If the value of CONDITION field contains B certain table should display out of 5.i like to know how to query.

for example:
table name: PROFIT
fields:ID,FIRST NAME,LAST NAME,EMP,CONDITION

MY DESIERD OUTPUT:IF CONDITION HAS A ID AND EMP SHOULD BE DISPLAYED.
IF CONDITION HAS B ID ,FIRST NAME AND LAST NAME SHOULD BE DISPLAYED.

PLEASE HELP ME IN QUERING IN ACCESS DATABASE.

THANK YOU

 

[CameronM]

SELECT IIF([CONDITION]="A",[ID],[FIRST NAME]) AS FIELD1, IIF([CONDITION]="A",[EMP],[LAST NAME]) AS FIELD2
FROM PROFIT

On caveat on this solution is that you will always need to output the same number of fields for both A and B and you should use an Alias for the output so that you can refer to the fields in forms and reports.

 

[sriram]

hi thxs for your reply.i think i did not say clearly what i want.the query u gave resulted in diff answer.
table name: PROFIT
fields:ID,FIRST NAME,LAST NAME,EMP,CONDITION
1.... AN............... YT ........S .......A
2.... NH................ZK..........D...... A
3.... GR ...............OR .........E ......B

IF I INPUT [CONDITION]="A" AS THE CRITERIA.(IF I WANT ONLY THE "A" VALUES IN THE CONDITION)
NORMALY THE RESULT WILL BE

ID,FIRST NAME,LAST NAME,EMP,CONDITION
1.... AN............... YT ........S .......A
2.... NH................ZK..........D...... A

IF I INPUT [CONDITION]="B" AS THE CRITERIA.(IF I WANT ONLY B VALUES IN THE CONDITION)

NORMALY THE RESULT WILL BE

ID,FIRST NAME,LAST NAME,EMP,CONDITION
3.... GR ...............OR .........E ......B

"BUT MY OUTPUT SHOULD BE"

IF I INPUT [CONDITION]="A" AS THE CRITERIA.

ID EMP CONDITION
1... S...... A
2... E...... A

IF I INPUT [CONDITION]="B" AS THE CRITERIA.

ID FIRST NAME LAST NAME CONDITION
3... GR .............OR............. B

PLEASE HELP ME IN WRITING A QUERY FOR THIS
THANKING YOU

 

[sriram]

plz some one reply

 

[CameronM]

You can use the same query, but add the WHERE clause to show only A or B values.

SELECT IIf([CONDITION]="A",[ID],[FIRST NAME]) AS FIELD1, IIf([CONDITION]="A",[EMP],[LAST NAME]) AS FIELD2, PROFIT.CONDITION
FROM PROFIT
WHERE PROFIT.CONDITION=[Enter the Condition]