Large Queries

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I've one APPEND query, but I use another 'SELECT' query for selecting
records to be inserted... Following is the query...

PARAMETERS p_MonthLastDate DateTime;
INSERT INTO EmpInctStats
(Emp_ID,Total_Contribution,Sta_ID,Dis_ID,Trn_Date,Trn_Year,Trn_Month)
SELECT DISTINCT p.Emp_ID, (SELECT TOP 1 ECont.Total_Contribution FROM
EmpIncrStats ECont
WHERE ECont.Emp_ID=p.Emp_ID
AND ECont.Trn_Date<p_MonthLastDate
ORDER BY ECont.Trn_Date DESC) AS Total_Contribution, 1, 3, p_MonthLastDate,
YEAR(p_MonthLastDate), MONTH(p_MonthLastDate)
FROM EmpIncrStats AS p
WHERE p.Emp_ID NOT IN
(SELECT p1.Emp_ID FROM EmpIncrStats p1
WHERE p1.Trn_Date=p_MonthLastDate)
ORDER BY p.Emp_ID;

But the query isn't working... my machine stops responding after a couple of
minutes...
On the same data imported into SQL Server 2000 and T-SQL version of the same
query works fine and executes within 2 seconds...

Can anybody help me with this?
 
Try to break it up to multiple queries. This thing is impossible to debug.

--
Steve Clark, Access MVP
FMS, Inc.
Call us for all of your Access Development Needs!
1-888-220-6234
(e-mail address removed)
www.fmsinc.com/consulting
 
Access may be failing on the sub-query in the SELECT clause. IF I remember
correctly, T-SQL will work with this as long as Top 1 returns only one record -
which it does unless you modify the TOP with WITH TIES. Access on the other
hand will return TIES, so that could be causing a failure. As a test, try
removing the Total_Contribution column and see if this runs for you.

I'm surprised that you don't get an error message about "only one value" can be returned.

I can't come up with an SQL statement that would work in Access to give you the
results you are trying to get in the sub-query. Perhaps someone else can.

PARAMETERS p_MonthLastDate DateTime;
INSERT INTO EmpInctStats
(Emp_ID,Total_Contribution,Sta_ID,Dis_ID,Trn_Date,Trn_Year,Trn_Month)
SELECT DISTINCT p.Emp_ID,

(SELECT TOP 1 ECont.Total_Contribution
FROM EmpIncrStats ECont
WHERE ECont.Emp_ID=p.Emp_ID
AND ECont.Trn_Date<p_MonthLastDate
ORDER BY ECont.Trn_Date DESC) AS Total_Contribution,

1, 3,
p_MonthLastDate, YEAR(p_MonthLastDate), MONTH(p_MonthLastDate)
FROM EmpIncrStats AS p
WHERE p.Emp_ID NOT IN
(SELECT p1.Emp_ID FROM EmpIncrStats p1
WHERE p1.Trn_Date=p_MonthLastDate)
 
Back
Top