Query help

[tonydepo]

I have 3 fields as follows: "CardholderNumber", ProcessedTransactionAmount and TransactionStatus

SAMPLE DATA SET:

CardholderNumber ProcessedTransactionAmount TransactionStatus
************0011 ($27.30) Processed
************0013 ($54.00) Processed
************0018 ($80.50) Processed
************0019 ($52.00) Processed
************0019 ($52.00) Rejected
************0020 ($37.00) Rejected

I am only interested if the card has a "Rejected" status with out a correlating Processed status or a single Processed status. So, in the data set example above, I want the end result to show the following via query:

CardholderNumber ProcessedTransactionAmount TransactionStatus
************0020 ($37.00) Rejected

Thank you so much for your help, I know it is late for me and I must be brain dead.

-Tony

[ArviLaanemets]

SELECT * FROM YourTable WHERE TransactionStatus = "Rejected"

[alansidman]

crossposted: https://www.excelforum.com/excel-for...ml#post5587812

[NoellaG]

SELECT * FROM YourTable WHERE TransactionStatus = "Rejected" and CardholderNumber not in(select distinct CardholderNumber from YourTable WHERE TransactionStatus = "Processed")

[tonydepo]

This will show me all rejects and not a true reject. If the cardnumber has a processed and rejected status, I do not want that record to be displayed. Only display a record that only has a rejected status without a correlating Processed status.

[tonydepo]

SELECT * FROM YourTable WHERE TransactionStatus = "Rejected"

This will show me all rejects and not a true reject. If the cardnumber has a processed and rejected status, I do not want that record to be displayed. Only display a record that only has a rejected status without a correlating Processed status.

[Welshgasman]

Post 4 should do what you want?

[tonydepo]

Post 4 should do what you want?

Got it, had to do some tweaking.. Thank you!!

[CJ_London]

just curious - have you considered the situation if the same amount is processed on the same cardnumber more than once? i.e. for two different purchases.

[tonydepo]

I did and also if only a rejected occurred with a different amount and same card . Because right now the SQL is only looking for the cardnumber and if there is a processed and reject and only display rejects. However, it really needs to look for cardnumber, exact amount and then if there is a process and reject. Looks like it needs to be tweaked again, however I turned it over to my developer which he knocked it out, but I really wanted to try it in access.

[CJ_London]

depends on the rules and other data available. For example a timestamp and/or location and/or transaction reference such as an invoice number can give 'proximity' between records. and I would think rejects would precede processed - sometimes my card is rejected on touch because I have exceeded the allowed number of touch payments before I have to insert and complete my pin. So the processed record has a timestamp perhaps a minute later than the rejected record (and not always the same value because having had my card rejected, I think of something else I want to purchase or decide I only want 1 of something rather than 2 before I complete the transaction)

And to add to the mix. Sometimes 'touch' fails due to network or similar issues so you can end up with 2 or 3 rejects before it is finally processed

[tonydepo]

The only thing I have from the cc processor is Date based on a report, but I am going to check on a timestamp again with them. Also, we only have the last 4 digits of a CC due to PCI constraints, so this makes it challenging too. However, not too many records are similar during a given week based on the last 4 digits , card type and dollar amount. Thanks for your input, I really appreciate it.

[Welshgasman]

Yes, I once worked for a bank and we had to check if the customer had been paid PPI refund on their cerdit card, but we only had the last 4 digits to work with.
With the checksum, I have no idea as to whether the last four digits could be duplicated?

[CJ_London]

with last 4 digits there has to be potential for duplication - there can only be 10000 variations, but limited possibility with other limitations such as timescale and amount. If you are selling a single product such as subscription, the possibility increases