simple agent/commission report

[smil27]

But if I change the Control Source on the Agent combo box to AgentID then it only allows numbers entered instead of the agents name.

[pbaldy]

AgentID is the field in the table. You want the combo to display the name but save the ID. The combo wizard will walk you through it.

[smil27]

I've entered in 10 records and everything seems to be going into the right spots without duplicates.

Moving on to the Reports
- I have created a query that asks for the agents name and then Pending or Closed and then lists the Agent, and the Sum of their pending or closed gross commission. This works well.
- I can create a report that lists all agents, individual commissions and grouped by pending or closed. Is there a way generate a report listing each in Agent, grouped by Pending & Closed, and then within pending and closed, have the gross commission totalled?

Thanks

[pbaldy]

It sounds like you're already using a totals query. Getting pending and closed summed should be a matter of including that field with Group By in the totals row.

[smil27]

So I selected Group By pending and closed and total type sum for the Commission column, but none of the selections allow for subtotal in each category of pending or closed. It just gives a grand total at the bottom or a subtotal for each line.
Is there another way to choose the sum and grouping here?

[pbaldy]

I meant in a query, since it sounds like the report is already based on a totals query.

[smil27]

I have added a new query that does the proper sorts and has everything totalled by pending and closed for each agent. Before I was adding fields and then fiddling with the outcome afterwards. It seems to be giving me the outcome I was looking for.

Thanks for your tremendous help, Paul

[pbaldy]

Happy to help!

[smil27]

Hello,
After entering a over a hundred entries, there are starting to be some duplicates and houses sell twice. The entry form will not let me to enter a duplicate record. Is there a way to change the PID field (the primary key on the Property table to allow duplicates without changing the relationship. It has a one to many with the PID field on the tblSales.
Thanks

[pbaldy]

What are your relationships now? It sounds like the wrong field is the key. Can you post the current db?

[pbaldy]

How do I recreate the problem exactly?

[smil27]

If you try to enter a property PID that has already been entered and its info, it will give an error when you go to the subform to enter agents that have listed and sold the property. The error says that you cannot enter a duplicate.

[smil27]

The exact message I get is this -
"The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index or redefine the index to permit duplicate entries and try again."
I have also sent you the db again. (sent the wrong link before)
Thanks

[pbaldy]

You have the main form bound to a query that includes the property table. You get the error because it's trying to add the PID to that table again, rather than the sales table. I would have that form bound just to the sales table. You can use a combo for the property and use this to display the related info:

BaldyWeb - Autofill

[smil27]

This doesn't seem to be working for me. Shouldn't I just be able to add an AutoNumber field to tblProperty and relink the many-to-one relationship with the tblSales?