Looking for efficiency here, not just "something that works".I need to pull a recordset from a SQL Server 2000 table into a local Access 2000 table, and will later massage, manipulate, and report on it. I have the syntax of my SQL query worked out, and it looks like this:SELECT isnull(u.lastname, '') + ', ' + isnull(u.firstname,'') as AgentName, convert(varchar(10),a.abegintime, 101) as ActivityDate, a.substateid as CategoryID, sum(DATEDIFF(n, A.abegintime, A.aendtime)) as Duration, min(wg.workgroupname) as SupervisorFROM activity a LEFT JOIN users u ON a.entity = u.userid LEFT JOIN workgroup_users wgu on u.userid = wgu.userid LEFT JOIN workgroup wg on wgu.workgroupid = wg.workgroupidWHERE a.entitytype = 1 and a.abegintime BETWEEN '12/6/2003 00:00:00' AND '12/20/2003 00:00:00' and a.substateid IN (5, 36, 12, 34, 8, 30, 35, 6, 7) and wg.workgroupname like 'Team %' and wg.status = 1GROUP BY isnull(u.lastname, '') + ', ' + isnull(u.firstname,''), convert(varchar(10),a.abegintime, 101), a.substateidORDER BY 1, 2, 3
One challenge is that the dates in red above will need to be assigned based on input criteria from a user. My understanding is that a Pass-Through query is the efficient approach to retrieve data from a SQL Server, but I don't have much experience playing in this part of Access. Based on Knookie's post at [url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=13137[/url] it appears that perhaps I can use a pass-through and just re-define the actual SQL statement in code. Do I understand that correctly?And perhaps more important will be how do I efficiently get that result set inserted into my Access 2000 table. Do I need a second query that will do the INSERT ... SELECT with the first query as the source for the select? If so, will that retain the benefits of the pass-through aspect in the first query? Does anyone have any better suggestion?Thanks!--------------------------------------------------------Visit the SQLTeam Weblogs at [url]http://weblogs.sqlteam.com[/url]
Query from Pass-Through to Local
Posted - 2003-12-22 : 14:20:21
