Before I waste a lot of time figuring this out mysef can someone just

[straytoasters]

Ok, I have a simple Access database (and I mean simple), but large. I need to get a report/query that will produce only results where column 1 and 2 are the same but column three is different or blank (if this requre's two differen searches...thats fine)...then again blank might be considered "different" and thats fine too. If this can be done, can it be done with the wizards or would it require some extensive scripting? Access is not one of my preferred Office products but I am willing to learn a bit to get this done. Also if this is easier in Excel the "database" can be made into that instead.

Straytoasters

So dont sound like a whiner...the tag line was suppose to end "...can someone just tell me if this is possible" not ...can someone just (tell me how to do this), as it may look. B)

Straytoasters

[Scooter513]

This can be accomplished by adding 2 Expression Fields to your Query, and then limiting the results based on the Criteria of those fields.

The information below is based on a table with 3 fields named A, B and C

Build the Query like normal with the necessary fields

In blank columns, build the following Expressions by typing the following information in the Field and Criteria fields.

[A]=

-1

=[C]

<>-1 Or Is Null

When you enter the formula in the field, it will look like this ‘Expr1: [a]=’. You can change the text before the : to whatever you want the filed to be named in your Query (Example Test1: [A]=)

What this will do is show any rows were A and B are the same (-1 = True) AND where B and C are not equal or when it is not able to compute (Blank Fields) --> see image for example

Hope this helps

Edited March 29, 2006 by Scooter513

[straytoasters]

I dont think this is exactly what I want...I should have explained better. What I need is a search that will take the data in row 1, column 1 & 2 and search the whole database and if it finds a match see if row 1 column 3 is the same. If its the same then leave it and move on, if not then output the reference data and the (possible) duplicate so it can be verified later.

Straytoasters

[Scooter513]

This query will search every row in the database individually, and return results based on that specific row.

Unless I am completely misunderstanding what you are asking for, the information above should do what you needed it to (I have added an image with the detials to help show what it does).

My sample checks the data in column 3 against 2, not 3 against 1 as you just listed, but based on the fact that it is only checks column 3 IF 1 and 2 are the same, it shouldn't matter which column you use for that check.

If this is really not what you are looking for, please post a sample of your Table Structure, Sample Data and Expected Results so I can better understand what you need.

Thanks,

Scott

Edited March 29, 2006 by Scooter513