Re: Non-Equal values in Queries

Tech-Archive recommends: Fix windows errors by optimizing your registry

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.

"Jeff Boyce" wrote:

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

"Bob" <Bob@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:24898809-BFAE-4780-A6FB-5AC9F059EC13@xxxxxxxxxxxxxxxx
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.

"Jeff Boyce" wrote:

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

"Bob" <Bob@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:9B0713C6-6479-4C51-BB6F-BF776BA3E009@xxxxxxxxxxxxxxxx
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.





.

Relevant Pages

  • Re: Code for related tables
    ... Microsoft Office/Access MVP ... "Jeff Boyce" wrote: ... address that belongs to the Employee. ... I have two tables, tblTasks and tblEmployees, both related by ...
    (microsoft.public.access.modulesdaovba)
  • Re: how to make a query to select the last 3 years
    ... Create a new query. ... Microsoft Office/Access MVP ... "Jeff Boyce" wrote: ... The employee record will be duplicated; ...
    (microsoft.public.access.queries)
  • Re: Macro to rename a table
    ... Jeff Boyce ... Microsoft Office/Access MVP ... to use queries to produce summary or total figures for each/any employee ... Steve Schapel, Microsoft Access MVP ...
    (microsoft.public.access.macros)
  • Re: how to make a query to select the last 3 years
    ... Microsoft Office/Access MVP ... Microsoft IT Academy Program Mentor ... The employee record will be duplicated; ... "Jeff Boyce" wrote: ...
    (microsoft.public.access.queries)
  • Re: how to make a query to select the last 3 years
    ... Each employee has 6 P&P ID and each P&P has more than 3 years of evaluation ... "Jeff Boyce" wrote: ... Microsoft Office/Access MVP ...
    (microsoft.public.access.queries)