Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to find records in main file that have no entry of particular type

Status
Not open for further replies.

mac524

Technical User
Jun 9, 2000
3
US
I have a database that has two files. The relationship is one to many. The sales analogy works good here. Assume that the first file has the name of the customer in it, and a key field to link it (by customer) to the second file (nothing else is necessary for the example). The second file has the orders with fields that include the amount of the purchase, and the item purchased, and a key field that links it to the first file.

How can I get a list of all the customers that have never purchased a hammer? The problem I am having is that the record selection formula compares each purchase. Therefore, when I write

not(purchase.item = hammer)

I get customers that purchased anything other than a hammer, even if one of their purchases was a hammer. I need a way to say "only show me the customers where NONE of the purchases are hammers."

There just has to be a way to use the selection formula on the second file as a whole.

This has to be easy to do, but I am not a professional programmer and I have spent hours trying to figure this out. Any help is appreciated.

 
Use a left join from the customer table to the order table, with no record selection criteria using fields from the order table. Then create a formula {@Hammer}:

if isnull({table.item}} or
{table.item} <> "Hammer" then 0 else 1

Then go to report->edit selection formula->GROUP and enter:

sum({@Hammer},{table.customer}) = 0

This assumes you have a group on {table.customer}.

-LB
 
LB -

I tried this. I am still getting a line for each order, however. Though the "hammer" order lines are not showing up. What this means is if "Adam" ordered 100 items (99 of which were not hammers) then I get 99 line items for "Adam." I can of course suppress duplicates - but that still leaves "Adam" showing up even though he did purchase a "hammer."

The thread before this had a similar question and the suggestion there was to use the Sum field in the Record Selection criteria, but I get an error with Crystal 8.5 when I try to do that.

-mac524
 
disregard that second post from me.... I had a statement in the record selection formula that I forgot to remove. I think this is working after all. Thank you so much!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top