Filtering Semi Unique Values

[Colinm]

Hey guys, I'm relatively new to Access and I need some help building a query. So I work with a website that handles credit card transactions, and we keep a log of activity ie. NameOnCard, Date, Status. About once a month or so, I do an audit to make sure that all the attempted transactions are successful. Sometimes the server needs to restart, or the users computer has a problem, etc. Typically, someone who's had a failed attempt will have a successful attempt minutes or seconds later, so the NameOnCard is exactly the same, but the Date and Status are different. Right now I've created a query that look for failed attempts and one that looks for successful attempts, but what i want is one that returns only people with failed attempts that don't also have a successful attempt. Everything I've thought should work hasn't. Filtering for unique values doesn't help, because each row is slightly different, and the NameOnCard isn't the primary key. I recently tried this as a column with this in the field value:

Success: IIf([Online Gift Errors]![NameOnCard]=[Online Gift Successes]![NameOnCard],"Success","Failure")

(NOTE:Online Gift Errors and Online Gift Successes are queries, not tables)

But when I try to run the query it thinks [Online Gift Errors]![NameOnCard] is a parametric value and asks for an input. What can I do? Should I be trying something else entirely?

 

[Morten R]

Hi, i think u shud use a crosstable query to solve this.
Beacuse you have all the data in some tables alredy?

 

[Colinm]

As far as I can tell, I don't want a Crosstable Query. I'm not calculating anything, and the most relevant fields are text based. I don't fully get Crosstable Queries, but every attempt at making one with the wizard produces useless results.

 

[gemma-the-husky]

do this in 3 stages

1. do a query to sort out your failed attempts

eg, by card no/date/amount/failed=true

2. then do another query to find successful attempts
eg card no/date/amount/failed=false

3. then do a find unmatched queries to find any failed, with no corresponding success - query wizard will help if you need it.

 

[Colinm]

This sounds like what I want to do, but when I try and run it I get "Property not found." Is this because each entry has a unique ID?

 

[Colinm]

Anyone have any more ideas? I feel like this is possible, just tricky. I think what's making this so difficult is that every attempted transaction is a unique row or data with it's own ID.