How to deal with Table Links ? 2

[CurtR]

I am still using version 7 Crystal. I have an Oracle table that's holding data from our Hansen System.
The tables I am linking go in a one to many to many relationship.
As long as I stop at the first group of tables (the one to many relationship), all is well, however as soon as I add another table I get a cascade.
Table1 gives me a Service Number
Table2 gives me perhaps 2 orders for this service number.
Table3 gives me 2 mechanics for the service number.
Everything works fine to here. However
Table4 that should give me total parts cost for the service number, gives me that total for each work order. so if there was 4 work orders in the servce number I would get the total parts cost 4 times instead of just once for the service number.

I have turned this everyway I can think of Left join, right join, reversed the link. I'm lost!

What can I do to over come this problem.
Sorry if this is not clear a clear example.
Thanks
Curt

 

[IdoMillet]

If you are joining a single table to two tables with a 1-to-many relationship, you will get a combinatorial set of all possible combinations. If that is the case, you need to re-think your joins (you may need to use a view...).

In any case, to ensure you are summing part costs only once for each service number, you can create a Running Total
that sums the part costs with an "Evalaute" option set to
"On Change of Field:" Service_N

Cheers,
- Ido
ixm7@psu.edu

 

[CurtR]

Thank You!
I am already using running totals to evaluate the totals.
I have a total of 17 tables in this report, and everything is good until I add one more layer to this mix, to give the costs.
Perhaps I will have to get a view created and use that.
I have tried everything else I can think of to join these tables together.
Thanks again
Curt

 

[kenhamady]

What you are experiencing is the nature of combining multiple tables into 1 table for reporting purposes.

When your service number finds 2 mechanics and 2 orders then your original service number record is inflated into 4 records (2x2). Each of those will link to the new table, bringing the cost record in 4 times. You can total it using a running total set to 'evaluate' once per service number, or you can bring in costs with a linked subreport so that it doesn't get inflated by the original report. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[CurtR]

I know that I need to push back and get away from this for awhile cause my mind is clouded! Deadlines !
I have created a sub report that does the miles. THe main report does the costs, so seperatly they both run fine. I created the same date range parameters as I did for the main report in the sub report. Start and End dates. I have read the threads on this, there is a reference giude on my lap and I still cannot link this sub report to the main correctly.
The main report table date could be any date. Where as the sub report table only has a weekly date (Sundays). So what I THINK I need is an " if sub report {?Start} date in main report {?Start} date to main Report {?End} Date and sub report {?End} date in main report {?End} date to main Report {?End} Date then .... type statement
Anybody got any ideas?

 

[kenhamady]

Do you want the subreport to only print the Sunday between the Start and End? Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[CurtR]

Actually what These are is the odometer readings from the vehicles. I need the sub report to get the max and the min for the report period and then subtract those to get what I need which is the actual mileage for the period.

 

[kenhamady]

Max and min counting only the records (Sundays) between your start and end dates? Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[CurtR]

Yes, that's what need to do. Once I get the max and min between the start and end dates , I can calculate the actual miles traveled.

 

[kenhamady]

Use the date range in the suberport so that it only selects the dates (sundays) between the Start and End Date, and for each date show the mileage. Then within the subreport calculate the Min and Max of the mileage as two grand totals. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[CurtR]

Thanks Ken (Again)
That works, now I just need to get a view created so that I can do this without a sub report. If I can figure out how to pass the dates? anyway thanks again!