combining data from multiple tables

[dragct]

I consider myself a basic Access user. I have built a few straight forward databases, but I have a question.

I want to build a database to combine data. I want to download data out of our ERP system. I have a table with Position IDs, but no incumbents. Then I have a table that shows Position IDs with incumbents. I ran a query, but it is only pulling data if the Position ID has an incumbent. I want to combine all the data, where or not there is an incumbent. what am I doing wrong?

FYI. We have Position IDs for all FT and PT positions. For FT the relation between Position ID and incumbent is 1:1, for PT it can be 1:many.

 

[boblarson]

You need to use an OUTER JOIN. What you would do is, if you are using the query designer (graphical interface) you would add both tables to your query (which I'm sure you have done) and then the link that links the ID fields between them you can double click on and then select the option which states something like:

Select all records from Position table and only those records which match in the Other table.

 

[tehNellie]

If you query the table containing position ids and LEFT (OUTER) join the table with Positions and incumbents, you should get All the positions regardless of whether or not there is an incumbent.

i.e. in SQL

SELECT p.PositionID, i.incumbentID, i.name
FROM positions AS p
LEFT JOIN incumbents AS I ON
  p.positionID = i.positionID

Where a Part time Position has multiple incumbents, you'll get multiple rows:

PositionID       IncumbentID     Name
1                NULL            NULL  <- No incumbent
2                1               Rod   <- Full time
3                2               Jane  <- Part time position with two incumbents
3                3               Freddy

 

[dragct]

I ended up using a Union Query. Results have been confirmed. Thank you both for your input. Kim