select only required fields from the table according to condition

[sriram]

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

I NEED ONLY THE ABOVE 3 FIELDS IN THE RESULTING TABLE
IF I INPUT [CONDITION]="B" AS THE CRITERIA.

ID FIRST NAME LAST NAME CONDITION
3... GR .............OR................... B
I NEED ONLY THE ABOVE FOUR FIELDS IN THE RESULTING TABLE.IF THE USER INPUTS
"B"


PLEASE HELP ME IN WRITING A QUERY FOR THIS IN ACCESS 2007.
THANKING YOU

 

[ErezM via AccessMonster.com]

hi
you cannot have a select query output different fields (or columns) on
different conditions. this is not possible in access
you need 2 queries, one for condition A (or any condition with 4 fields), and
another for condition B

and the selecting proccess between them should be in vba code. like:

Dim cnd as string
cnd=InputBox("Select Condition", "Codition")
if cnd="A" then
DoCmd.OpenQuery "qryA"
else
DoCmd.OpenQuery "qryB"
End IF

good luck
Erez

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

I NEED ONLY THE ABOVE 3 FIELDS IN THE RESULTING TABLE
IF I INPUT [CONDITION]="B" AS THE CRITERIA.

ID FIRST NAME LAST NAME CONDITION
3... GR .............OR................... B
I NEED ONLY THE ABOVE FOUR FIELDS IN THE RESULTING TABLE.IF THE USER INPUTS
"B"

PLEASE HELP ME IN WRITING A QUERY FOR THIS IN ACCESS 2007.
THANKING YOU

 

[Jeff Boyce]

?You have a table named [Profit] that contains fields named [FirstName] and
[LastName]?

I suspect that this table is copied over from a spreadsheet. So what?, you
ask?

Access is a relational database. If you feed it 'sheet data, both you and
Access have to struggle to do easy things (easy if your data is
well-normalized, as Access uses that).

If you want assistance re-examining the data structure (and hopefully
simplifying what you are trying to accomplish), post back a bit more
detailed description of what you are trying to accomplish, and what data you
are working with.

If the ONLY data you have, in total, is what you've described in [Profit],
you might be able to stick that in Excel and filter it.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP