convert SQL 7 query to work with JET 
Author Message
 convert SQL 7 query to work with JET

Hi everyone,

Could someone please help me convert the following SQL 7.0 query into a
format that will run against my Jet 3.5 Provider?

Select emp.sEmpIDf, emp.sSSN, emp.sLastName,
         Emp.sFirstName, QtdWage.qWage, YdtWage.yWage,
         YtdTax.taxAmount
From tblPREE

Join (select sum(tblPRHistEarn.curAmount) as qWage
        From tblPRHistEarn Where tblPRHistEarn.dtmPayDate >=
        01/01/00 and tblPRHistEarn.dtmPayDate <= 12/31/00) as
            QtdWage
        on Emp.sEmpIDf = tblPRHistEarn.sEmpIDf
Join (select sum(tblPRHistEarn.curAmount) as yWage
       From tblPRHistEarn Where tblPRHistEarn.dtmPayDate >=
       01/01/00 and tblPRHistEarn.dtmPayDate <= 12/31/00) as
           YtdWage
       on Emp.sEmpIDf = tblPRHistEarn.sEmpIDf
Join (Select sum(tblPRHistTaxes.curEEAmount) as taxAmount
       From tblPRHistTaxes Where (tblPRHistTaxes.sTaxTypeIDf =
          'SWT')AND tblPRHistEarn.dtmPayDate >= 01/01/00 and
          tblPRHistEarn.dtmPayDate <= 12/31/00) as YtdTax
       On Emp.sEmpIDf = tblPRHistTaxes.sEmployeeIDf

Thanks very much!!!

Erica



Mon, 19 Aug 2002 03:00:00 GMT  
 convert SQL 7 query to work with JET
Erica,

I just quickly reviewed it and I believe you should enclose the date values
in #, i.e.

 Join (select sum(tblPRHistEarn.curAmount) as qWage
         From tblPRHistEarn Where tblPRHistEarn.dtmPayDate >= #01/01/00# and
tblPRHistEarn.dtmPayDate <= #12/31/00#) as
             QtdWage on Emp.sEmpIDf = tblPRHistEarn.sEmpIDf

That ought to do it.
--
Regards

Carsten Thomsen
MCSE/MVP VB/VB Analyst & Programmer
------------------------------------------------------------------
Please reply to the newsgroup, so we can all benefit from the discussion!


Quote:
> Hi everyone,

> Could someone please help me convert the following SQL 7.0 query into a
> format that will run against my Jet 3.5 Provider?

> Select emp.sEmpIDf, emp.sSSN, emp.sLastName,
>          Emp.sFirstName, QtdWage.qWage, YdtWage.yWage,
>          YtdTax.taxAmount
> From tblPREE

> Join (select sum(tblPRHistEarn.curAmount) as qWage
>         From tblPRHistEarn Where tblPRHistEarn.dtmPayDate >=
>         01/01/00 and tblPRHistEarn.dtmPayDate <= 12/31/00) as
>             QtdWage
>         on Emp.sEmpIDf = tblPRHistEarn.sEmpIDf
> Join (select sum(tblPRHistEarn.curAmount) as yWage
>        From tblPRHistEarn Where tblPRHistEarn.dtmPayDate >=
>        01/01/00 and tblPRHistEarn.dtmPayDate <= 12/31/00) as
>            YtdWage
>        on Emp.sEmpIDf = tblPRHistEarn.sEmpIDf
> Join (Select sum(tblPRHistTaxes.curEEAmount) as taxAmount
>        From tblPRHistTaxes Where (tblPRHistTaxes.sTaxTypeIDf =
>           'SWT')AND tblPRHistEarn.dtmPayDate >= 01/01/00 and
>           tblPRHistEarn.dtmPayDate <= 12/31/00) as YtdTax
>        On Emp.sEmpIDf = tblPRHistTaxes.sEmployeeIDf

> Thanks very much!!!

> Erica



Tue, 20 Aug 2002 03:00:00 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. DLL for converting queries from Jet to T-SQL

2. Simple jet SQL query will not work for one table but will for another

3. Converting SQL Server/MSDE code to Jet SQL

4. Converting dynamic query by example SQL to RDO based SQL Stored Procedure

5. SQL query works, Crystal query doesn't

6. Insert works in Access query window but not from VB using JET 4.0

7. HELP: Parametrized union queries don't work with Jet 4.0

8. Help converting JET MDB into SQL Server

9. Jet SQL Function for converting datatypes?

10. Executing SQL queries using modules via Jet

11. SQL Query to Access(jet) OLE Container

12. Jet 3.51 with VB5: Large SQL update query misses some records

 

 
Powered by phpBB® Forum Software