Credit card fraud

[morlan]

Hi all,

Heres an interesting one,

Some of our Sales people are sumbitting duplicate credit cards applications to us. They are submitting an application for a blue card and a red card for the customer (hence they get paid twice). This is against the rules and is hard for us to detect.

Here is a sample of the type of duplicated I'm trying to filter:

tblMain
----------
SalesExec | CardType | CustName | CustDOB
=================================
John Harrit | RedCard | Joyce | 03/05/69
John Harrit | BlueCard | Joyce | 03/05/69


This is clearly a duplicate because CustName is the same and CustDOB is the same and it has been taken by the same SalesExec.

The SalesExec has signed the customer up for two credit cards even only one is allowed per customer.

I need to run a clever query that can detect this.

I have a few different ideas but niether of them seem to do what I want. Any concepts welcome.

 

[ColinEssex]

At first glance it looks like you need to focus on the construction of your database.

It appears that you have one table (tblMain) which holds the main data. What you'll need to do is have one table for customers and one for the transactions of the customers.

What you can then do is have a "CustomerData" form which will show (as a subform) all the transactions that the customer has clocked up so far. Then you can have an "Enter New Transaction" button and a new transaction screen will open up and at various points (like date of transaction) a check can be made to ensure you're not repeating stuff.

In addition, when the staff member looks at the customer screen, a visual check can be made at that point by viewing the subform data.

You will then have a "One-to-Many" relationship. (One customer can have many transactions)

Col