1

I've made a old question but I wasnt able to get it working with the answers: Old link

Someone gave me this answer :

SELECT 
        SUM(TotaalPrijs) As TotaalPrijs,
        AutoNR,
        AutoKlasse,
        MAX(Dagen) as Dagen,
        dag125KM as PrijsPerDag,
        ExtraKM As PrijsPerExtraKM,
        FactuurNR,
        KlantNR,
        Begindatum,
        Einddatum,
        Borg,
        voorletters,
        tussenvoegsel,
        achternaam,
        straatnaam,
        huisNR,
        Postcode,
        rekeningNR,
        Plaats,
        KMteVEEL,
        BorgA
        FROM     (SELECT p.Borg as BorgA,f.Dagen, f.AutoNR AS carNR, a.AutoNR, a.Klasse AS AutoKlasse, p.Klasse, p.dag125KM, p.ExtraKM, (p.dag125KM*f.Dagen) AS MinPrijs, f.FactuurNR, f.KlantNR, f.Begindatum, f.Einddatum, f.Borg, (KMteVEEL*[Prijzen]![ExtraKM])+([Prijzen]![dag125KM]*[Factuur]![Dagen]) AS TotaalPrijs, g.voorletters, g.tussenvoegsel, g.achternaam, g.straatnaam, g.huisNR, g.Postcode, g.rekeningNR, g.Plaats, IIf([Factuur]![EindKMStand]-[Factuur]![BeginKMStand]-([Factuur]![Dagen]*125) < 0, 0, [Factuur]![EindKMStand]-[Factuur]![BeginKMStand]-([Factuur]![Dagen]*125))  AS KMteVEEL
                  FROM autos a
                  INNER JOIN Factuur f  ON a.AutoNR = f.AutoNR
                  INNER JOIN Prijzen p  ON a.Klasse = p.Klasse
                  INNER JOIN Gegevens g ON f.KlantNR = g.KlantNR    

       ) AS sub
GROUP BY AutoNR,
        AutoKlasse,
        dag125KM, 
        ExtraKM,
        FactuurNR,
        KlantNR,
        Begindatum,
        Einddatum,
        Borg,
        voorletters,
        tussenvoegsel,
        achternaam,
        straatnaam,
        huisNR,
        Postcode,
        rekeningNR,
        Plaats,
        KMteVEEL,
        BorgA

But I am getting a syntax error missing operator. Any way to get this working? You might want to check the old question out to understand the case better.

Thanks,

Improve this question
2
  • Can you show the error message? Commented Jan 13, 2015 at 11:16
  • @Jens Im getting Syntax Error Missing operator. Screenshot: gyazo.com/efb7149c3e624d0c2eb5ada56df9a6d9 Commented Jan 13, 2015 at 11:31

1 Answer 1

Reset to default
2

MS Access requires parentheses around join operators as as for table aliases. Replace the from with:

FROM (SELECT p.Borg as BorgA, f.Dagen, f.AutoNR AS carNR, a.AutoNR, a.Klasse AS AutoKlasse, p.Klasse, 
             p.dag125KM, p.ExtraKM, (p.dag125KM*f.Dagen) AS MinPrijs, f.FactuurNR, f.KlantNR, 
             f.Begindatum, f.Einddatum, f.Borg,
             (KMteVEEL*[Prijzen]![ExtraKM])+([Prijzen]![dag125KM]*[Factuur]![Dagen]) AS TotaalPrijs,
             g.voorletters, g.tussenvoegsel, g.achternaam, g.straatnaam, g.huisNR, g.Postcode, 
             g.rekeningNR, g.Plaats,
             IIf([Factuur]![EindKMStand]-[Factuur]![BeginKMStand]-([Factuur]![Dagen]*125) < 0, 0, [Factuur]![EindKMStand]-[Factuur]![BeginKMStand]-([Factuur]![Dagen]*125))  AS KMteVEEL
      FROM ((autos as a INNER JOIN
             Factuur as f
             ON a.AutoNR = f.AutoNR
            ) INNER JOIN
            Prijzen as p
            ON a.Klasse = p.Klasse
           ) INNER JOIN
           Gegevens as g
           ON f.KlantNR = g.KlantNR  
    ) sub

Of course, there might also be a problem with the arithmetic logic in the subquery. Focus on getting the subquery to work first.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Thanks for your answer but now I am getting this error: gyazo.com/a8ef67d4e8ee886bbd6665963ed46f97
@Jesse . . . Try using a different name for the alias in the outer select.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.