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

 

[Micron]

If there is a bold word in that I don't see it.

 

[Micron]

I'm guessing that you attempted to bold the word by using html tags: [ b ] as [ / b ] seta

I'm not 100% sure but I don't believe you can alias one side of a join.

 

[james_lankford]

try making this
select top 3 (PNr) from [Prüfungen pro Anlage]) as a2
its own query

name it list_top_3

and then do

select a1.* from [Prüfungen pro Anlage] as a1
INNER JOIN
list_top_3 as a2
ON a1.PNr = a2.PNr

 

[dahin]

try making this
select top 3 (PNr) from [Prüfungen pro Anlage]) as a2
its own query

name it list_top_3

and then do

select a1.* from [Prüfungen pro Anlage] as a1
INNER JOIN
list_top_3 as a2
ON a1.PNr = a2.PNr

I tired your suggestion and it said "Syntax Error in JOIN Operation", then marked the "[Prüfungen pro Anlage]" in "select a1.* from [Prüfungen pro Anlage] as a1".
So I took a1 and made it its own query, but got a FROM-error again, marking the "as" in "as seta". So I made another query encapsulating the join of the before created queries, so it looked like this:

Internverantwortlicher INNER JOIN 
top3PpAall
ON Internverantwortlicher.[Interner Verantwortlicher] = top3PpAall.[Interner Verantwortlicher]

But then joins after this one then threw JOIN-errors marking the table mentions of "[Prüfungen pro Anlage]", so I switched all those with "top3PpAll" (the encapsulating 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, top3PpAall.PNr, top3PpAall.PrInt, top3PpAall.GesGrundl, top3PpAall.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 
top3PpAall
ON Internverantwortlicher.[Interner Verantwortlicher] = top3PpAall.[Interner Verantwortlicher]
)
ON [Sparten Interner Verantwortlicher].Sparten = Internverantwortlicher.Sparte) ON [Allgemeine Daten].CBNummer = top3PpAall.CBNummer) INNER JOIN Prüfungsdatenbank ON top3PpAall.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;

But now it says that the field "top3PpAall.PNr" refers to more than one table in the FROM-clause.
I can only solve this via aliases (as), but as soon as I put an "as x" somewhere, I get an error marking the "as"

 

[BigDat6CC]

Please excuse me for coming to this post a bit late. I may have missed an important point, but have you tried setting up your query in Access's design view and then to switch to SQL to see what it has made of the code?

Regards, David

 

[JonXL]

But now it says that the field "top3PpAall.PNr" refers to more than one table in the FROM-clause.
I can only solve this via aliases (as), but as soon as I put an "as x" somewhere, I get an error marking the "as"

Get rid of the AS perhaps? Doubtful that'll do the trick since it shouldn't make a difference but it's not needed so maybe worth a try...

 

[dahin]

Get rid of the AS perhaps? Doubtful that'll do the trick since it shouldn't make a difference but it's not needed so maybe worth a try...

There is no "AS" currently, but I get the error that the field "top3PpAall.PNr" refers to more than one table in the FROM-clause.

Please excuse me for coming to this post a bit late. I may have missed an important point, but have you tried setting up your query in Access's design view and then to switch to SQL to see what it has made of the code?

Regards, David

Since in the query wizard there are no options for "top" or similar, I can't.
Also the base query is old and I am just trying to filter it based on one field of one table (I only want the latest X (for testing purposes X=3) PNr shown (from table [Prüfungen pro Anlage])).

 

[JonXL]

There is no "AS" currently, but I get the error that the field "top3PpAall.PNr" refers to more than one table in the FROM-clause.

Are any of the things you're referencing queries?

 

[dahin]

Are any of the things you're referencing queries?

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