simple agent/commission report

[pbaldy]

Typically the subform would be in continuous or datasheet view. You would select Agent and Roll and fill in the commission. As soon as you start filling in one, you'll get a new record below. That enables you to have 1 agent or 100.

[smil27]

Hello, When adding records, instead of adding the record to each agent in the agents table, it is adding a new record for each entry in the agents table. So Agent x is entered 5 or 6 times with one role and one commission attached to it, instead of Agent x there once with 5 or 6 entries of roles and commissions below it.

What does it seem like I did wrong for this to happen?
Thanks

[pbaldy]

Hard to say without seeing it. Can you post the db here?

[pbaldy]

I'm not clear on the problem. Looking in tblCommissions, there appear to be 2 agents per sale. Using the form, the appropriate agents & commissions seem to appear in the subform as I scroll records on the main form.

[smil27]

I was trying to run reports so that I could see each agents commission totals on both Listing and Selling together, separated by Pending and Closed, upon trying to write the proper query, the data seemed to be entered into the wrong table.

[smil27]

Sorry I see my problem, just need to modify the query, I was looking at something else.
thanks

[pbaldy]

No problemo.

[smil27]

Hello, when entering records, I pick an agent from the dropdown and it enters the agent I selected into the Agents table as a new entry and creates a new Agent ID. So when I look back at the records, the agents table now has 40 entries where it should only have the original 20 that I created. Did I not check something off there?

[smil27]

I have created a couple queries and it seems to work fine though. Is the duplicate records in the agent table going to be a problem down the road, meaning another redo of the whole DB? I have a few hundred records to input and I want to make sure before I do all that work.
Thanks

[pbaldy]

I think I see the problem. That form is bound to a query that includes both tblAgents and tblCommissions. It should just be tblCommissions (or a query that only includes tblCommissions). The combo would get its selections (Row Source) from tblAgents, but that's all.

[smil27]

Ok, So take the Subform out and just insert 2 combos at the bottom and have the first combo for Agent Rowsource come for the agents table. Instead of the adding Agent from the agents table for the field?

[pbaldy]

No, just change the record source of the subform. The subform is bound to the commissions table. The agent combo on that subform gets its selections from the agents table, but is bound to the field in the commissions table. I think because of the joined tables, it's adding to both tables.

[smil27]

So this is the Record Source from the Subform properties:

SELECT [tblAgents].[Agent], [tblCommissions].[Role], [tblCommissions].[Commission], [tblCommissions].[SalesID] FROM tblAgents RIGHT JOIN tblCommissions ON [tblAgents].[AgentID] =[tblCommissions].[AgentID];

I will take out the SELECT [tblAgents].[Agent]

And then add a combo box add in the RowSource SELECT tblAgents.Agent FROM tblAgents;

[pbaldy]

The combo is already fine. The subform record source should either be

tblCommissions

or

SELECT [tblCommissions].AgentID, [tblCommissions].[Role], [tblCommissions].[Commission], [tblCommissions].[SalesID] FROM tblCommissions

[pbaldy]

Sorry, the combo row source is fine, the control source would be AgentID.