Non-Equal values in Queries

B

Bob

I have an Employee Info Table that holds the employee information, EmpID (pri
key, txt), BCO (#), EmpName(Txt), AdjInt(Txt), DOH(date), CurPos(Txt)
CurPosStrtDte(date), PrevPost(Txt), IndExp(Txt), and YearsExp(#). +

Then I have an Emp Training Table that collects the individuals training
history EmpID(Txt), Type(Txt), State(Txt), License#(Txt),LicExpDate(Date).
No primary key defined as unique values are not guaranteed.

Finally I get a feed from our system that indicates who is handling what in
what state called Cognos Data AdjName(Txt), AdjInit(Txt) and State(Txt).

The goal is to write a query that will indicate for me who is handling
business in a state where they either do not have a license or their license
has expired.
 
J

Jeff Boyce

Bob

It doesn't appear as if you have a (?reliable) way to connect employees to
your "Cognos Data". While you have a name-like field in both, are you quite
confident that "Bob Smith" in one table is not "Robert J. Smith" in the
other? If you are assured that both record the same person name the same
way in both, you are still in trouble, as your system could have more than
one "Robert Smith". Without an absolutely guaranteed unique identifier in
two table you wish to join, you can't.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP
Microsoft IT Academy Program Mentor
 
B

Bob

The AdjInit field is what I use. That way I don't have to rely on accurate
spelling. AdjInit in each table is unique to that individual, though there
are many records for each Adjinit.
 
J

Jeff Boyce

So, you're saying that the Employee (table) info and the state(s) in which
the employee can practice are related via the AdjInit fields in each.

Sounds like a query that returns Employee info and all state(s) info. If
there's a chance you have an Employee without any states, you'd want to
change the join to be directional. Hightlight the join, change the
properties to include all (qualifying) rows from Employee and any (matching)
rows from your Cognos table.

Next, it looks like your Employees could have 0-to-many records in the
Employee Training table. That sounds like the "state" situation. You could
open the query (from above) in design mode and add the Training table, again
joining to include all employees and any training record info.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP
Microsoft IT Academy Program Mentor
 
B

Bob

If I am missing something ( and I probably am ) forgive me. I believe what
you state below still does not give me the employee info and the state that
they do not have a license in. It may give me the employee infor, but not
the state. I keep thinking I need some reference whos results are not equal
to the existing license.
 
J

Jeff Boyce

Bob

Joining all three tables will give you employee, state and licensing. To
get the "not" information, you could create a second query that joins the
results of the first back against the ?state table, and looks for IDs where
the employees are in the state but not in the first query.

See the "unmatched query" wizard.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP
Microsoft IT Academy Program Mentor
 
Top