query w/ filter, but display all records

[jasminewhite]

My DB table:
FIELD1 FIELD2 other fields
1 FY11 other-values
1 FY12 other-value
2 FY12 other-value
3 FY11 other-value

display results:
FIELD1 FIELD2 other fields
1 FY11 values
2 FY11 N/A
3 FY11 values

for the records that don't satisfy filter condition, display its FIELD1 & FIELD2 value, but set the rest to N/A, instead of skipping the entire record from the result display.
Please show me how to achieve this algorithm:
For each value of FIELD1, do this: if FIELD2 is FY11, print the rest of this record, else print N/A for the rest of this record

Please show me how to do this using any ACCESS mechanism.
Thank you in advance

 

[jasminewhite]

To all SQL experts out there,
How do I implement the following logic:
Query1 (no filter)
For each value of FIELD1: if FIELD2 = FY11 then count FIELD3 as FIELD3count. Note that DB is sorted by FIELD1. For a group of FIELD1 records that does not have any FY11, FIELD3count will be 0 for that group.

Query2 (no DB query at all)
For each value in this set [1, 2, 3]: If FIELD3count of Query1 is 0, display N/A for the rest of this record, else display FIELD3count. The set [1, 2, 3] is a constant set representing all values in FIELD1 of DB table.

 

[plog]

Your initial post is a little confusing and then your follow up makes it even worse.

In the initial post you say you want to 'filter' your table on Field2, except your definition of filter is to not completely exclude rows, but display 'N/a' for its fields other than Field1 or Field2. Confusing, but I am with you. Until you provide sample data that shows 4 records in your table that when run through this query you want to create it outputs only 3 records. How come the second example record in your query isn't represented in the query data? If your not actually excluding rows in this query then what happened to the record where Field1=1 and Field2=FY 12?

In the second post is Query 1 an aggregate query? That is do you want to show Field1 and then another field which Sums all the Field3 values? If not, show me some sample data.

Since Query 2 is based on Query 1, I'll hold off my questions until I better understand Query1.