Multiple Relationships

[Niteshift]

I am trying to generate a commisions report for salespeople. The Sales are divided into a 52 period system (weeks in a year). I need the commissions database to break down the sales by period AND by agent fields... When i make the relationaships it works great for either the period or the agent but not both. It disregards the other relationship.

So i am doing sum of of Units sold....however i want sum of units sold during a specific period.

[Vaughan]

If the units sold were in a relate file, you'd just have to perform the required find for the period, then use a summary report.

How do you have it arranged?

[Niteshift]

there is 1 file for sales... Each sale is a new record and it contains the period and agent for the sale. I have another file for commissions that i need to have show all the sales for the given period and print a statement for that period. I therefor issue a check and attach this to it to show what i am paying. When i create a new record in commissions i put in the period and it will show all sales for the period... how do i make it also do so by agent? If i change the relationship to agent i can then get all the sales for a certain agent...but not by period.

[Vaughan]

What field are you using as the primary (local) key?

[Niteshift]

hmmm... key? ms access type key or "the unique identifier" is agent

[Niteshift]

In the spirit of continuing to move forward... ive changed the system of period to include year... which makes an ideal primary key.. so i still have 52 periods to a year but we are now in period 5102 instead of just 51, therefor being unique as well... am i moving in the right direction??? lol