Junction tables updating sub forms

[PhillipsAndrew]

I have a form with a sub form based on a many to many junction table, which works fine.
If I don't enter any data into the subform no record is created in the junction tables (as you would expect).
The main form is base on a table containing Person Info (ID, Name, Address etc..)
The junction table contains info on what departments they may be associated with (could me many hence the junction table)

The trouble is, I have a query based on these two tables, if I don't have an entry in the junction table then no record is shown.

For example, in my form, if I enter the Person Info but decide not to enter any department info for that person, then the query will not display any data on that person....
I know that I could redesign my query, but I want to do things like - find the total number of people on the system AND what departments they are associated with.
The main and sub forms are linked with the child/master.
Should I be using AfterUpdate or something to create a blank record in my junction table? Or do I have a fundamental misunderstanding..

Thanks

 

[Travis]

You are currently using an INNER Join between tables. This will only Display records that the Joins Match.

You will need to use a LEFT/RIGHT Join. This will display all of the records in the PERSONS table and only matching records from the Department table. Of couse where there are no matching records in the Department table those fields will be NULL.

 

[PhillipsAndrew]

Thanks Travis... I'd just stumbled on this in the help...

Trouble is, my query actually has another table (I tried to keep it simple earlier) which has a one to one relationship with PersonInfo.
This table (tblAdminControl) contains yes/no fields about authorisations etc...

When I change this to be an outer join, Access complains about ambiguous outer joins and suggests I run a query within a query...

What I'm trying to do is run a query that shows who is associated with a certain department OR has a higher level of authorisation (it's all to do with authorising orders) So you can see why I need to see information from all three tables.

I'm looking into it! but any thoughts appreciated!