MS Access Form/Query Setup

[Datatech]

Afternoon,
I am pretty sure I am overlooking the solution to this access database but I having difficulties figuring out the correct path.
Basically what I am working on is a financial database for on-going projects and their commission payouts, I am working with 3 tables setup as follows:
tblProjects
ProjectID – (PK)
ProjectName – Text

tblSubProjects


SubProjectID – (PK)
Main Project # – Text
Project Amount
Project AGP
Project Total

tblSalesMasterCommission
ID – (PK)
Mas Project # – Text
Salesman
Salesman Commission

The issue I am having is until now all commission was paid off the subprojects where all the project data (Amt,APG,Totals) are stored. However due to recent changes they want to track commission at the top Project level. The problem is I need data stored at the sub project level. When ever I use a query to join the Master and Sub table I am then unable to add data/records. I have tried a variety of tweaks to get around this but with different results.

Anyone with info on how I can work around this would be great.

Thanks again for your time and help.

[June7]

Not sure what the issue is.

The commission record links to the subproject record and subproject record links to main project record. Save the subproject ID in the commission record and the main project ID can be retrieved in report whenever needed by query joins to summarize commissions grouped by main project number.

[Datatech]

Thanks for the suggestions, I'll give it a try and see but I noticed my table layout was a bit off there it seems. The layout above is what the structure was like and working great but now they want the tblSalesMasterCommission joined to tblProjects. That is where I have ran into problems with being able to add/update or view all data. I've been able to do one of two things:

A) Add data to Commission table but unable to see details from SubProjects. (Remove SubProject table from any query)

B) Unable to add data to commission table but able to see all details from all three tables. (Made a query of Master and Sub to get details and linked to Commission table)

[June7]

Why join commissions directly to projects? Join through subprojects for report output.

Use form/subform arrangement for data entry.

[Datatech]

That is basically what I am trying to do but they want to see the data on the form summarized, you will always have 1 master project but could have a single or multiple sub projects, but when it comes to the commissions they want to see the sub projects grouped and totaled by the master project for pay outs.

Master #, Master Name, Sub # , Sub Name, Sub Amount, Sub AGP, Sub Total, Sub Salesman
01-1234 , PJ1 , 01-1234-01, PRJ1-01 , 5,000 25% 2,750 , Smith
01-1234 , PJ1 , 01-1234-02, PRJ1-02 , 2,000 45% 800 , Smith
02-5678 , PJ2 , 02-5678-01, PRJ2-01 , 52,000 15% 36500 , Smith

etc.

But when I try to get the fields from each table on a form I run into the above problems for the data entry side.

[June7]

What 'they' are asking for is really what reports are for, not data entry forms. Forms and subforms can be nested up to I think 7 levels, most I have done is 3 - form/subform/subsubform. In your case main form of projects, subform of subprojects, subsubform of commissions. I suppose there could be another subform or a listbox with an aggregate query as data source that would show the commissions summarized as you show. The trick is to refresh the summary calcs 'on-the-fly' as data is entered. That what you are looking for?