Query to include "X Criteria", but not exclude other criteria in same field associated with Employee

[CharlieGirl]

All,

I have a separate table for each of my programs, that includes some of the same fields, to include "Employee" name, Date, Division, and Program Name, among others. I have created a Union Query so that I can see the activity by Employee across all "Programs". How can I write a query to see all activity by Employees that have activity in "X Program" in addition to all other programs. If I query by "x program" in the Program field, I only see those records where "x program" is noted, but I want to see all programs associated with employees where "x program" is noted, along with other programs associated with the employee.

 

[LarryE]

If you can provide a screenshot or picture of your Relationship window so we can see what tables and relationships you have, perhaps someone can help. But it sounds like you are keeping the same data in multiple tables instead of using ACCESS as a relational database, so what you want to accomplish may be a challenge. Let's see what you have first.

 

[CharlieGirl]

Larry. You are correct. I inherited multiple tables, all of which have "some" of the same fields, hence the reason for the Union Query. There are approximately 100 tables - it's impossible to get it in a screen shot. I'm able to conditionally format a report so that it highlights those "Employees" where the program = XCriteria, but by report is close to 2,000 pages and I'd like to be able to extract only those Employees name that are conditionally formatted or atleast sort them so that they appear at the top of the report. It doesn't look like I can sort by color either...any chance I can do that in VBA?

 

[The_Doc_Man]

From your description, you have a MAJOR issue in normalization. Are you familiar with the topic of Database Normalization? Because that duplication of table structures is going to bite you in sensitive places EVERY TIME you try to do anything complex.

Unless there is a major security directive that mandates such separation, keeping different but very similar tables for separate programs is very bad design. Having once inherited someone else's miserable design, I understand your position. But to make serious progress, you MIGHT have to divert to a normalization initiative. If you could succeed in that initiative, those UNION queries would vanish.

 

[LarryE]

Larry. You are correct. I inherited multiple tables, all of which have "some" of the same fields, hence the reason for the Union Query. There are approximately 100 tables - it's impossible to get it in a screen shot. I'm able to conditionally format a report so that it highlights those "Employees" where the program = XCriteria, but by report is close to 2,000 pages and I'd like to be able to extract only those Employees name that are conditionally formatted or atleast sort them so that they appear at the top of the report. It doesn't look like I can sort by color either...any chance I can do that in VBA?

Your tables need to consolidated and normalized first. There is no point in trying to report or sort anything without that. The fact that you have 2,000 report pages and 100 tables should tell you something about how this project is designed. Whoever designed it, attempted to force ACCESS to operate like a spreadsheet and you can see what happens when someone tries to do that.

If you wish, I would be happy to attempt to normalize the tables and create relationships for you, but you would need to attach your file so I could download it. With 100 tables, it might be a challenge but I am willing to try. I would have many questions along the way, so just be forwarned. I don't know what this ACCESS project file is supposed to track, but it sounds like some sort of employee program tracking where each employee has been assigned various "programs" to wok on or something.

It would be very helpful if you could at least describe what this ACCESS system is supposed to do and what data you want to track so anyone trying to help can learn what the purpose of the system is. ACCESS design always follows the workflow process, so we need to learn what that process is.

 

[CharlieGirl]

Okay, thanks Larry. I appreciate the response. Looks like I have a big project to do, then. And, yes, if I could lean on you for assistance, that would be great. There are some security issues....so, I need to look at the data first and determine if I can even send it to you and what I can tell you. My apologies for being vague, but I'll be in touch early next week. Thanks again for your time.

 

[LarryE]

Okay, thanks Larry. I appreciate the response. Looks like I have a big project to do, then. And, yes, if I could lean on you for assistance, that would be great. There are some security issues....so, I need to look at the data first and determine if I can even send it to you and what I can tell you. My apologies for being vague, but I'll be in touch early next week. Thanks again for your time.

If you can't attach the actual data file, then at least describe the workflow process and we can then recommend a proper design. The actual data will not matter so much as the design and how tables relate to one another. For example:

1. I have many departments
2. Each department has many employees
3. Each employee is assigned to many projects
4. Each project has many conclusive results

This kind of description helps anyone outside learn what you wish to track and generally what tables would be needed. Anyone helping you can then recommend a design to get you started. Of course, you would need to then convert the tables you now have to the new design format. But at least it is a start. In the above very simple example you could have:

1. A department table holding all department information
2. An employee table holding only employee information
3. A project table holding only project information
4. A project results table that holds all project results information

Hope this helps.

 

[Pat Hartman]

I have a separate table for each of my programs, that includes some of the same fields, to include "Employee" name, Date, Division, and Program Name, among others.

All your programs should be in the same table. They should not be in different tables. As the others indicated. Fixing that problem is where you need to start. Otherwise, you are treading water and losing ground.

If there is variation in the columns of the tables, we need to understand why and can you fix that? Did that happen over time? Can you just standardize on the max columns? Maybe you can pare them down later.

When you are consolidating the tables, make sure to get rid of all the Zero Length Strings and replace them with nulls. Add a column so you can identify the source table. You might also want to look into replacing some of the duplicate data with foreign keys as well.

Once the tables are consolidated, then you need to look into normalizing them. No point in doing that first.

 

[CJ_London]

If I query by "x program" in the Program field, I only see those records where "x program" is noted, but I want to see all programs associated with employees where "x program" is noted,

you need two queries.

The first to get the employees who have been on the X program

You then join this to your list of all employee/programs on the employeePK (you union query)

lets just say you have a right bundle of data resolved by your union query (called Uqry in the sql example) and your union query shows the following columns

EmployeePK	EmployeeName	programPK	programName
1	fred	1	X program
2	george	2	Y program
1	fred	3	Z program

If I've understood the requirement, then if you look at the X program you want two records returned for employee 1 (fred) - the X and Z programs

so first query

SELECT *
FROM uQry
WHERE programPK=1

now join it to the uQry again

SELECT *
FROM uQry A INNER JOIN uQry B on A.EmployeePK=B.EmployeePK
WHERE programPK=1

As others have said, better to normalise your data, union queries cannot take advantage of indexing so performance will be slow for larger datasets

 

[CharlieGirl]

If you can't attach the actual data file, then at least describe the workflow process and we can then recommend a proper design. The actual data will not matter so much as the design and how tables relate to one another. For example:

1. I have many departments
2. Each department has many employees
3. Each employee is assigned to many projects
4. Each project has many conclusive results

This kind of description helps anyone outside learn what you wish to track and generally what tables would be needed. Anyone helping you can then recommend a design to get you started. Of course, you would need to then convert the tables you now have to the new design format. But at least it is a start. In the above very simple example you could have:

1. A department table holding all department information
2. An employee table holding only employee information
3. A project table holding only project information
4. A project results table that holds all project results information

Hope this helps.

Hi Larry - Sorry for the late reply. I've been way on travel. I can attach the shell of the database, along with some dummy records. I should have it complete and attached here today...just need to do more scrubbing first. I so appreciate your help! Just for reference, I work for a security program at a government agency. We service over 20,000 personnel, hence the reason for the lengthy reports that I referenced earlier. I have set the database up to track workload by security program (our security personnel), as well as data attached to each of our customers, potentially pointing to security risks. I will send both the backend and frontend, so that you can get a sense of how I am using the database to track our information. Once I send it...feel free to bombard me with questions. Just wanted to let you know it's coming today.....

 

[CharlieGirl]

you need two queries.

The first to get the employees who have been on the X program

You then join this to your list of all employee/programs on the employeePK (you union query)

lets just say you have a right bundle of data resolved by your union query (called Uqry in the sql example) and your union query shows the following columns

EmployeePK	EmployeeName	programPK	programName
1	fred	1	X program
2	george	2	Y program
1	fred	3	Z program

If I've understood the requirement, then if you look at the X program you want two records returned for employee 1 (fred) - the X and Z programs

so first query

SELECT *
FROM uQry
WHERE programPK=1

now join it to the uQry again

SELECT *
FROM uQry A INNER JOIN uQry B on A.EmployeePK=B.EmployeePK
WHERE programPK=1

As others have said, better to normalise your data, union queries cannot take advantage of indexing so performance will be slow for larger datasets

Thanks CJ. I'm having a huge problem with the database slowing down, but I wasn't aware that it was because of my Union Queries. I'm going to try this after I'm able to send my database to Larry. Thanks for your help and suggestion!

 

[CJ_London]

you can always use your union query to populate a temporary table (ideally in another db to prevent bloat). Also ensure your employeePK field in the temporary table is indexed. Then base your query on the temporary table instead of the union query.

Better still - normalise your tables

 

[LarryE]

Hi Larry - Sorry for the late reply. I've been way on travel. I can attach the shell of the database, along with some dummy records. I should have it complete and attached here today...just need to do more scrubbing first. I so appreciate your help! Just for reference, I work for a security program at a government agency. We service over 20,000 personnel, hence the reason for the lengthy reports that I referenced earlier. I have set the database up to track workload by security program (our security personnel), as well as data attached to each of our customers, potentially pointing to security risks. I will send both the backend and frontend, so that you can get a sense of how I am using the database to track our information. Once I send it...feel free to bombard me with questions. Just wanted to let you know it's coming today.....

OK. I will look and others in the forum can look at it too and make recommendations. As all of us have mentioned, the most important thing at the beginning is to understand what you want to track and how the process works. The tables and fields will tell us some of these things. Some of the people in this forum have worked for the government, so perhaps they will help as well.

 

[CharlieGirl]

OK. I will look and others in the forum can look at it too and make recommendations. As all of us have mentioned, the most important thing at the beginning is to understand what you want to track and how the process works. The tables and fields will tell us some of these things. Some of the people in this forum have worked for the government, so perhaps they will help as well.

 

[CharlieGirl]

Larry...after several attempts, my file is too large to attach. Do you have another way I can get this to you?

 

[LarryE]

Larry...after several attempts, my file is too large to attach. Do you have another way I can get this to you?

Try to Copy to my OneDrive Public folder:
Public

 

[LarryE]

Larry...after several attempts, my file is too large to attach. Do you have another way I can get this to you?

You can also Zip the files and attach them here.

 

[Pat Hartman]

I'm going to try this after I'm able to send my database to Larry.

You're new to the forum so maybe you don't understand how they work. Solving your problem is a community effort so sending the database only to Larry means that no other expert can help you. I also didn't see Larry tell you that he wanted to help you personally for free.

 

[LarryE]

@Pat Hartman:
It was always my intention to Zip the files, if I received them, and then attach the Zip file so everyone could take a look at what the file(s) contain and make recommendations for a course of action. And if I do receive them, that is what I will do. I never charge anyone for helping and I don't know if anyone in this forum does. I doubt it, but don't know.

@CharlieGirl:
If you still wish anyone to help you, please Zip the files you have and attach it here so we can all take a look. That is your best option for help.

 

[Pat Hartman]

Sorry Larry, I didn't mean to offend you. I was trying to nicely explain to a new user that this is a community effort and when you send files to a specific member rather than posting them here, you cut off your support from all other members and you may be imposing on the member you sent the files to.