query with relation in tables problem

[JeroenMioch]

Hey Guys,

I have a query based on a table with a lot of data (columns) in it.
I needed to include some data (one column) from another table, so i added this table in the query and made a relationship between two columns with the same data from both tables.

Now that access knows the relationship, i could add the column i needed to the query, so far so good.

But when i delete a name from the second table, in stead of leaving the fields blanc in the main table, the query doesnt show these records.

To clarify :

Main table :

Loads of data colums, ManagerName



Second table :

Some data, ManagerName, Phonenumber

The query output :

Loads of data, ManagerName, PhoneNumber

When i delete a managers name from the second table, this manager and his phonenumber isnt showing up in the query result. I would like the output to be empty fields for the manager i deleted.

Any thoughts ?

[orange]

Please post the query SQL.

[John_G]

I assume you have joined the tables on ManagerName. Normally, a query will only show records where the ManagerName is the same in both tables. So, if ManagerName "A" is present in the big table, but there is no ManagerName "A" in the smaller one, then there can be no cases where there is a ManagerName "A in both, so no records are shown.

However, your requirement is very common, and you solve the problem using an "Outer Join" between the two tables. If you are using the query design grid, right-click the link between the two tables, then click "Join Properties". You will see there are three options, with the default set as option 1. change the setting to Option 2 or Option 3 (All records in table....), and run the query. One of those will give you what you need, but I can't say which without seeing your design - it depend on how you set the query up.

[NoellaG]

Hi, if you joined the 2 tables with an inner join on manager name, and then delete the manager name, the join can't find the linked record anymore and the result doesn't show up in the result. To show all records from one table and only those that match of the second, you need an outer join.

[nhorton79]

If you just deleted the managers name (making it empty) but there was still a record for the phone number, then maybe you need to allow for nulls in your query.

Try changing ManagerName in your query design to be
Manager: nz([ManagerName],"")