one side of 'outter join' issue.

[travismp]

Access 2000
tbl_TESTS (lists all active employees for a company)
tbl_RANDOMS (table that lists which employee needs to take a test)

[EmpSS] is a social security field in both tables to connect them. I need a form that will allow me to show all active employees from tbl_TESTS and then select the option for a test from the tbl_RANDOMS.

tbl_RANDOMS
EmpSS
1Q07
2Q07
3Q07
4Q07


tbl_TESTS
CompName
NameFirst
NameLast
EmpSS
EmpInPool (yes/no check box for active employees)


I want a form that lists all of the fields above.
Show all the active employees
Then if I type something into one of the four fields for tbl_RANDOMS it adds a new record to the table.

I have added a simple DB for an example. When the form opens select Wal-Mart from the drop down. You will see the 8 people they have as current employees. Now it will not allow me to select one of the drop downs for any of the quarterly stats. Thanks.

 

[boblarson]

A few things:
1. You don't need to set the subform to be bound (child/master) to the main form as your combo box is unbound and just selects the applicable item for displaying the related items in the subform. You can just set the query on the subform to be limited by the selection of the combo box on the main form.

2. You should not be having columns for 4Q2006, 1Q2007, etc. You should have another table to have ROWS for each quarter (normalization - and easy adding of new ones).

Fix those and you might not have a problem.