SQL in microsoft access?

Question

Hello everyone! In fact I am a 15 yr old student who has a complete walkthrough on sql... now i wanna use it in access. my question is that how i do that? where do i type the sql commands? is it when doing queries? like where do i write them? in tables? where?

Answer 1

Go into the Queries object (listed under Tables). Click "New", a dialog box comes up. Select "Design view". The query design screen opens up, and a second box comes up, prompting you to add a table or query.



If you want to go straight to SQL, just close this box, and on the menu click View -> SQL.



Jim is completely wrong about Access, Access can certainly handle complex queries in forms and reports. I have built a complete book lending & circulation system using Access, with multiple forms, reports, label printing etc.

Answer 2

access uses a very small subset of SQL, and it's somewhat mangled. sorry to disappoint. it's also a single-user database. if you want a multi-user database with forms and reports, or something you can put on IIS and serve up over the web, get Microsoft Visual FoxPro.

I know someone who uses it to make accounting /tax applications for companies.

it also works with Crystal Reports, an old standard in the industry (there are easier and more interesting ones now, such as Jasper Reports).



the SQL language to each "software package" is somewhat different. mysql vs postgresql vs access vs foxpro vs db2 vs oracle vs sybase vs MS sql server.



in access you have to be in design mode of the query section. you have to dig for it. I don't think it can do SELECT...INNER JOINs or nested SELECTs and things like that, but you can certainly try it and experiment. you may end up with a useful app. note that forms are made to only work with one table at one time, unfortunately.



one thing I have not tried is to drive a form from the database engine directly from multiple table sources. I don't know if that is possible. it would be cool if you could. but then maybe that is why you have FoxPro.



mysql is free for web use, postgresql is free period, even for embedding (mysql costs money to make an embedded application). if you are just looking to learn SQL and tinker with queries, those would be good to work with. you are more likely to run into mysql for web apps (postgres/pgsql is also used for web apps, but not every server supports that database).

Answer 3

Access uses a wizard to create QBE (query-by-example) SQL queries, but you can also write queries yourself from scratch (or better still, use the wizard to create the skeleton and then modify it) by opening the SQL View window.

Answer 4

decide on t1.* from youTable AS t1 connect (decide on CompanyName, max(EvaluationYear) AS myear from youTable group via CompanyName) AS t2 on t1.CompanyName = t2.CompanyName and t1.EvaluationYear = t2.myear