summary of information in other tables

[thejkfarr]

I am developing a simple invoicing solution, and I need to provide a report that has a summary of information from 2 other tables. For the life of me, I can't seem to figure out how to apply the date range to the calculation.

to simplify: table1 = labor sales, table2 = Item sales (assume each has a date field)

I need to create a report that gives me summaries for a date range, a page that just has "total labor sales for date range" and "total item sales for date range".

Can anyone give me some pointers on how to get the numbers to reflect the date ranges. Perhaps I don't understand how the relationship needs to be setup.

Thanks kindly

[doughemi]

You could use a global StartDate field and a global EndDate field in a separate report table. The relationships should be

ReportTable::StartDate ≤ LaborSales::date

ReportTable::EndDate ≥ LaborSales::date

and

ReportTable::StartDate ≤ ItemSales::date

ReportTable::EndDate ≥ ItemSales::date

Then you can use Go To Related Record script steps to extract the records for the date range and summary fields (total of sales) to add up the sales.

[doughemi]

On further thought:

Is there a reason why you have two separate tables instead of one table with a type field (choice of labor or items). That way reporting is simpler, because you can use subsummary parts after sorting by type to get both reports on the same page.

[thejkfarr]

Your advice worked perfectly!

It's actually much more complicated, there are 8 tables not just 2, I just over simplified it for posting reasons.

Points to Doug!