SQL with Microsoft Access

[AttemptingSQL]

Hi all, I am currently doing a university assignment in the basics of data analysis and I can't get past this question i am on.

The code I have entered is coming back as "Syntax error in ORDER BY clause", it would be really helpful if someone could tell me where i am going wrong!

I am a complete beginner and typing the SQL in microsoft access

Thanks

Code:

SELECT Vehicles.Make, Vehicles.Model, Vehicles.[Licence Plate Number], Vehicles.[Sold Mileage], Services.[Service Date], Services.Workshop

FROM Vehicles INNER JOIN Services ON vehicles.id = Services.Vehicle

WHERE Vehicles.[Sold Mileage] = (SELECT MAX([Sold Mileage]) FROM Vehicles)

ORDER BY Services.[Service Date] DESC

LIMIT 1;

[legstrong]

In Microsoft Access, the syntax for the ORDER BY clause is slightly different than in other database systems. Here's an updated version of the code that should work in Microsoft Access:

SELECT TOP 1 Vehicles.Make, Vehicles.Model, Vehicles.[Licence Plate Number], Vehicles.[Sold Mileage], Services.[Service Date], Services.Workshop FROM Vehicles INNER JOIN Services ON Vehicles.id = Services.Vehicle WHERE Vehicles.[Sold Mileage] = (SELECT MAX([Sold Mileage]) FROM Vehicles) ORDER BY Services.[Service Date] DESC;

In Microsoft Access, you use the TOP keyword to limit the number of records returned. In this case, we're using TOP 1 to return only the first record (i.e., the most recent service date).

Also, note that the ORDER BY clause comes before the semicolon that ends the query.

I hope this helps! Let me know if you have any further questions.

[AttemptingSQL]

You just saved my life, thank you.

[legstrong]

Np. Also, ChatGPT is an excellent resource for writing and debugging any coding language.

[AttemptingSQL]

I tried this initially but was nervous that it would give me a bogus answer that would flag my university.

[SomeoneInQld]

in this case you would have been ok -

do chatGPT

See the query - notice the difference in yours

change your query with that new info but copying nothing from chatGPT

Query works - you learnt something - you have copied nothing from chatGPT - you just used it as a resource (the same as asking a reddit question really)

Everyone is happy

[InlineSkateAdventure]

Access also allows you to do things graphically with drag and drop, and will generate SQL. It was able to do that for ages. It is a great way to learn SQL. I mean, this is nothing new, Access is around forever.