mismatched query where one cell matches two cells in another table searching via employee ID.

[Tarvalon14]

Hi all,

I need a query that searches an Employee ID and a charge code they are supposed to be charging (EmployeeList Table) and compares it to the Employee ID and the charge code they are actually charging (EmployeeTimesheets Table) and return the unmatched results. (I need to know if they charged the incorrect code) The issue is, on EmployeeList, the straight time and overtime codes are separate fields. On EmployeeTimesheets, they are combined in one field "PA Cost Category ID." Below is a screenshot of my attempt at creating the relationships. Can you please help me build this query? The wizard is not getting me the results I need. It keeps filling in the OT field as "is null." I used to be able to rock and roll in this stuff but that was well over a decade ago. (

Thanks in advance.

 

[Micron]

Your tables might be the beginning of your problems. Then again, maybe you've been using this for years without being stumped.
The only fields in your Empl table should be those that describe (are attributes of) the employee. FName and LName (note the absence of spaces in my object names) should not be in timesheets. Does that query even run at all with joins going in opposite directions?

I need a query that searches an Employee ID and a charge code they are supposed to be charging

For that you should have a table of charge codes and a junction table for employee/codes records.
For the immediate issue, you might have to create query1 that returns all records from timesheets and gets the names/numbers from employees. That should give you everything they're charging to. Then using unmatched query wizard, create another query using query1 and empl table, assuming your charge codes are somehow related to your employee (which again, it should not be). Too late for this db I guess, but you should research how to name objects in a db. Not being familiar, I have to wonder about things like joining cost cats on PA stuff.

Here's a partial of a set of relationships in one db I have. Note the lack of spaces, special characters in names. Note the names id which side is the pk and which side is the fk field, and the names are similar. It's all designed to help others that have to take over what you create. Also helps you 6 months down the road rather than wonder what something means.

If I forget what tblME is for, the comments in the design view of description field will remind me, or I can use the properties dialog:

Hope all of that is not too much. Sometimes I have trouble controlling the teacher in me.

 

[Tarvalon14]

This is a brand new database that I created Friday trying to solve an excel problem I'm having. I used to be able to rock and roll at this stuff. Now I'm staring at it like I've never seen it before.

I'll do what you suggested and see what happens.

 

[Micron]

My advice would be to start over given that you don't have tons (weeks or months) of time into it. I have a bunch of links pertaining to design tips if you want me to post them. One approach might be to post a pic of your relationships when (if) you do that and get feedback on it before entering a lot of data.

 

[Tarvalon14]

 

[Micron]

EmployeeID is not an attribute of Codes so it shouldn't be there, much less be a primary key. What you have there would impose this restraint:
1 employee can only have 1 title, 1 reg and 1 ot values. Not sure if that is your intention. That raises the issue that anyone helping you with table design needs to have some grasp of the process, and that only happens through discourse.

So unfortunately still not right and I can only guess that you haven't studied db normalization (at least not as a refresher).
I'm willing to help as I can, but only if you put some study effort into it. This isn't something you're going to create correctly in 30 minutes or so. If you don't want to put in the time I respect that and will bow out. You could then try the 2 query approach that I mentioned in post 2.

Also, Date is a reserved word and should not be used as an object name. That would have been included in the links you didn't ask for.

 

[Tarvalon14]

You're absolutely right...I definitely need a refresher. Thanks for all your help.

 

[Micron]

Here - they're free anyway. Might as well take another shot wrt guidance.
Good luck!

Normalization Parts I, II, III, IV, and V

What Is Normalization, Part I

Thoughts, opinions, samples, tips, and tricks about Microsoft Access

rogersaccessblog.blogspot.com

and/or

Database Normalization – Holowczak.com Tutorials

holowczak.com

Entity-Relationship Diagramming: Part I, II, III and IV

Entity-Relationship Diagramming: Part I

Thoughts, opinions, samples, tips, and tricks about Microsoft Access

rogersaccessblog.blogspot.com

How do I Create an Application in Microsoft Access?

How do I Create an Application in Microsoft Access?

Thoughts, opinions, samples, tips, and tricks about Microsoft Access

rogersaccessblog.blogspot.com

Important for success:
Naming conventions - General: Commonly used naming conventions

MS Access Naming Conventions

_________________________________________________________ Naming Conventions. This document is aimed at the user who is unfamiliar with any naming convention. It is based upon personal experience and the interaction I have had with others. It is not a hard and fast rule. Why use a...

www.access-programmers.co.uk

What not to use in names
- Microsoft Access tips: Problem names and reserved words in Access

About Auto Numbers
- General: Use Autonumbers properly

The evils of lookup fields - The Access Web - The Evils of Lookup Fields in Tables
Table and PK design tips - Microsoft Access Tables, Primary Key Tips and Techniques
About calculated table fields - Microsoft Access tips: Calculated Fields
About Multi Value Fields - Multivalued Fields