Help needed in build dynamic SQL of unknown table.

I'm not sure why you are using a subquery but regardless, you would have to build the entire query string using VBA if you want to specify a table name at run time.  No querydef can have variable structural elements.  All you can do is pass in a data value at runtime.  The query engine creates an execution plan when Access saves the querydef so if you think about it, the plan would have to completely change if you substitute one table for another.

Hi Pat,

This is used in vba as a parameter for the following store procedure

USE [PlacementNP]
GO
/****** Object:  StoredProcedure [dbo].[procStatistics]    Script Date: 05/12/2015 18:02:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[procStatistics]
(
	@intCategory	AS int,
	@strFilter		AS nvarchar(4000)
)
AS
BEGIN
	--DECLARE @strSQL AS nvarchar(MAX) = '';
	DECLARE @strSQL AS nvarchar(MAX);
	
if @strFilter = '' set @strfilter = 'EmployeeID > 0';
	
 IF @intCategory = 1	--State
		SET @strSQL = ' SELECT [State] AS Category, COUNT(*) AS [Total #]
						FROM dbo.View_EmpStatisticsEmployeestbl
						WHERE ' + @strFilter +
					  ' GROUP BY [State]';
ELSE IF @intCategory = 8	--Source
		SET @strSQL = ' SELECT [Source] AS Category, COUNT(*) AS [Total #]
						FROM dbo.View_EmpStatisticsSource
						WHERE ' + @strFilter +
					  ' GROUP BY [Source]';	
	EXECUTE sp_executesql @strSQL;				
END

Select allOpen in new window

I have there two  options of tables (View_EmpStatisticsEmployeestbl & View_EmpStatisticsSource)

Now if I use EmployeeID in (Select ID from Employeestbl), that will work as both tables/views have EmployeeID, however if I want to replace it with the faster method Exists, I'm wondering how can it be done?

As @PatHartman said, it is just string work.
The question is about "dynamic SQL' so is it safe to assume you are doing this in VBA?
Its just string work
Dim s as string

s= "Select SomeField "
s = s & "From SomeTable Join AnotherTable "
s = s & "Where SomeCondition = SomeValue "
s = s & " And Exists (Select who knows what)"

EmployeeID in (Select ID from Employeestbl)
EXISTS is more efficient that IN if NULL is in play.
But both are markedly poorer than WHERE, and they all get their butts kicked by JOIN if you can manage that.
And generally -- though it can be a brain-bender if you don't breathe set logic -- An IN or EXISTS clause can be rewritten as a JOIN.

But it's all just concatenating strings.

*** posted before your replay to @PatHartman***

@PatHartman
Look at the logic.
It's an IF THEN based on what value of @intCategory he passes in.
The SELECT tables comes from the branches of the IF THEN

@Scott
How would you rewrite the two branches using a JOIN?
I don't do the SQL Server end that often but isn't
WHERE  EmployeeID IN (SELECT ID FROM EmployeesTbl)

the functional same as

FROM dbo.View_EmpStatisticsEmployeestbl JOIN EmployeesTbl on EmployeesTbl.ID = dbo.View_EmpStatisticsEmployeestbl  

And will it perform better/same/worse?

Thanks a lot!

Actually I was thinking similar to Nick's suggestion, to determine the table according to category and use a replace function to a given string like replace( "MyTable", @TableName).
But since its not a big difference, why bother..

but isn't

WHERE  EmployeeID IN (SELECT ID FROM EmployeesTbl)

 the functional same as

FROM dbo.View_EmpStatisticsEmployeestbl JOIN EmployeesTbl on EmployeesTbl.ID = dbo.View_EmpStatisticsEmployeestbl  

 And will it perform better/same/worse?

Typically yes.  If you don't reference another column from the EmployeesTbl, SQL should turn the INNER JOIN into the equivalent of an EXISTS since that performs better.  SQL's optimizer is sophisticated enough to do that.