You are here:

Using MS Access/MS Access 2007 querying same field on Table

Advertisement


Question
QUESTION: I have a database that compares a field on a table to the same field on another table. The error mismatch's are appended to an error log table. That error log table is then used to identify staff that have different types of errors. Example: I am trying to identify someone who has an Salary or hourly rate mismatch AND the same person has a job title mismatch. I have tried to aquire this information a couple of ways..
Here is what I tried

SELECT
E1.Clock,
E1.PSID,
E1.Paytype,
E1.EEName,
E1.CompareType,
E1.ErrorType,
E1.TOS_Value,
E1.Master_Value,
E1.IIncome_Value,
E1.WFM_Value,
E2.ErrorType
FROM tblErrorLog AS E1 INNER JOIN tblErrorLog AS E2 ON E1.Clock = E2.Clock
WHERE (((E1.ErrorType)='Annual Salary Mismatch' Or (E1.ErrorType)='Base Rate Mismatch') AND ((E2.ErrorType)='Job Title Mismatch'));

but I only seem to be get the salary or hourly mismatch.

Any suggestions would be greatly appreciated

Katt

ANSWER: I'm a little confused. Are you maintaining two separate error logs (E1 and E2). If so, that'a a mistake. You should have one error log with a field to identify the table with the error.

But I would try an OR instead of the AND in your Where clause.

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

---------- FOLLOW-UP ----------

QUESTION: Thank you for the suggestion....I only have one error log table, but thought that I would have to join it against it self in order to achieve what I was trying and that is to only get the staff that have a job title mismatch error and either a salary mismatch or a hourly rate mismatch. I am using the error log table to identify staff that meet certain senarios, like a staff member that has a job title mismatch and a salary or hourly rate mismatch.. or staff that have a salary mismatch and a job code mismatch..I am able to get part if I use the error log table and put in the errortype = salary mismatch or hourly mismatch, but when I put in the job title mismatch...I get all the job title mismatch's that may not have a salary or hourly mismatch. I hope I was able to explain that a little better

Thanks

Answer
What I would do is 2 queries, one showing Salary or Base rate mismatches, the other showing the Job Title mismatches.

Then do a third query joining the 2 queries.

An alternative is to do a subquery.

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Scottgem

Expertise

I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.

Experience

I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Organizations
Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Education/Credentials
Brooklyn College BA

©2016 About.com. All rights reserved.