Join problems ?? data spewing out doubled up info

[Pshaw]

Hi
I'm pretty new at using Crystal and am having problems with this report I'm trying to create.
I'm using Crystal 10, connecting to my database using ODCB

The data i have is on two tables: e.g.

Table 1 Table2
Item ItemNo
Act Sales$ Cost1
Fcst Sales$
Act qty
Fcst Qty

I'm linking table 1 and table two by Item -> Item No by outerjoin...
tried it with inner joins but it is spewing out the data twice
I've tried it with a combination of joins and its still spewing out the data twice.
I'm using a cross tab for this...but also did try using a on a drill down...
the only problem is the drill down can't handle how I want the data to look like which is:

Item No Jan 04 Feb 04
3600 Actual Sales
Forecast Sales
Actual Units
Forecast units
Actual Cost
Fcst Cost
4500 Actual Sales
Forecast Sales
Actual Units
Forecast units
Actual Cost
Fcst Cost
etc
Using the cross tab works for the "actuals" but it then screws up the forecast and the cost...it seems to be multiplying the cost by the count (forecast sales)

Sighhhh confusedddddddddd....

 

[IanWaterman]

Do you have a data base analysis tool, if yes find out how many records you have in table 2 for each item. Looks like you have two records.

If you do not have another tool, build a crystal report looking at table 2 only and use select expert to show only data for item 3600. You may see 2 records, you will then need to find out which field causes the duplicate and then use that to restrict records from table 2 to 1 per item.

Ian

 

[Pshaw]

Hi Ian,
Sorry...been playing with it and have made some improvement!
I figured out the forecast record is dependent on whether there was an actual sale...if an "actual" sale was made for the month, it will pick up the forecasted qty, if actual sale was not made, then it will default to zero even though there is a forecasted qty.....
but at least now the forecast qty that the table picks up is 100% correct!

my tables at the moment are:

Forecast Actual
Table Table
ItemNo --- LOJ ---- Item


and i've got a formula for fcst qty:

if (itempd <= 7) then {actqty} else {fcstqty1}

as i want the table to pick up forecast qty only if item period is greater than 6 else it must pick up actual qty

actual qty is just {actqty}

 

[Pshaw]

sorry...didnt quite finished asking my question...
so how do i go about it getting the table to pick up forecast qty regardless if sales is made or not?

 

[IanWaterman]

where are you picking up ItemPD, if it is from actual table then it will override your LOJ.

If you can not get it from Forecast table change your formula to deal with the Null created by your left outer join.

if isnull(itempd) then {fcstqty1} else
if (itempd <= 7) then {actqty} else {fcstqty1}

Ian