Hello, with limited Access 2010 knowledge, I have created a small database for a real estate office to show reports of commission for each agent and for the total office.
I have a table of all 30 agents in the office called Agents:
ID | Agent
I made a form to input each sale that contains the following fields:
ID | Street Address | Selling Price | Commission % | Gross Commission | Listing Agent | Listing Agent Commission (LAC) | Listing Agent 2 | LAC 2 | Selling Agent | Selling Agent Commission (SAC) | Selling Agent 2 | SAC 2 | Pending / Closed | Net to Office
All this info goes into Table1
The Listing Agent, Listing Agent 2, Selling Agent, Selling Agent are all drop down lists that is taken from the Agents table.
The Pending/Closed field is also a drop down of the 2 choices.
Sometimes the agents share listing a property or share in selling a property, hence the listing agent 2 or selling agent 2.
Sometimes one agent is the listing and the selling agent on a single property.
I would like to run a report that lists total commission for each agent for all of their pending or closed sales - whether or not they are the listing agent, 2nd listing agent on a property, selling agent, 2nd selling agent or listing and selling agent.
Commissions stay same for the agents but change for each property so Property 1 -Commission = 6% of the selling price. It could then be 4 for the listing agent ( or 2% & 2% if there's a 2nd agent) and 2% for the selling agent.
Each property entered into Table1 has an ID field which is the primary for the table and set to Autonumber but each property also has a property ID (PID) which is unique to the property but the property could be sold twice in one year so I didn't want to use it as a primary.
This doesn't seem to be working for me:
SELECT Table1.
[Listing Agent], Table1.LAC, Table1.
[Listing Agent 2], Table1.LAC2, Table1.[Selling Agent], Table1.SAC, Table1.[Pending/Closed], Table1.[LAC]+[LAC2]+[SAC]+[SAC2] AS [GCI to Date]
FROM Table1
WHERE (((Table1.
[Listing Agent])="John Smith")) AND
WHERE (((Table1.
[Listing Agent 2])="John Smith")) AND
WHERE (((Table1.[Selling Agent])="John Smith")) AND
WHERE (((Table1.[Selling Agent 2])="John Smith"));
Thanks for the help in advance!