Linking Tables In Crystal

[wanzek]

My report was working perfectly until I needed to add in another table. The first table has one record for each phase code per month like below:

Mth Phase Amt
5/1/2016 1604. 61,056.60
6/1/2016 1604. 0.00
7/1/2016 1604. 0.00

The table I am trying to add has multiple lines for each month like below:

Mth Phase Amt
5/1/2016 1604. 26,292.30
5/1/2016 1604. 16,210.80
5/1/2016 1604. 18,553.50

When I link the 2 tables together my first table does this:
Mth Phase Amt
5/1/2016 1604. 61,056.60
5/1/2016 1604. 61,056.60
5/1/2016 1604. 61,056.60
6/1/2016 1604. 0.00
7/1/2016 1604. 0.00

It repeats the month 5/1/2016 3 times because the 2nd table has 3 rows of data for 5/1/2016 for that phase. Is there a way around this? Here is my current link:

Table 1: Table:2
Mth UIMth
JCCo JCCO
Job Job
PhaseGroup Phase Group
Phase Phase
Cost Type JCCType

I am using a left outer join.

Thanks!

 

[Charliy]

create a formula like TOTEXT({table.mth},"yyyyMMdd")&' '&{table.phase}

Group on that formula
Suppress the Detail and Group Header
Put everything in the Group Footer.

 

[wanzek]

That didn't solve my problem. The incorrect data is now just summarized under the group heading.

 

[Charliy]

It what way is it incorrect?

What does your select statement look like?

 

[wanzek]

The first table has one record for each phase code per month like below:

Mth Phase Amt
5/1/2016 1604. 61,056.60
6/1/2016 1604. 0.00
7/1/2016 1604. 0.00

The table I am trying to add has multiple lines for each month like below:

Mth Phase Amt
5/1/2016 1604. 26,292.30
5/1/2016 1604. 16,210.80
5/1/2016 1604. 18,553.50

When I link the 2 tables together my first table does this:
Mth Phase Amt
5/1/2016 1604. 61,056.60
5/1/2016 1604. 61,056.60
5/1/2016 1604. 61,056.60
6/1/2016 1604. 0.00
7/1/2016 1604. 0.00

When I add in the 2nd table the one number from the month 61,056.60 is repeated 3 times because the 2nd table has 3 lines for one month. The first table should only have 1 amount for May but it repeats based on table 2 because table 2 can have multiple lines per month.

Here is my query:
SELECT "JCCP"."JCCo", "JCCP"."Job", "JCCP"."CostType", "JCCP"."Phase", "JCCP"."Mth", "JCCP"."ActualCost", "APUL"."GrossAmt"
FROM "Viewpoint"."dbo"."JCCP" "JCCP" LEFT OUTER JOIN "Viewpoint"."dbo"."APUL" "APUL" ON (((("JCCP"."Job"="APUL"."Job") AND ("JCCP"."PhaseGroup"="APUL"."PhaseGroup")) AND ("JCCP"."Phase"="APUL"."Phase")) AND ("JCCP"."CostType"="APUL"."JCCType")) AND ("JCCP"."Mth"="APUL"."UIMth")
WHERE "JCCP"."JCCo"=1 AND "JCCP"."Mth"<{ts '2016-08-02 00:00:00'} AND "JCCP"."Job"=' 1098.'
ORDER BY "JCCP"."JCCo", "JCCP"."Job", "JCCP"."CostType"


JCCP = 1st table in reference
APUL = 2nd table in reference

 

[Charliy]

Oh, you're doing SQL Command - I'm out.

 

[wanzek]

No, I am just using Crystal to create the query. You can copy the query from Crystal.

 

[mrdjsm1th]

Based on the information provided, it appears you will need to group by Mth and Phase and use an aggregate function like MIN, MAX or AVG on "JCCP"."ActualCost", and a SUM function on "APUL"."GrossAmt" in order to handle the many APUL records per JCCP record.