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 Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Linking Tables In Crystal

Status
Not open for further replies.

wanzek

Technical User
Mar 8, 2010
58
US
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!

 
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.
 
That didn't solve my problem. The incorrect data is now just summarized under the group heading.
 
It what way is it incorrect?

What does your select statement look like?
 
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

 
No, I am just using Crystal to create the query. You can copy the query from Crystal.
 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top