Limiting Results to Items Not In Another Table. 1

[JARNDT]

I am trying to get a list of my vendors for which we have not paid anything to in the last year. This way, I can see the vendors which need to be deleted. I am using my master vendor table which lists all vendors and I want to create a formula that will only list vendors from the master vendor table which DO NOT show up on my table of vendor payments. Can anyone point me in the right direction? Thanks.

 

[Transcend]

Select vendorid from mastervendortable
where vendorid not in
(Select vendorid from vendorpaymenttable)

Transcend

 

[Naith]

Transcend makes a good point; it would be nice to deal with this in a query before the report gets it's hands on it. A couple of SQL statements using the minus function would set you straight.

However, if you have to draw all the records in the report and don't mind separating the dross records there, then assuming you have a left outer join between MasterVendor and VendorPayments, apply a conditional suppression on the details section where MasterVendor.ID = VendorPayments.ID.

You should be left with the vendors in the master table with no corresponding payment records.

Naith

 

[kenhamady]

The problem is the "in the last year". It would be simpler if it was those not paid "ever".

I think you will have to do a left outer join from vendor to payments, and put no criteria on the payments table. Then group on Vendor and add a summary field to each vendor that is the Maximum of the Payment Date. Last, add a Group Selection Formula that is:

Maximum ({date},{vendor}) < Date (2002,1,1)

It would be nice if you had a field in Vendor which stored the last payment date. That would make it really easy. Some systems store that. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[SmpTraining]

My suggestion is a slight variation on Ken's.

1. Use a left outer join to link Vendors to Payments
2. Group on Vendor
3. Select all records
4. Supress all details
5. Supress Group Footer
6. Sort records by payment date (so latest payement comes top)
7. Suppress the GH (Vendor) if the payment date falls within the last year.

This works because the 'latest' payment date will be accessible in the GH (Vendor) section. If there are NO payments, the date will be NULL.

Have you considered NEW vendors who perhaps haven't been on the system for a year?
Steve Phillips, Crystal Consultant

 

[kenhamady]

SMPhillips variation will work pretty much the same, but has one disadvantage if you are trying to do any Grand Totals. You would have to use conditional totals to get grand totals, which adds an extra step.

By using the Group Selection technique you can use any automatic running total to get accurate Grand Total. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[JARNDT]

Thanks for everyone's help. I ended up using Transcend's sql statement because I'm not that good on Crystal and I got what I needed.