Mainform forces navigation thru each subform record

[Cl0n3Tr00per]

Hello guys, I need Guru Advice...

I have a form that lists employees, and a subform that lists each month's sales total [plus other stuff ]for the employee displayed on the main form. At least that's what I intended it to ...

I thought each form was bound to its own table. However, when I added the subform, Access changed my main form's recordsource to :

SELECT SalesRep.*, Month.FinancialMonthNo
FROM SalesRep INNER JOIN Month
ON SalesRep.SalesRepID=Month.SalesRepID;

FinancialMonthNo is basically a list field with 2 columns, and helps display the month name, while binding the month's number to the table. It has no default indexing/linking. I didn't select it when I designed the main form!

The subform's recordsource is the Month table, as expected.

As a result, instead of allowing me to navigate the Main Form from one employee to the next, I have to click my Next Record Button up to 12 times for each employee. (same with previous rec)

• When I removed the FinancialMonth field from SQL, the results were the same.
• When I removed the INNER JOIN[ SUBTABLE ] clause, the unnecesary subrecords navigation problem was fixed.
• BUT It is now showing me the records that have no monthly data. So fired employees are now in the query results (Not a big problem, I guess I can add an 'employed' bit field and filter it in the table query)

I can't see a downside from eliminating the Subtable link from the main table's recordsource.

Here is the question: Why did access add the subtable link when I inserted my subform ???

Question 2: Will my deletion cause me problems in the future?

Insights will be appreciated! BEER