INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Linking Tables In Crystal

Linking Tables In Crystal

(OP)
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!

RE: Linking Tables In Crystal

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.

RE: Linking Tables In Crystal

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

RE: Linking Tables In Crystal

It what way is it incorrect?

What does your select statement look like?

RE: Linking Tables In Crystal

(OP)
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

RE: Linking Tables In Crystal

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

RE: Linking Tables In Crystal

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

RE: Linking Tables In 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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close