Query Issue with Multiple Tables

[Asphyz]

Hi there,



Basically I am trying to construct a query that will read multiple tables. I have 3 tables based on Devices, Laptops and Mobiles with information about each piece of equipment and which employeeID has that equipment.

I want to construct a query which pulls information from each table on the different equipment linked to an employeeID. Now the problem is Access will only seem to display people with equipment in every table, so somebody who just has a mobile and laptop is not being displayed because they don't have a device.

Can anybody aid me in trying to figure out what I am doing wrong? Many Thanks.

[orange]

Hi there,

Basically I am trying to construct a query that will read multiple tables. I have 3 tables based on Devices, Laptops and Mobiles with information about each piece of equipment and which employeeID has that equipment.

I want to construct a query which pulls information from each table on the different equipment linked to an employeeID. Now the problem is Access will only seem to display people with equipment in every table, so somebody who just has a mobile and laptop is not being displayed because they don't have a device.

Can anybody aid me in trying to figure out what I am doing wrong? Many Thanks.

Can you show us what you tried? What result did you get?
What is your table and relationship structure?

[Asphyz]

Each employee has their own ID so I have linked the tables together via using that.

The problem is not everybody has the piece of equipment, so for example 1 ID may have 2 items but not a mobile, so their employee ID does not exist in the mobile table.

I have tried adding an EmployeeID into the mobile table who doesn't have one and just filling the mobile information in as Null and this worked, it displayed the information about the kit they do have and just filled in the mobile section as Nulls.

But of course I would rather Access do this automatically. In an ideal world what I want is for access to update the fields in the queries that are blank as blanks so the query displays correctly.

Sorry if I haven't explained everything well, I am a bit of an Access noobie.

[stmoong]

I assume you have an Employee table that is linked to the other three tables.

When you link the tables, Access creates inner join by default, i.e. only rows with matching values in the joined field will be displayed.

You need to double-click on the join and change it to left outer join (option 2) if you Employee table is on the left joining Device, Mobile, Laptop tables on the right. Basically, you want to include all rows from Employee table, and only those rows from the other tables matching the Employee Id field.

[Asphyz]

Thank you so very much, that worked perfectly.