simple agent/commission report

[smil27]

Hello, I have created a small database for a real estate office to show reports of commission for each agent and for the total office.

Table1 has fields for sales, commission and who is the listing or selling agent or both. Field Listing Agent pulls form linked table with the list of agents.

Trying to do a query for each agent which totals and displays their commission from all sales closed or pending where they are entered as the Listing Agent, Selling Agent or both.

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!

 

[boblarson]

Welcome to AWF.

I hate to be the bearer of bad news but the very first thing is to fix your incorrect table structure. You should not have repeating fields like that. You use RECORDS in a table instead of columns. So, there should not be a Listing Agent, Listing Agent 2, etc.

You would need to have another table which would store the data like

tblListingsAgents
ListingAgentsID - Autonumber (PK)
ListingID - Long Integer (FK)
AgentID - Long Integer (FK)
ListingType - Long Integer (FK)

And that means an Agents table

tblAgents
AgentID - Autonumber (PK)
FirstName
LastName
Commission
etc...
tblListingTypes
ListingTypeID - Autonumber (PK)
ListingTypeDescription - Text

So that is a quick example but of course I don't know your business needs and all, but that is essentially where you would head.

 

[smil27]

Thanks Bob, you are probably right but before I start over, I will lay out all the info.

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.

Does this make better sense?
Thanks!!

 

[boblarson]

Yes, it makes sense and what I wrote before still stands. And I would change the field name in the Agents table to AgentID instead of just ID. It makes things much better when working with things. Same with the Table1 ID, but with that one that would be much like what I originally posted.

So, does the commissions change for the agents at all? If so, you would want a junction table to store the commissions as of a certain date.

tblAgentCommissions
AgentCommissionID - Autonumber (PK)
AgentID - Long Integer (FK)
CommissionPct
EffectiveDate

Again, we might need to work on this a bit to get it right.

 

[smil27]

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.

 

[smil27]

Welcome to AWF.

I hate to be the bearer of bad news but the very first thing is to fix your incorrect table structure. You should not have repeating fields like that. You use RECORDS in a table instead of columns. So, there should not be a Listing Agent, Listing Agent 2, etc.

Sorry to bother, can you elaborate on the above. I do not quite understand why these fields are repeating and why the table structure is incorrect?
Thanks