SQL problem

[needmoremoney]

I'm getting a sql error. It's quite long but any help from anyone here would greatly be appreciated. I'm not a SQL person at all. The databases are Access 97. Any help, thanks much.
There is no debugging statement to help me debug what is wrong. I just get this big error that it doesn't work. I pass it through a third party AD-Hoc application.
......code....


#set errors off;
DROP TABLE temp401KReport;
DROP TABLE temp401KReport2;
DROP TABLE ADPexportEInfo ;
DROP TABLE ADPexport2 ;
DROP TABLE ADPexportEPayHist ;
DROP TABLE ADPexportEDed ;
DROP TABLE ADPexportCDed ;
DROP TABLE ADPexportEPayHist ;
DROP TABLE ADPexportEPayHistDetail ;
DROP TABLE ADPexportCEarn ;
DROP TABLE ADPexportEYtd ;
#set errors on;

Select *
INTO [\\tpm6\payroll\client\6882].ADPexportEInfo
FROM [\\tpm6\payroll\client\6882].einfo;

Insert into ADPexportEInfo
SELECT *
From [\\tpm6\payroll\client\6883].einfo;

Select *
INTO [\\tpm6\payroll\client\6882].ADPexportEPayHist
FROM [\\tpm6\payroll\client\6882].epayhist;

Insert into ADPexportEPayHist
SELECT *
From [\\tpm6\payroll\client\6883].epayhist;

Select *
INTO [\\tpm6\payroll\client\6882].ADPexportEDed
FROM [\\tpm6\payroll\client\6882].eded;

Insert into ADPexportEDed
SELECT *
From [\\tpm6\payroll\client\6883].eded;

Select *
INTO [\\tpm6\payroll\client\6882].ADPexportCDed
FROM [\\tpm6\payroll\client\6882].cded;

Select *
INTO [\\tpm6\payroll\client\6882].ADPexportEPayHistDetail
FROM [\\tpm6\payroll\client\6882].epayhistdetail;

Insert into ADPexportEPayHistDetail
SELECT *
From [\\tpm6\payroll\client\6883].epayhistdetail;

Select *
INTO [\\tpm6\payroll\client\6882].ADPexportCEarn
FROM [\\tpm6\payroll\client\6882].cearn;

Insert into ADPexportCEarn
SELECT *
From [\\tpm6\payroll\client\6883].cearn;

Select *
INTO [\\tpm6\payroll\client\6882].ADPexportEYtd
FROM [\\tpm6\payroll\client\6882].eytd;

Insert into ADPexportEYtd
SELECT *
From [\\tpm6\payroll\client\6883].eytd;

SELECT co, id, 0.0 AS currGross, 0.0 AS eePercent,
0.0 AS currEePreTax, 0.0 AS currEePostTax,
0.0 AS currEeLoan, 0.0 AS currEeLoan2,
er401KMatch AS erPercent,
0.0 AS currErMatch, 0.0 AS ytdGross, 0.0 AS ytdHours,
0.0 AS ytdEePreTax, 0.0 AS ytdEePostTax,
0.0 AS ytdEeLoan, 0.0 AS ytdErMatch
INTO temp401KReport
FROM ADPexportEInfo;

SELECT id, SUM(gross) AS currGross
INTO temp401KReport2
FROM ADPexportEPayHist
WHERE ADPexportEPayHist.process >= [paramBeginProcess] AND
ADPexportEPayHist.process <= [paramEndProcess]
GROUP BY id;

UPDATE temp401KReport
INNER JOIN temp401KReport2
ON temp401KReport.id = temp401KReport2.id
SET temp401KReport.currGross = temp401KReport2.currGross;

DROP TABLE temp401KReport2;

SELECT ADPexportEDed.id, SUM( ADPexportEDed.rate ) AS eePercent
INTO temp401KReport2
FROM ADPexportEDed
INNER JOIN ADPexportCDed
ON ADPexportEDed.dCode = ADPexportCDed.dCode
WHERE ADPexportCDed.dedType = "401k"
GROUP BY ADPexportEDed.id;

UPDATE temp401KReport
INNER JOIN temp401KReport2
ON temp401KReport.id = temp401KReport2.id
SET temp401KReport.eePercent = temp401KReport2.eePercent;

DROP TABLE temp401KReport2;

SELECT ADPexportEPayHist.id, SUM( ADPexportEPayHistDetail.amount) AS currEePreTax
INTO temp401KReport2
FROM (ADPexportEPayHist
INNER JOIN ADPexportEPayHistDetail
ON ADPexportEPayHist.transaction = ADPexportEPayHistDetail.transaction)
INNER JOIN ADPexportCDed
ON ADPexportEPayHistDetail.detCode = ADPexportCDed.dCode
WHERE ADPexportEPayHist.process >= [paramBeginProcess] AND
ADPexportEPayHist.process <= [paramEndProcess] AND
ADPexportEPayHistDetail.transactionType <> "X" AND
ADPexportEPayHistDetail.det = "D" AND
ADPexportCDed.dedType = "401k"
GROUP BY ADPexportEPayHist.id;

UPDATE temp401KReport
INNER JOIN temp401KReport2
ON temp401KReport.id = temp401KReport2.id
SET temp401KReport.currEePreTax = temp401KReport2.currEePreTax;

DROP TABLE temp401KReport2;

SELECT ADPexportEPayHist.id, SUM( ADPexportEPayHistDetail.amount) AS currEePostTax
INTO temp401KReport2
FROM (ADPexportEPayHist
INNER JOIN ADPexportEPayHistDetail
ON ADPexportEPayHist.transaction = ADPexportEPayHistDetail.transaction)
INNER JOIN ADPexportCDed
ON ADPexportEPayHistDetail.detCode = ADPexportCDed.dCode
WHERE ADPexportEPayHist.process >= [paramBeginProcess] AND
ADPexportEPayHist.process <= [paramEndProcess] AND
ADPexportEPayHistDetail.transactionType <> "X" AND
ADPexportEPayHistDetail.det = "D" AND
ADPexportCDed.dedType = "401k Over"
GROUP BY ADPexportEPayHist.id;

UPDATE temp401KReport
INNER JOIN temp401KReport2
ON temp401KReport.id = temp401KReport2.id
SET temp401KReport.currEePostTax = temp401KReport2.currEePostTax;

DROP TABLE temp401KReport2;

SELECT ADPexportEPayHist.id, SUM( ADPexportEPayHistDetail.amount) AS currEeLoan
INTO temp401KReport2
FROM (ADPexportEPayHist
INNER JOIN ADPexportEPayHistDetail
ON ADPexportEPayHist.transaction = ADPexportEPayHistDetail.transaction)
INNER JOIN ADPexportCDed
ON ADPexportEPayHistDetail.detCode = ADPexportCDed.dCode
WHERE ADPexportEPayHist.process >= [paramBeginProcess] AND
ADPexportEPayHist.process <= [paramEndProcess] AND
ADPexportEPayHistDetail.transactionType <> "X" AND
ADPexportEPayHistDetail.det = "D" AND
ADPexportCDed.dedType = "401k Loan"
GROUP BY ADPexportEPayHist.id;

UPDATE temp401KReport
INNER JOIN temp401KReport2
ON temp401KReport.id = temp401KReport2.id
SET temp401KReport.currEeLoan = temp401KReport2.currEeLoan;

DROP TABLE temp401KReport2;

SELECT ADPexportEPayHist.id, SUM( ADPexportEPayHistDetail.amount) AS currErMatch
INTO temp401KReport2
FROM (ADPexportEPayHist
INNER JOIN ADPexportEPayHistDetail
ON ADPexportEPayHist.transaction = ADPexportEPayHistDetail.transaction)
INNER JOIN ADPexportCEarn
ON ADPexportEPayHistDetail.detCode = ADPexportCEarn.eCode
WHERE ADPexportEPayHist.process >= [paramBeginProcess] AND
ADPexportEPayHist.process <= [paramEndProcess] AND
ADPexportEPayHistDetail.transactionType <> "X" AND
ADPexportEPayHistDetail.det = "E" AND
ADPexportCEarn.earnTypeCode = "MemoErMatch"
GROUP BY ADPexportEPayHist.id;

UPDATE temp401KReport
INNER JOIN temp401KReport2
ON temp401KReport.id = temp401KReport2.id
SET temp401KReport.currErMatch = temp401KReport2.currErMatch;

DROP TABLE temp401KReport2;

SELECT id, SUM(gross) AS ytdGross
INTO temp401KReport2
FROM ADPexportEPayHist
WHERE ADPexportEPayHist.year = [paramYear]
GROUP BY id;

UPDATE temp401KReport
INNER JOIN temp401KReport2
ON temp401KReport.id = temp401KReport2.id
SET temp401KReport.ytdGross = temp401KReport2.ytdGross;

DROP TABLE temp401KReport2;

SELECT id, SUM(hours) AS ytdHours
INTO temp401KReport2
FROM ADPexportEPayHist
WHERE ADPexportEPayHist.year = [paramYear]
GROUP BY id;

UPDATE temp401KReport
INNER JOIN temp401KReport2
ON temp401KReport.id = temp401KReport2.id
SET temp401KReport.ytdHours = temp401KReport2.ytdHours;

DROP TABLE temp401KReport2;

SELECT EYtd.id, SUM( ADPexportEYtd.aamount) AS ytdEePreTax
INTO temp401KReport2
FROM ADPexportEYtd
INNER JOIN ADPexportCDed
ON ADPexportEYtd.detCode = ADPexportCDed.dCode
WHERE ADPexportEYtd.year = [paramYear] AND
ADPexportEYtd.det = "D" AND
ADPexportCDed.dedType = "401k"
GROUP BY ADPexportEYtd.id;

UPDATE temp401KReport
INNER JOIN temp401KReport2
ON temp401KReport.id = temp401KReport2.id
SET temp401KReport.ytdEePreTax = temp401KReport2.ytdEePreTax;

DROP TABLE temp401KReport2;

SELECT ADPexportEYtd.id, SUM( ADPexportEYtd.aamount) AS ytdEePostTax
INTO temp401KReport2
FROM ADPexportEYtd
INNER JOIN ADPexportCDed
ON ADPexportEYtd.detCode = ADPexportCDed.dCode
WHERE ADPexportEYtd.year = [paramYear] AND
ADPexportEYtd.det = "D" AND
ADPexportCDed.dedType = "401k Over"
GROUP BY ADPexportEYtd.id;

UPDATE temp401KReport
INNER JOIN temp401KReport2
ON temp401KReport.id = temp401KReport2.id
SET temp401KReport.ytdEePostTax = temp401KReport2.ytdEePostTax;

DROP TABLE temp401KReport2;

SELECT ADPexportEYtd.id, SUM( ADPexportEYtd.aamount) AS ytdEeLoan
INTO temp401KReport2
FROM ADPexportEYtd
INNER JOIN ADPexportCDed
ON ADPexportEYtd.detCode = ADPexportCDed.dCode
WHERE ADPexportEYtd.year = [paramYear] AND
ADPexportEYtd.det = "D" AND
ADPexportCDed.dedType = "401k Loan"
GROUP BY ADPexportEYtd.id;

UPDATE temp401KReport
INNER JOIN temp401KReport2
ON temp401KReport.id = temp401KReport2.id
SET temp401KReport.ytdEeLoan = temp401KReport2.ytdEeLoan;

DROP TABLE temp401KReport2;

SELECT ADPexportEYtd.id, SUM( ADPexportEYtd.aamount) AS ytdErMatch
INTO temp401KReport2
FROM ADPexportEYtd
INNER JOIN ADPexportCEarn
ON ADPexportEYtd.detCode = ADPexportCEarn.eCode
WHERE ADPexportEYtd.year = [paramYear] AND
ADPexportEYtd.det = "E" AND
ADPexportCEarn.earnTypeCode = "MemoErMatch"
GROUP BY ADPexportEYtd.id;

UPDATE temp401KReport
INNER JOIN temp401KReport2
ON temp401KReport.id = temp401KReport2.id
SET temp401KReport.ytdErMatch = temp401KReport2.ytdErMatch;


DROP TABLE temp401KReport2;

...........code end......

 

[dhookom]

Do you realize that an Access query supports only a single SQL statement? This ain't SQL Server.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[needmoremoney]

I use AD-HOC.

 

[PHV]

And AD-HOC supports the following syntax ?
INTO [\\tpm6\payroll\client\6882].ADPexportEInfo
FROM [\\tpm6\payroll\client\6882].einfo

JetSQL doesn't.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886