Query between 2 tables

[Guest]

I have a query that I can't seem to comprehend. One table I have employee
initials and states they are doing business in. The second table I have
employee initials and the state they have license to business in. I need a
query that will show me who's license is expired and who is doing business in
a state they do not have a license in.

Any help will be greatly appreciated.

 

[OfficeDev18 via AccessMonster.com]

Help us help you by telling us the names and exact structures of both tables.
Without this info it's impossible.

Sam

 

[Guest]

To find out whose license is expired, use something like this:

SELECT EMP_Lic.EMPID, EMP_Lic.STATE, EMP_Lic.ExpireDate
FROM Emp_States INNER JOIN EMP_Lic ON Emp_States.EMPID = EMP_Lic.EMPID
WHERE (((EMP_Lic.ExpireDate)<Date()));

To find out who is working where they should not be:

SELECT Emp_States.EMPID, Emp_States.STATE
FROM Emp_States
WHERE not Exists (select "X"
from EMP_Lic
where Emp_States.EMPID = EMP_Lic.EMPID
and Emp_States.STATE = EMP_Lic.STATE);

However you have much bigger problems than that. You need two tables.

One should have ALL the information about the Employee including an
EmployeeID for the primary key. This should NOT be initials are there is a
very good chance of duplicates or getting changed. An autonumber would be
best.

The second table for tracking licenses should have the EmployeeID, State
they work in, LicenseNumber, and Expiration date. This way you could easily
pull out who is working where they shouldn’t be or is expired by the
expiration date. If it is null, they aren’t licensed. If the Expiration date
is past, they are expired.

SELECT Employees.EMPID, EMP_Lic.STATE, EMP_Lic.LicenseNum, EMP_Lic.ExpireDate
FROM Employees LEFT JOIN EMP_Lic ON Employees.EMPID = EMP_Lic.EMPID
WHERE (((EMP_Lic.ExpireDate)<Date())) OR (((EMP_Lic.ExpireDate) Is Null));

 

[Guest]

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.

I saw Jerry's post, but don't think that will do what I need, plus I really
never used VBA before so not sure how to tie it into the application.