unmatched records from two tables help

[tonygr]

Hi all,
I am Using Crystal Developer xi, SQL server 2000
I am trying to show unmatched records from two tables, but the selection criteria is based on both tables.
I have a left outer join from table2(Visits) to table1(Members) linked via the member number fields
the following works!

{MEMBERS.EXPIRY} > currentdate and
{MEMBERS.TYPE} in ["A","b","c"]and
isnull({VISITS.MEMBER})

But when I add the following No records are shown

and
{VISITS.DATE} in Over90Days

I am trying to show members that have not visited us in the last 3 months grouped by month.

Can anybody help with this?
any help would be appreciated.
Tony

 

[synapsevampire]

You need to make up your mind, do you want
isnull({VISITS.MEMBER}) or {VISITS.DATE} in Over90Days?

You can't have both, right? How could the child not exist AND have a value?

The solution most will offer here is to use a subreport, but please try to avoid that, it's klugdy, promotes laziness and inefficiencies.

Instead of using tables in Crystal, you might use a Command Object (listed under your data connection as Add Command).

Then place a query which already does the check for unmatched in there, such as:

SELECT members.type, visits.date
FROM members LEFT JOIN
(select visits.member from visits where visit.date <= getdate() - 90) visits
ON members.member = visits.member
WHERE
members.expiry > getdate()
and
members.type in ('A','b','c')

Doing this from memory so it might require some minor tweaking yet.

Now you have the results performed by the database how you'd like. You could also peform the grouping within the SQL.

Better still would be to create database onjects to accomplish this, such as Views or SPs.

Your post is too generic so it's hard to give you quality help. You give NO indication of what you intend to output or where, so the complexity may grow, so I also suggest that you take the time to explain requirements beyond finding unmatched, at least until you get a solid grasp of SQL and Crystal.

Not to worry, what you're experiencing is a VERY common growing pain for Parent->Child relationships and Left Outer joins

-k

 

[tonygr]

Thanks Ver Much! synapsevampire
I took your advise and spoke to a friend who then helped me create a view on the SQL server for the first part.
It looks at the customer visits and datediff to the last visit.I have then used crystal to group into months, using a simple formular.
This shows customers that hasnt visited in i.e.
1 month 90 customers
2 months 60 customers
3 months 30 customers
Thanks again for keeping me right.
Regards
Tony

 

[synapsevampire]

Nae worries, the learning experience should have proven an excellent growth step, you owe your friend a decent martini and she/he should return the favor with a large bottle of migraine medication now that the Pandora's box has been opened

-k