CODEWITHSUNDEEP
sqlsql-serverms-access
Michael
Michael

Reputation: 141

Convert SQL Server query to access

I've got the following SQL Server query and can not for the love of God get this to work in MS Access.

select 
   U.voorstellingnummer, U.uitvoeringnummer, U.zaalnaam, S.rijnummer, S.stoelnummer
from 
   Uitvoering as U 
inner join 
   Stoel as S on U.zaalnaam = S.zaalnaam
where 
   U.voorstellingnummer = 4 AND U.uitvoeringnummer = 1 
   AND
   -- (S.rijnummer, S.stoelnummer) not in (select(B.rijnummer, B.stoelnummer)
   ('rij '+ cast(S.rijnummer as varchar(3)) + ' stoel ' + cast(S.stoelnummer as varchar(3))) not in  
         (select('rij '+ cast(B.rijnummer as varchar(3)) + ' stoel ' + cast(B.stoelnummer as varchar(3)))
         from Bezetting as B
         where B.voorstellingnummer = 4 AND B.uitvoeringnummer = 1)

I've been add it for a couple of days now but it keeps giving me syntax errors on the first AS.

The query works perfect in SQL Server. What am I doing wrong?

Thanks for your time.

Upvotes: 0

Views: 507

Answers (1)

BAReese
BAReese

Reputation: 501

I typically avoid Access like the plague if I can lol.

However, it looks like your problem might be in the casts. If I remember right, Access will do an implicit conversion from numeric to char. If that's the case, this query should work:

select      U.voorstellingnummer, U.uitvoeringnummer, U.zaalnaam, S.rijnummer, S.stoelnummer
from        Uitvoering as U
inner join  Stoel as S
    on      U.zaalnaam = S.zaalnaam
where       U.voorstellingnummer = 4
    AND     U.uitvoeringnummer = 1
    AND     ('rij ' & S.rijnummer & ' stoel '  & S.stoelnummer) not in 
        (
        select      ('rij ' & B.rijnummer & ' stoel ' & B.stoelnummer)
        from        Bezetting as B
        where       B.voorstellingnummer = 4
            AND     B.uitvoeringnummer = 1
        )

If the implicit conversions don't work, Cstr() should convert the numbers to strings. If you want only the left 3 characters, you'd probably need to do something like left(cstr(S.rijnummer),3).

Edit: Also, I'm not entirely sure you can use a subquery in the were clause. If not, you might need to make the majority of this a sub query then left join Bezetting to it and use where isnull(Bezetting.rijnummer) - or whatever it is in Access :-)

Upvotes: 1

Related Questions