simple agent/commission report

[smil27]

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!

[pbaldy]

For starters, I think you've made a design mistake. Normalization rules would dictate another table related one-to-many on the sale ID or similar field. That table would have a record for each agent involved. That makes your search simple. That said, you want "OR" instead of "AND", plus you don't repeat the word "WHERE".

[smil27]

Thanks Paul,
This second table with the one-to-many relationship, what fields would I move to there and what fields could be left in the main table?
Could there be an issue if the property sells twice in the same year with regards to that design?
Lastly, is there any way I could fix this without the redesign .
Steve

[pbaldy]

Well, I already addressed how to fix the problem with the current design:

That said, you want "OR" instead of "AND", plus you don't repeat the word "WHERE".

The agent fields would go to the new table, probably the percentage for each agent, and perhaps a field to designate which role they played in this sale. You could sell it 100 times a year with that design, presuming your other tables were structured properly. I'd expect a property table and a sales table in addition to this one. And an agents table.

[smil27]

Alright, so the redesign would look like this?:

tblProperty
PID – long integer (PK)
Streetaddress - text
City - text

tblAgents
AgentID – Autonumber (PK)
Agent – text
Role (listing agent, listing agent 2, selling agent, selling agent 2) - text

tblSales
PID – long (PK)
SellingPrice - currency
Commissionpercent (of sale price) long integer
GrossCommission – currency
PendingClosed – text
Nettooffice – currency

Am I missing anything?
Thanks

[pbaldy]

Yes, the table I suggested. I'd take the role out of the agents table, since presumably it can vary. New table, perhaps tblCommissions:

tblCommissions
CommID - autonumber PK
PID - FK from tblSales
AgentID - FK from tblAgents
Role
Commission

So if a given sale had 3 agents involved, there would be 3 records in this table.

[smil27]

Do I need the Commission to be associated with the agents as it just for the properties - 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. But they only charge 6, 5, 4 sometimes 4.5% but mostly 6% as a standard. All agents are 50/50 with the company so does there even need to be a Commissions table. Sorry for all the questions. just not really good at this.

Thanks, Steve

[pbaldy]

It's your baby, but my sense of it is that the commission should be in the new table, because it can change from sale to sale. I may get 4% for one sale and 2% for another, correct? In other words, commission is an attribute of the sale, not the agent.

[smil27]

Got sidetracked with other projects now I am back to this one.

I redid the tables and created the relationships, now I am trying to create a form with a subform below that will have 4 lines each with Agent | Role| Commission . After entering the property information in the form, the user will then choose up to 4 agents, choose their role in the transaction and then enter the commission.

When creating the form in the wizard it doesn't ask me the "How do you want to view your data?" question. I am guessing that the one to many relationship I created is wrong.

Attached are 2 pics of the relationships and the form I am thinking of.

many thanks
Steve

[pbaldy]

For starters, I'd expect a relationship between commissions and sales on the sales ID, not commissions and properties. The commission is connected to a sale, is it not? Then you'd have a form bound to the sales table and a subform bound to the commissions table, with master/child relationship on the sales ID.

[smil27]

Does this look better? or is there more?

[pbaldy]

You tell me; does it sound reasonable that Sales ID is related to Agent ID? The commission table should have sales ID in it rather than PID.

[smil27]

Well I was thinking that the one to many relationship should be with the sales to the agents. I think I am seeing it now.

Agents - onetomany with commissions because one agent can have many commissions.
Property - onetomany with Sales table for the same reason
I replaced PID in the Commissions table with SalesID and its the foreign key with a manytoone relationship for the SalesID in the Sales table.

This is where I get confused creating that last relationship.

Thanks for the patience with a super novice,
Steve

[pbaldy]

Looks like you have it now.

[smil27]

Thanks for the help Paul

Can I just copy and paste the Agent, Role, & Commission boxes on the subform to allow for 4 agents.
The thing I am wondering is how will it know which role and commission is for which agent?
see below -