Displaying records if isnull or Criteria is met

[stem]

I sincerely hope this is just a systax thing...here goes:

I have 2 tables:

parts
=====
partnum descr type
------- ----- -----
A0001 Part1 STxxx
A0002 Part2 FGxxx
A0003 Part3 FGxxx
etc...

pricelist
=========
partnum listname price
------- -------- -----
A0001 A0001a 5.10
A0001 A0001b 1.00
A0001 A0001c 2.00
A0002 A0002a 10.00
A0002 A0002c 3.00
A0003 A0003a 6.00
A0003 A0003b 0.00
A0003 A0003c 1.50
etc...

Report needs to show only parts.type = FG and
1) All parts with a missing A____b pricelist.listname
2) All parts with price = 0.00 for pricelist.listname = A____b
[(1) & (2) must appear in 1 line]

I have LEFT OUTER JOIN'ed parts to pricelist. My selection Criteria:

left({parts.type},2) = "FG" and
(isnull({pricelist.listname})
OR (mid({pricelist.listname},6,1) = "b" and
{parts.partnum} = {pricelist.partnum} and
{pricelist.price} = 0.00))

PROBLEM:
========
Crystal does not return any parts where the A____b record is missing!
Can anyone help me please



Dirk S
dirk@dol.dot.ie

 

[synapsevampire]

Try removing this: {parts.partnum} = {pricelist.partnum}

-k

 

[lbass]

I would just use for the record select:

left({parts.type},2) = "FG"

I'm not sure you really need a left join. Are there any instances where you have a part number which has no corresponding record in the price list? If not, then just do an equal join.

Group on {partnumber}, and then create a formula {@bstatus}:

if right({pricelist.listname},1) <> &quot;b&quot; or
(right({pricelist.listname},1) = &quot;b&quot; and
{pricelist.price} = 0) then 0 else 1

Then add a group select statement:

sum({@bstatus},{partnumber})= 0

If you need to identify which parts fall into which category, then also do two other formulas:

if right({pricelist.listname},1) <> &quot;b&quot; then 0 else 1//call this {@nob}

if (right({pricelist.listname},1) = &quot;b&quot; and
{pricelist.price} = 0) then 0 else 1//call this {@zero}

Then sum each:

if sum({@nob},{partnumber}) = 0 then &quot;Missing 'b' Listname&quot; else if sum({@zero},{partnumber}) = 0 then &quot;Listname 'b' with Zero Price&quot; //place this in group header

To order partnumbers by to show all of one result then all of the next, use TopN on one of the groups sums. To place them in one line, as you specified, you'll have to use variables, and someone else could help you with that.

-LB

 

[stem]

Thanks to both of you.

I resorted to using lbass's method to create a subreport (in the report footer) to show the few lines that my main report did not pick up. Works fine except for the part number sequence....




Dirk S
dirk@dol.dot.ie