Reporting against targets with results generated from multiple tables.

I suggest this.
Create 8 date fields representing the quarter period starts for the 4 quarters in the current year and the 4 next year.
Create 8 more formula fields to be the last day of those quarters.

e.g. [Q1 end] would be 
LastDayOfMonth(AdjustMonth([Q1 Start],2)

Create 8 more fields for the Quarter targets.

The reasons I suggest 8 field sets is that you you have the data for a full 2 year period.

Lookup those Q1 starts and Q1 ends etc down to the detail data.

Make 8 summary fields (well get one working and then copy it 7 times.)
to capture sales within that period.

That should do it, once you get those 8 field set lines up nicely on the form.

Basically Get Q1 working and then replicate the whole setup 7 more times.

Thank you Coach


Would the fields above be created in the targets table or would an additional table be required? The targets remain consistent over the full period, would one still require the additional 8 fields for the quarter targets?

The fields would be created on the supplier table.  I imagine that the targets would change from one year to the next, so I just thought it would be easiest to load the Targets for each quarter even if its the same number for each quarter for that year.