Finding missing data in months

[pinkpoppy]

This is an example of my database. We keep track of how customers pay for products that are on a monthly basis. Customers are required to pay every month.
I need to find which State, Customer(s) have not paid for a certain month. This report will need to run every month, so I will enter parameter values, but I need to know how I can display the state, customers(s) that have not paid for a certain month?

Table: Customers
CustomerID, State, City, FirstName, LastName, Email

Table: Orders
OrderID, ConfirmationID, CustomerID, Month, Day, Year

Table: Payments
PaymentID, PaymentDate, Visa, MasterCard, Paypal, Discover

Example data:

PaymentID   PaymentDate  Visa   MasteCard  Paypal  Discover

001        01/01/2013     y        n           n       n
002        02/05/2013     n        n           y       n

Table: CustomerPayment_Cross
ID, CustomerID, PaymentID

 

[hilfy]

Try this:

Left join from Customers to CustomerPayment-Cross and then left join again from CustomerPayment_Cross to Payments. Do this by right-clicking on the line that shows the join between the tables, selecting something like "Join Options" (I don't have Crystal open and I don't remember the exact wording...) and then marking the join as "left outer".

In your selection formula, add something like the following (assuming that the month you're looking for is in a parameter called {?Month}):

(IsNull({Payments.PaymentDate}) or (Month({Payments.PaymentDate}) = {?Month}))
and IsNull({Payments.PaymentID})

A left join will get you all of the customers regardless of whether they have a payment. The first part of the selection criteria makes sure that you're only looking at the month you're searching for and the second part makes sure that you have just the customers who don't have a payment in that month.

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com