Excluding Data?

  • Thread starter Thread starter Jamie Allison
  • Start date Start date
J

Jamie Allison

Hi Forum

I have a little problem in a Microsoft Access 2000 database i have.

What i have is two tables. One contains all the information of staff such
as

StaffID - NAME - ADDRESS - NoYearsWorked

The other table only has the field StaffID only.

For this lets call them tblDATA and tblEXCLUDE

What i want to do is have all of the data from tblDATA unless there is an
entry in tblEXCLUDE for it.

so as we add data to tblEXCLUDE as people retire the query will return less
results.

I hope this makes sense.

I know it is probably pretty simple to do some sql for it but i have been
struggling for ages with it. Just can't seem to get it working.

I tried a RIGHT JOIN and an INNER JOIN but they only return results when the
entry's are in both tables. Also tried a LEFT JOIN which returns no results.

Thanks for your help in advance.

Jamie
 
rather than a separate table, suggest you just add a field to the staff
table called Retired and set the Data Type to Yes/No. when somebody retires,
checkmark the box in that record, which will then have a value of True. to
query for "not retired" staff, set a query criteria on the Retired field as
False.
to query the results from two tables (as you originally asked), open your
database and go to the Queries tab. click on New. in the New Query dialog
box, select the Find Unmatched Query Wizard and click OK. follow the
directions in the wizard.

hth
 
You might try something like this: (AIR CODE)

SELECT tblData.* FROM tblData RIGHT JOIN tblExclude ON
tblData.StaffID=tblExclude.StaffID WHERE tblExclude.StaffID IS NULL

HTH
- Turtle
 
Forgot to say that the tblDATA is controlled by globally by another
department and data cannot be added or changed in it. Thus the reason for
creating another table.

Thanks
 
Back
Top