help with links

[Tracey]

hi

i have 6 tables linked to my report, a company table, a machine table (one company has many machines)
a rate table linked to the machine table with a junction table - MachineRate. (each machine has various rates which change every month)
a FinancialYear table linked to machine via MachineValue junction table. (each machine has a different value for each given finacial year)

My problem is, that i am getting records for what seems to be EVERY financial year in the table.

selection criteria of main report:
{COMPANY.COMPANYID} = {?cID} and
{ENTRY.ENTRYDATE} >= {?LowDate} and
{ENTRY.ENTRYDATE} <= {?HighDate} and
{?LowDate} in {FINANCIALYEAR.BEGINDATE} to {FINANCIALYEAR.ENDDATE}

selection criteria on subreport:
{COMPANY.COMPANYID} = {?Pm-COMPANY.COMPANYID} and
{ENTRY.ENTRYDATE} = {?Pm-ENTRY.ENTRYDATE} and
{RATE.RATEMONTH} = {?Pm-RATE.RATEMONTH} and
month({ENTRY.ENTRYDATE}) = {RATE.RATEMONTH} and
{RATE.RATEYEAR} = {?Pm-RATE.RATEYEAR} and
year({ENTRY.ENTRYDATE}) = {RATE.RATEYEAR}

i have tried changing the link from machine to MachineValue to a left outer to no avail (also tried right outer, less than etc)

I know it must have something to do with my links... is it obvious to anyone what i need to do??

 

[rosemaryl]

I think, and I could be wrong, but from your data it seems that Crystal doesn't know which rates need to be applied for a month. What designates the applicable rate on any given month? A field with rate for each month? or ?

Provide a little more information and maybe we can solve.

ro Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[Tracey]

the rates are ok, a field in the table holds the month integer and onother holds the year integer.

my problem is with the values, which are against year ranges.

i have narrowed it down to the correct record now, by using the criteria:

main report:
{COMPANY.COMPANYID} = {?cID} and
{ENTRY.ENTRYDATE} = {?DateRange}

subReport:
{COMPANY.COMPANYID} = {?Pm-COMPANY.COMPANYID} and
{ENTRY.ENTRYDATE} in {?Pm-?DateRange} and
month({ENTRY.ENTRYDATE}) = {RATE.RATEMONTH} and
year({ENTRY.ENTRYDATE}) = {RATE.RATEYEAR} and
minimum({?Pm-?DateRange}) in {FINANCIALYEAR.BEGINDATE}to{FINANCIALYEAR.ENDDATE}

still working on a more accurate formula.
so it wasnt my links after all.. thats a start