Finding & Summing from two related dbs

[LaRetta]

Hi Everyone! Well, it's my day to need help! My boss has just requested a report and I'm not sure how to obtain the required information.

I have a Services db. I need to find all Services date with ServiceDate 10/1/2002...10/31/2002. I have a Clients db related to Services on ClientID. AND, I have a Contracts db with multiple contracts per Client, also related to Clients on ClientID.

These contracts are date-ranged (FromDate and ToDate) and have a total contract dollar amount. I need to find all contracts matching the related Services -- if there is a Contract within the valid date range of 10/1/2002...10/31/2002. Oh, I hope this makes sense.

I then need a report which summarizes by Client ... ContractTotalDollarsAllowed minus SumOfServices for that same date range, and *remaining* dollars left on the Contract. I have planned to learn SmartRanges but haven't done so yet. Is there any way to accomplish this task? I'm sure there probably is, so my question really should be ... what is the BEST way to accomplish this? Since my boss is breathing down my neck, I'll be checking back frequently, hoping someone can help me

[BobWeaver]

I think you need to generate the report fromt the Services file.

You probably have to do this in steps. Eventually, you can make a script for it if you are going to be generating this report regularly.

Since you are just generating a report, I doubt that you would need to use smart ranges. It's intended for using range relationships in more dynamic display situations (ie portals).

Just do a find in the Services file in the date field for the range of dates 10/1/2002...10/31/2002.

You will need to make one or two calculated fields to help sort and summarize. However, I'm not certain from your description about the summarizing whether you are talking about three separate reports, or one report sorted by 3 criteria. The reason I'm confused is that if you have "ContractTotalDollarsAllowed minus SumOfServices for that same date range" in your sort order, then follow it with "*remaining* dollars left on the Contract", this last item won't have any effect on the sort order or summary. So, I'm guessing that you need at least two reports.

In general, for everything that you need to summarize or sort by, you need a field. To get "ContractTotalDollarsAllowed minus SumOfServices" it will have to be a calculated field with a formula like:

Contracts::AllowedAmount-GetSummary(ServiceAmountTotal,ClientID)

where ServiceAmountTotal is a summary field total of service costs.

This is pretty sketchy, but hopefully helps.

[BobWeaver]

Just another thought. Since you appear to be generating a report for a specific month, you can avoid the range issue by making a calculated field 'MonthYear' with the formula:

MonthName(ServiceDate) & ", " & Year(ServiceDate)

so it will have a value of "October, 2002" for all records in the range you want.

Then you can have a relationship based on this field. That may simplify the summarizing somewhat.

[LaRetta]

Thanks for the ideas Bob. I would like one report simply showing Client Name, Original Contract Max Dollars, minus SumServices (within that contract date range) = Balance remaining on contract.

I can get the ServiceDateRange easily with a calc as you suggested. I started with ServiceDate>=10/1/2002 & ServiceDate<=10/31/2002

I can even create a left calc key unstored using the above & ClientID

My problem is the right side, I know it must be indexed right? -- how to combine the dates from the Contract date range to be sure they are 'within' the range - they won't RELATE to the left Key.

Contracts key: ContractsFromDate >=10/1/2002 and ContractsToDate <=10/31/2002&ClientID -- just doesn't match up right I will keep working on this, as you've given me some more good ideas to try! Thanks Bob! The Contract DateRanges extend over several months, for instance 7/1/2002...10/16/2002.

You know, this would be a good time to understand how to take a range of dates, and expand them into a multi-key field, huh! Wish I would have learned Ray's Range Finder demo thing!

LaRetta

[BobWeaver]

If you need to generate a field for the right side of the relationship, maybe the easiest thing is to use a script with a loop to set the field, (or the ever popular Replace command).

[BobWeaver]

Oh yes. Another thing you can do is use a lookup field instead of a related field, since you can set up the lookups to use 'next lower value' which, essentially, is a range.