Two Table Query

[Darin]

I have two tables, one containing Job information like, Title, Pay Range, Job
ID, Active Flag, etc...
The other table has personnel information such as Name, Rate of pay, Job ID,
Active Flag, etc...

I am pulling all information from my "Job Table" Joining it to to my other
table by the Job ID and setting the property to pull all records from the Job
table, and only those records from the other table where the joined fields
are equal. I want to only add a few fields from the second table .

I have an Active flag in both tables, one referiing to the Job and the other
refering to someone working or being terminated.

I set the criteria in my query to pull only Active "Jobs" from the Job
table, and to pull "Is Null" or "Active" personnel information from the other
table, but not terminated info.

In Access 2003 this would pull all information from the "Job Table" then add
the hand full of fields from the other table. If it came to a non-match, the
record would come down with Job information but not have information in the
extra fields from the other table. Basicly a vacant Job.

In access 2007 this same criteria excludes any positions that are currently
vacant. Instead of giving me the record with Job information and no
personnel information added, it simply excludes the record.

Is there a better way of combining this type of information?

 

[Jerry Whittle]

You probably have an Inner Join between the two tables. In query design view,
double click on the line between the two tables. When the dialog box appears
(it could take a few tries) try the second option which is a Left Join. If
that doesn't work attempt the third option which is a Right Join.

 

[Darin]

I appreciate the suggestion but when I tried your suggestion, but it made no
difference in the outcome of my query.

My job table has only active jobs with a status field showing "A" for
active ... there are currently no inactive jobs. All Jobs have an
identifier, for instance the identifier for Sales people would be SL01, SL02,
SL03 etc. The same is true of all other 2600 jobs in the database.

My other table has both Active ("A") and Terminated ("T") personnel, with
the status field showing "A" or "T". This table also has the job identifier
such as SL01, SL02, SL03 etc for Sales people. But because this contains
both active and terminated employee information, there are multiple
occurances of some of these identifiers. ie John is currently employed "A",
but Sam was his predicessor "T" both records having the SL01 identifier.

The only criteria I used in the query is "A" in the "Job Table" status field
and
Is Null Or "A" in the status field of the table contaiing past & present
employee information.

I am still having records droped where the employee information table has
only "T" terminated employees for a particular job ID.

Access 2003 will bring in the job information, leaving the employee info
blank.
Access 2007 drops the record that has only "T" in the employee information
table.

Is there another way to aproach this?

 

[Jerry Whittle]

Hi Darin,

Interesting....

Show us the SQL. Open the problem query in design view. Next go to View,
SQL View and copy and past it here. If you could also post some sample data
from the two tables, that could help. I have A03 at work and A07 at home and
could try it out.

 

[Darin]

After further investigation ... I found that the ODBC version I was using was
not fully compatible with one of my data sources. I changed the driver to
the prior version and I no longer have the problems that I have described.

Thanks for your advice ...