Need Help With SQL

[AnonymousJane]

I've created an Inventory report that basically stinks. It was made with a lot of exporting to Excel and doing the counting in Excel instead of CR. Now I need to re-create the WHOLE thing in CR. I'm having trouble doing a count of models stored in certain bin areas. For example, if model "ABCD" is stored between bin '100A001' and '199Z999'. See the layout below:

MODEL QTY IN 100 QTY IN 200 QTY IN 300
ABCD 25 35 10
ABC1 0 25 5

I need to know how many of model "ABCD" is located in that bin range. I need to do this about 5 times (5 different columns) within one report. I tried the SQL Designer but I only come up with all models located between the specified range. I NEED TOTALS per model for that particular range. Perhaps I'm blind to what I'm doing, but I've been at it for almost 2 hours now, and all I get is a bunch of errors and computer crashes.

 

[kenhamady]

Now need to bother with the SQL.

Use a cross-tab, which needs three fields:

The ROWS field would be your Model#.

The COLUMN field will be a formula like the following:

if {bin} in '1' to '2' then "QTY in 100" else
if {bin} in '2' to '3' then "QTY in 200" else
if {bin} in '3' to '4' then "QTY in 300" else
"QTY in 400"

Your summarized field would probably be your model# field using the operation COUNT or DISTINCT COUNT.
Ken Hamady

http://www.kenhamady.com/

On-site custom Crystal Reports Training and Consulting.
Quick Reference Guide to using Crystal in VB.