Query based on txtdept/some txtdepts need to see multiple depts results

[cbrxxrider]

I have a table listing all the departments and a home form with a [txtdept] control.

I have many different queries pulling information based on the logged in dept. One example is below.

(weeklycompletions)

SELECT qryEmployeeWeeklyCompletions.EmployeeID, qryEmployeeWeeklyCompletions.Department, qryEmployeeWeeklyCompletions.[Last Week], qryEmployeeWeeklyCompletions.[This Week], qryEmployeeWeeklyCompletions.[Total Entries]
FROM qryEmployeeWeeklyCompletions
WHERE (((qryEmployeeWeeklyCompletions.Department)=[forms]![Home]![txtdept]));



--

Some departments oversee other departments. Instead of creating this query many times, I'm wondering if there's criteria I can put in so if the txtdept = Finance, I can make it show finance and receiving, or if it's hotel, it will show hotel and housekeeping.

I know I could just ask for user input on what department their looking for but I'm trying to keep them from seeing the other depts if I can.

--

Another example of my issue is this query showing total completions per department, most depts can use the following...

SELECT tblCompletions.ID, tblCompletions.Department, [Employees Extended].[Employee Name], tblCompletions.Goal, tblCompletions.CompletionDate AS [Completion Date], [Employees Extended_1].[Employee Name] AS Entered, tblCompletions.[Verified By], tblCompletions.[Verified Date], tblCompletions.Comments, tblCompletions.[Points Earned], tblCompletions.[Denied By], tblCompletions.[Denied Reason], tblCompletions.[Denied Date]
FROM (tblCompletions INNER JOIN [Employees Extended] ON tblCompletions.EmployeeID = [Employees Extended].UserID) INNER JOIN [Employees Extended] AS [Employees Extended_1] ON tblCompletions.[Entered By] = [Employees Extended_1].UserID
WHERE (((tblCompletions.Department)=[forms]![Home]![txtdept]) AND ((tblCompletions.[Denied By]) Is Null));

But since hotel needs to be able to update both hotel and housekeeping I had to create another specifically for them...

SELECT tblCompletions.ID, tblCompletions.Department, [Employees Extended].[Employee Name], tblCompletions.Goal, tblCompletions.CompletionDate AS [Completion Date], [Employees Extended_1].[Employee Name] AS [Entered By], tblCompletions.Comments, tblCompletions.[Denied By], tblCompletions.[Denied Reason], tblCompletions.[Denied Date]
FROM (tblCompletions INNER JOIN [Employees Extended] ON tblCompletions.EmployeeID = [Employees Extended].UserID) INNER JOIN [Employees Extended] AS [Employees Extended_1] ON tblCompletions.[Entered By] = [Employees Extended_1].UserID
WHERE (((tblCompletions.Department)="Hotel") AND ((tblCompletions.[Denied By]) Is Not Null)) OR (((tblCompletions.Department)="Housekeeping") AND ((tblCompletions.[Denied By]) Is Not Null))
ORDER BY tblCompletions.CompletionDate DESC , tblCompletions.[Denied Date] DESC;

I assume there's a way for me to use these more wisely and say if there's hotel show both these else show only txtdept. I also created an admin query separately to show all departments.

Thanks for any input.

[June7]

Instead of trying to manage filtering within the query, consider building the filter criteria in VBA and applying to the form Filter property. That's my preference. I don't build dynamic queries. I filter forms and reports.

[cbrxxrider]

Instead of trying to manage filtering within the query, consider building the filter criteria in VBA and applying to the form Filter property. That's my preference. I don't build dynamic queries. I filter forms and reports.

Thanks, that sounds like a good idea. I'll look into it.