CODEWITHSUNDEEP
sqlms-accesssubquery
Houcine Adsens
Houcine Adsens

Reputation: 27

Access SQL Query from another Query

I have a little problem with a piece of SQL code. I have a table Paiements_17_18 and I would like to create a single-line query that calculates:

All this from a sub request of the style SELECT TOP n FROM ....

I tried this: 

SELECT Sum(P.Montant) AS TotalMontant, 
       First(P.Date_Regulation) AS PremièreDate, 
       Last(P.Date_Regulation) AS DernièreDate, 
       First(P.N_Facture) AS PremièreFacture, 
       Last(P.N_Facture) AS DernièreFacture, 
       (SELECT Count(N_Facture) 
        FROM (SELECT DISTINCT N_Facture FROM Paiements_17_18)) AS NombreFactures

FROM (SELECT TOP 5 Paiements_17_18.* 
      FROM Paiements_17_18 
      ORDER BY Paiements_17_18.ID_Paiement DESC) AS P;

But I get an error of "P"

(The Microsoft Access database engine cannot find the input table or query" P" . Make sure it exists and that its name is spelled correctly)

Can you help me please?

Upvotes: 0

Views: 83

Answers (1)

kc2018
kc2018

Reputation: 1460

The 2 lines on generating the NombreFacture field is causing the error:

(SELECT Count(N_Facture) 
        FROM (SELECT DISTINCT N_Facture FROM Paiements_17_18)) AS 
NombreFactures

Replaced the two lines. See below.

SELECT Sum(P.Montant) AS TotalMontant, 
       First(P.Date_Regulation) AS PremièreDate, 
       Last(P.Date_Regulation) AS DernièreDate, 
       First(P.N_Facture) AS PremièreFacture, 
       Last(P.N_Facture) AS DernièreFacture, 
       (SELECT Count(n.N_Facture_distinct) 
        FROM (SELECT DISTINCT N_Facture as N_facture_distinct FROM Paiements_17_18 ) AS n) 
        AS NombreFacture 
FROM (SELECT TOP 5 Paiements_17_18.* 
      FROM Paiements_17_18 
      ORDER BY Paiements_17_18.ID_Paiement DESC) AS P;

Upvotes: 1

Related Questions