Large Queries

E

El Cid

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?
 
J

John Spencer (MVP)

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)
 
E

El Cid

Hi John,

Thank you for the reply.
I've already tried what you've advised.
One more thing, it isn't returning multiple rows to me and hence no errors
are occuring. My problem is that Access goes down after 2 minutes and it also
takes my OS with it.

Well, I also read about Provider specific properties like 'Jet OLEDB:Enable
Fat Cursors". Can you help me with that? I'm using VB6 (SP5) with ADO 2.7 and
Access2000 for this software. I'll appreciate further guidance please.

Regards,
Bharat Tamhankar
 
J

John Spencer (MVP)

Sorry, I can't offer you any further advice. Perhaps you can repost your problem.
 
Top