One to Many relationships

[shaunacol]

I am trying to produce a report which will outline a person and the amount of business revenue they have created throughout the year. I also want to specify their managers name.

The problem is that these sales reps often change their manager 2 or 3 times a year. Therefore I want to calculate the amount of revenue they created under each manager.

My initial thought was to produce a table which lists the date the sales person changes manager, however if they do this a few times then how do I recognise this in my report?

Any help appreciated

 

[huggybear]

Have you tried grouping on the manager field?

Bear

 

[sspainhour]

It sounds like you problem is that the manager is associated with the sales person only and not linked to the revenue. Have you tried setting up a manager ID field in the table that tracks the sales revenue. Set up a Manager table that links the manager ID to the sales person. Set up a querry so that the manager defaults into the sales revenue table, this way you do not have to enter it each time, but the salesperson and the manager will be associated with the revenue. If they change managers, you only have to change the entery in the Manager Table to change the default.

 

[shaunacol]

I never thought of it from that angle - sounds like a great idea, ill try that today.

Thanks!

 

[Chuck712]

I have an Access db that I need to update a table in on a weekly basis. I had been doing this with a macro that selected all records in the table, deleted them, and then imported the new excel spreadsheet to the table. It really is not working. This table has the customer list, and it contains a number of points that the customer accumulates during the week that they cash in for merchandise, which is stored in an orders table. Each customer has a unique ID that is the primary key, so the relationship between Customers and Orders is a one to many.
What I would like to do is to run a program that would first delete the relationship, update the table from the Excel spreadsheet, and then renew the relationship. I had used a macro, since I am more familiar with those, but I will try anything
Any ideas?