Access SQL: Syntax Error in FROM clause

[dahin]

Hey dear Access-Community,

I am trying to adapt an old access query at work (I have not used access in more than 10 years), but the SQL syntax is killing me.
What I am trying to do is to just show the results of the query with the latest 3 numbers of a counter value (currently using "TOP 3" just to try if things work).
This is the original query:

SELECT [Allgemeine Daten].CBNummer, [Allgemeine Daten].Bezeichnung, [Allgemeine Daten].Standort, [Allgemeine Daten].Typennummer, [Allgemeine Daten].Hersteller, [Allgemeine Daten].Baujahr, [Allgemeine Daten].Prüfbuchnummer, [Allgemeine Daten].Ausgeschieden, [Prüfungen pro Anlage].PNr, [Prüfungen pro Anlage].PrInt, [Prüfungen pro Anlage].GesGrundl, [Prüfungen pro Anlage].NächstesPrüfdatum, Internverantwortlicher.[Interner Verantwortlicher], Betriebsstätten.Betriebsstätten, [Sparten Interner Verantwortlicher].Sparten, Prüfungsdatenbank.Firma
FROM ((Betriebsstätten INNER JOIN [Allgemeine Daten] ON Betriebsstätten.Betriebsstätten = [Allgemeine Daten].Betriebsstätte) INNER JOIN ([Sparten Interner Verantwortlicher] INNER JOIN (Internverantwortlicher INNER JOIN [Prüfungen pro Anlage] ON Internverantwortlicher.[Interner Verantwortlicher] = [Prüfungen pro Anlage].[Interner Verantwortlicher]) ON [Sparten Interner Verantwortlicher].Sparten = Internverantwortlicher.Sparte) ON [Allgemeine Daten].CBNummer = [Prüfungen pro Anlage].CBNummer) INNER JOIN Prüfungsdatenbank ON [Prüfungen pro Anlage].PNr = Prüfungsdatenbank.PNr
WHERE ((([Allgemeine Daten].Ausgeschieden)=No) AND ((Betriebsstätten.Betriebsstätten) Between [Formulare]![frmberichtsmenü]![Kombinationsfeld91] And [Formulare]![frmberichtsmenü]![Kombinationsfeld91]) AND (([Sparten Interner Verantwortlicher].Sparten) Between [Formulare]![frmberichtsmenü]![Kombinationsfeld92] And [Formulare]![frmberichtsmenü]![Kombinationsfeld92]))
ORDER BY [Allgemeine Daten].CBNummer;

This is the adapted query (in bold the word which gets marked after the error has been thrown):

SELECT [Allgemeine Daten].CBNummer, [Allgemeine Daten].Bezeichnung, [Allgemeine Daten].Standort, [Allgemeine Daten].Typennummer, [Allgemeine Daten].Hersteller, [Allgemeine Daten].Baujahr, [Allgemeine Daten].Prüfbuchnummer, [Allgemeine Daten].Ausgeschieden, seta.PNr, seta.PrInt, seta.GesGrundl, seta.NächstesPrüfdatum, Internverantwortlicher.[Interner Verantwortlicher], Betriebsstätten.Betriebsstätten, [Sparten Interner Verantwortlicher].Sparten, Prüfungsdatenbank.Firma
FROM ((Betriebsstätten INNER JOIN [Allgemeine Daten] ON Betriebsstätten.Betriebsstätten = [Allgemeine Daten].Betriebsstätte) INNER JOIN ([Sparten Interner Verantwortlicher] INNER JOIN
(
Internverantwortlicher INNER JOIN 
(
(select * from [Prüfungen pro Anlage]) as a1 
INNER JOIN
(select top 3 (PNr) from [Prüfungen pro Anlage]) as a2
ON a1.PNr = a2.PNr
) [B]as[/B] seta
ON Internverantwortlicher.[Interner Verantwortlicher] = seta.[Interner Verantwortlicher]
)
ON [Sparten Interner Verantwortlicher].Sparten = Internverantwortlicher.Sparte) ON [Allgemeine Daten].CBNummer = [Prüfungen pro Anlage].CBNummer) INNER JOIN Prüfungsdatenbank ON [Prüfungen pro Anlage].PNr = Prüfungsdatenbank.PNr
WHERE ((([Allgemeine Daten].Ausgeschieden)=No) AND ((Betriebsstätten.Betriebsstätten) Between [Formulare]![frmberichtsmenü]![Kombinationsfeld91] And [Formulare]![frmberichtsmenü]![Kombinationsfeld91]) AND (([Sparten Interner Verantwortlicher].Sparten) Between [Formulare]![frmberichtsmenü]![Kombinationsfeld92] And [Formulare]![frmberichtsmenü]![Kombinationsfeld92]))
ORDER BY [Allgemeine Daten].CBNummer;

I have tried using more brackets, but to no avail.
I would appreciate any help I can get.

BR
dahin

 

[JonXL]

top3PpAall is a query consisting of queries (to circumvent all the errors I received before^^).

I would look through those queries (and any queries within them) to see how that field is referenced and displayed. I've had this issue with sub queries before that run separately by themselves but don't roll up right into other queries because of this field name conflict. The fix I use is to make the name of the field as displayed in the sub queries unique by listing them with AS in the SQL (give them an alias).

 

[dahin]

I would look through those queries (and any queries within them) to see how that field is referenced and displayed. I've had this issue with sub queries before that run separately by themselves but don't roll up right into other queries because of this field name conflict. The fix I use is to make the name of the field as displayed in the sub queries unique by listing them with AS in the SQL (give them an alias).

OK, now I don't get any errors, but if I want to run the report, I get a popup saying "PNr" waiting for me to fill something in (it should run automatically, though).

My Queries look as follows:

Main Query:

SELECT [Allgemeine Daten].CBNummer, [Allgemeine Daten].Bezeichnung, [Allgemeine Daten].Standort, [Allgemeine Daten].Typennummer, [Allgemeine Daten].Hersteller, [Allgemeine Daten].Baujahr, [Allgemeine Daten].Prüfbuchnummer, [Allgemeine Daten].Ausgeschieden, zz_PNrSort.PNrx, zz_PNrSort.PrInt, zz_PNrSort.GesGrundl, zz_PNrSort.NächstesPrüfdatum, Internverantwortlicher.[Interner Verantwortlicher], Betriebsstätten.Betriebsstätten, [Sparten Interner Verantwortlicher].Sparten, Prüfungsdatenbank.Firma
FROM ((Betriebsstätten INNER JOIN [Allgemeine Daten] ON Betriebsstätten.Betriebsstätten = [Allgemeine Daten].Betriebsstätte) INNER JOIN ([Sparten Interner Verantwortlicher] INNER JOIN
(
Internverantwortlicher INNER JOIN
zz_PNrSort
ON Internverantwortlicher.[Interner Verantwortlicher] = zz_PNrSort.[Interner Verantwortlicher]
)
ON [Sparten Interner Verantwortlicher].Sparten = Internverantwortlicher.Sparte) ON [Allgemeine Daten].CBNummer = zz_PNrSort.CBNummer) INNER JOIN Prüfungsdatenbank ON zz_PNrSort.PNrx = Prüfungsdatenbank.PNr
WHERE ((([Allgemeine Daten].Ausgeschieden)=No) AND ((Betriebsstätten.Betriebsstätten) Between [Formulare]![frmberichtsmenü]![Kombinationsfeld91] And [Formulare]![frmberichtsmenü]![Kombinationsfeld91]) AND (([Sparten Interner Verantwortlicher].Sparten) Between [Formulare]![frmberichtsmenü]![Kombinationsfeld92] And [Formulare]![frmberichtsmenü]![Kombinationsfeld92]))
ORDER BY [Allgemeine Daten].CBNummer;

zz_PNrSort:

SELECT B.PNr AS PNrx, CBNummer, PrInt, GesGrundl, NächstesPrüfdatum, [Interner Verantwortlicher]
FROM zz_alleFelder_PpA AS A INNER JOIN zz_Top3_PNr AS B ON A.PNr = B.PNr;

zz_Top3_PNr:

SELECT TOP 3 (PNr)
FROM [Prüfungen pro Anlage]
ORDER BY PNr DESC;

zz_alleFelder_PpA:

SELECT *
FROM [Prüfungen pro Anlage];

I don't know which PNr is showing up - does access not know where to get the value from? Why?

 

[JonXL]

Do you get that popup when you run the query or any of its sub queries?

If not, it means you likely still have a control on the report referencing that field which needs to be updated to reference the new PNrx.

 

[dahin]

Do you get that popup when you run the query or any of its sub queries?

If not, it means you likely still have a control on the report referencing that field which needs to be updated to reference the new PNrx.

That was it. The reference needed to be updated. Thanks a bunch.