Summarizing Data across multiple tables

[Goetch]

Hi I am having trouble making a report for my file.  In a nutshell I have These Tables "Written Sales", "Sales People" , "Salesperson hours", and "Sales Person Goals".  I am pretty happy with my yearly report /  comparison, but now I want to show the same data by salesperson then by month.  I tried using sub summaries but have not been able to get all the data needed for the report this way, namley my hourly productivity and goals.  I have attached a copy of the file I am working on for your viewing pleasure.   The file is pretty basic, and I am a novice so please be easy on the crtiticisim, but any and all comments are appreciated!!!  I am sure I am just not seeing the easy answer here.

 

 

Here is what I want to accomplish with the current file.

 

 

Writtensales copy.fmp12.zip

[dansmith65]

I always prefer using a calculated date field that returns the first day of the month, rather than the separate fields you have now: Month Name, Month of Sale, Month Sort, Year of Sale.

 

I also created a new "Months" table and populated it with a few years worth of data. Records will need to be created in this table as time goes by. I put a few related fields on the Months layout to give you an idea of how to accomplish what you need.

Writtensales copy DS.zip

[Goetch]

Dan,

    Thank you for your suggestion, I think I will start using the calculated date feild as you suggested.  However, your sample file didn't address what I was really going for, and I am sure that is becasue the image I uploaded didn't show the whole report.  The Report I was looking to generate was to sort by salesperson and summarize the sales person's sales by month.  Usually we only display a year at a time on this report.  However I am able to get the report I want by using many to many relationships with the sales table, however, I thought a many to many relationship was a no no.. 

 

Ideally I would like to be able to get the same data on the comparison report drilled down to a monthly format, however, I think I would need to use a different technique to accomplish this.  Anyway if anyone can shed some light about using many to many relationships in this way it would be appreciated.  I attached the file.

Writtensales copy DS 2.fmp12.zip