Access SQL Query from another Query

Question

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:

the total of the Amount field,
the first date of Date_Regulation field,
the last date of Date_Regulation field,
the distinct values of N_Facture field.

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?

If you remove the 2 lines for generating NombreFactures, do you get error? — kc2018
– kc2018, Commented Jan 4, 2018 at 15:21
From my sample data following your logic, I cannot reproduce error. By looking at syntax I cannot see why the error is raised. Make sure this exact query is generating that error. Possibly your actual did not include table alias, P. — Parfait
– Parfait, Commented Jan 4, 2018 at 15:22
@kc2018; Yes when i remove the line generating NombreFactures, there is no more error !! — Houcine Adsens
– Houcine Adsens, Commented Jan 4, 2018 at 15:38
@Parfait; what do you mean by I didn't Include the table's alias P — Houcine Adsens
– Houcine Adsens, Commented Jan 4, 2018 at 15:42
I am saying in your actual query, you may hve missed the P at end even though here it is posted. Very odd! I tested that SELECT Count(...) FROM (SELECT DISTINCT ...) without an issue! — Parfait
– Parfait, Commented Jan 4, 2018 at 15:44

kc2018 · Accepted Answer · 2018-01-04 16:30:28Z

1

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;

edited Jan 4, 2018 at 16:30

answered Jan 4, 2018 at 15:44

kc2018

1,4601 gold badge10 silver badges8 bronze badges

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

8 Comments

Houcine Adsens Over a year ago

@kc2018; I got an error "access circular reference caused by alias [N_Facture] in query definition's SELECT list" :(

kc2018 Over a year ago

I have changed the variable name from N_facture to N_facture_distinct

Parfait Over a year ago

@HoucineAdsens ... please provide sample data of Paiments_17_18, so we can fully reproduce issue.

Houcine Adsens Over a year ago

@kc2018; Finally it works, thank you thank you very very much, but I have another question if you allow me. I want to calculate The Destinct [N_Facture] from P. not from the Table! for exemple if i Have SELECT TOP 5, i want just count destinct [N_Facture] of the 5 first records. Not from all the table

kc2018 Over a year ago

@HoucineAdsens; Try count(distinct P.N_Facture) instead of the 2 lines of subquery.

|

Collectives™ on Stack Overflow

Access SQL Query from another Query

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related