query to total by broker..

[marduk813]

I have 2 tables: CommTracker, and Brokers. The Brokers table contains about 16 records, each listing a different broker that works for us. I'm using an autonumber field as the PK, and I also have a field for the Total Commission (per broker). In the CommTracker table, I have around 100 records containing info on each broker's commmission for each case that they have settled. What I would like to do is create a query (unless it can be done in the Brokers table itself) to give the total commission that each broker has earned from all of his cases. I am having considerable difficulty doing this, but I know it shouldn't be that hard. Using my rudimentary knowledge of SQL, I've created a query that shows me the total commission that ALL brokers have received from ALL of the cases, but I can't seem to show the total that EACH broker has received.
Suggestions welcome..

Jas

 

[marduk813]

Nevermind. For reasons beyond human comprehension, I completely forgot about the GROUP BY clause. Crawling back under my rock now...


Jas

 

[marduk813]

Ok, so now that I can display the information per broker, how can I use UPDATE to update the Total Commission fields in the Brokers table?