Jump to content
Server Maintenance This Week. ×

Finding & Summing from two related dbs


LaRetta

This topic is 7820 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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 crazy.gif

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 confused.gif 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! ooo.gif Wish I would have learned Ray's Range Finder demo thing!

LaRetta

Link to comment
Share on other sites

This topic is 7820 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.