how to compare two rows in one mdb table?

I have one mdb table with the following structure:

• Field1 Field2 Field3 Field4
• A ...
• B ...

I try to use a query to list all the different fields of row A and B in a result-set:

SELECT * From Table1
WHERE Field1 = 'A'
UNION 
SELECT开发者_StackOverflow社区 * From Table1
WHERE Field1 = 'B';

However this query has two problems:

1. it list all the fields including the identical cells, with a large table

2. it gives out an error message: too many fields defined.

How could i get around these issues?

Is it not easiest to just select all fields needed from the table, based on the Field1 value and group on the values needed?

SELECT field1, field2,...field195
FROM Table1
WHERE field1 = 'A' or field1 = 'B'
GROUP BY field1, field2, ....field195

Oh and for the group by statement as well as the SELECT part, indeed use the previously mentioned edit mode for the query. There you can add all fields (by selecting them in the table and dragging them down) that are needed in the result, then click the 'totals' button in the ribbon to add the group by- statements for all. Then you only have to add the Where-clause and you are done.

Now that the question is more clear (you want the query to select fields instead of records based on the particular requirements), I'll have to change my answer to:

This is not possible. (untill proven otherwise) ;)

As far as I know, a query is used to select records using for example the where clause, never used to determine which fields should be shown depending on a certain criterium.

One thing that MIGHT help in this case is to look at the database design. Are those tables correctly made? Suppose you have 190 of those fields that are merely details of the main data. You could separate this in another table, so you have a main table and details table. The details table could look something like:

 ID   ID_Main   Det_desc   Det_value

This way you can filter all Detail values that are equal between the two main values A and B using something like:

Select a.det_desc, a.det_value, b.det_value
(Select Det_desc, det_value
from tblDetails
where id_main = a) as A inner join
(Select Det_desc, det_value
from tblDetails
where id_main = a) as B
on A.det_desc = B.det_desc and A.det_value <> B.det_value

This you can join with your main table again if needed.

You can full join the table on itself, matching identical rows. Then you can filter on mismatches if one of the two join parts is null. For example:

select  *
from    (
        select  *
        from    Table1
        where   Field1 = 'A'
        ) A
full join
        (
        select  *
        from    Table1
        where   Field1 = 'B'
        ) B
on      A.Field2 = B.Field2
        and A.Field3 = B.Field3
where   A.Field1 is null 
        or B.Field1 is null

If you have 200 fields, ask Access to generate the column list by creating a query in design view. Switch to SQL view and copy/paste. An editor with column mode (like UltraEdit) will help create the query.