Not sure why your Start and Finish dates are in the Classification table. I'm going to assume there's good reason for this.
You'll need to construct a different set of relationships to match the date in JobSheet to the dates in Classification first, then link to the Samples table.
JobSheet----<Classification-----<Sites----<Samples ( ---< means one to many )
JobSheet::cDate > Classification::StartDate AND
JobSheet::cDate < Classification::EndDate
cDate has to be a field of type Date. Since DateTime appears to be a timestamp field, a calculation, GetasDate ( DateTime) can be defined for cDate. Then a reference from a JobSheet Record to Samples will only refer to Samples that were taken in the given date range.
That's one possibility, You can use a Filtered Portal to Samples to only show sample records with a matching JSID. This requires FileMaker 11. If you don't have 11, a slightly different approach will be needed.
Thanks again Phil,
The reason I have the Classification if a sepreate table is that they might be up to 10 diffrent classifictation for the same site thoughout the year so I have a portal on the Sites layout for adding how ever many is required. That would be the best way to do this?
I tried the method you mentioned but I couldnt get the results I was after.
However I managed to do what I was after by using some of your solution:-
Creating cDate in the samples tables and adding a Classification TO Related via
Happy Holidays, :)
Sounds like you came up with a better solution. Glad my idea sparked yours.