• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Does SQL and MS Access overlap each other ? Are they dependent ?

ThrottleWorks

Excel Ninja
Hi,

I have just started reading soft copy of ‘Microsoft Access 2010’ by Michael R. Groh.
As I was browsing on Mr. Excel’s Access forum, there were various references to SQL.
That is why I have below doubts in my mind.


Does SQL and MS Access overlap each other ?

Are the dependent ?

Do one need to learn both to proceed further, in other words, if I read access only and ignore SQL or vice versa.

Will I be incomplete in my learning.

Can anyone please help me in this.
PS - Reading softcopy first to decide if I should buy hardcopy of the same before going ahead.
 

Chihiro

Excel Ninja
SQL is Structured Query Language. And has many derivatives.

All (to my knowledge) relational database management system (RDBMS) use variant of SQL.

MS SQL uses Transact-SQL, MySQL uses SQL/PSM, Oracle uses PL/SQL etc.

MS Access uses it's own SQL (though similar to TSQL).

There are many things common to all SQL variants, but there are enough differences, most query can't be ported directly from one to the other.

However, if you learn one, it isn't difficult to pick up another.
 

ThrottleWorks

Excel Ninja
Good morning @Chihiro sir. Thanks for the help.

MS Access uses it's own SQL
Sir does this mean SQL mentioned in the first line by you and SQL by MS Access are different. Sorry for making it confusing.

Also, would you recommend to go ahead with ‘Microsoft Access 2010’ by Michael R. Groh. Just as FYI, at present, 99% per cent of my work involves MS Excel.
So, just wanted to know if above mentioned book right for me.

Have a nice day ahead. :)
 

Chihiro

Excel Ninja
Every database management system has it's own variation on SQL. Basic structure and many functions are shared. But there are enough differences between each that direct port of script isn't often possible (you need minor syntax adjustments and such).

There is ANSI and ISO (and few other) standards, but each variant does not necessarily follow that standard. You can read more about it on Wiki (under Interoperability and standardization section). Search for "Wiki SQL"

No idea on the book. I learned everything I know about MS Access, Excel etc from trial and error and through forums and web searches.

Since MS Access is file based and not server based, you can just set up sample database and applications to test to your heart's content.

I'd recommend downloading Northwind from MS.
https://www.thoughtco.com/installing-northwind-sample-database-access-2010-1019696

Edit: Grammar.
 
Last edited:

NARAYANK991

Excel Ninja
Hi ,

While different variants of SQL may have differences , I think that is not a very significant part of learning SQL ; there are different flavours of C , but learning C as a programming language involves much , much more than memorizing the syntax of a hundred commands.

Learning SQL means being able to create queries ; the ability to create complex queries is practically impossible without a proper and deep understanding of how RDBMS work , tables , keys , joins , normalization , relationships ,...

So if you intend to master SQL , take the effort to understand RDBMS.

See this for an introduction :

https://www.tutorialspoint.com/sql/sql-rdbms-concepts.htm

Narayan
 

ThrottleWorks

Excel Ninja
Hi @NARAYANK991 sir, thanks for the help.

Learning SQL means being able to create queries
Sorry for making in confusing. If I am correct, learning MS Access will involve learning SQL as well. Be it MS Access's own SQL or otherwise.

I guess, the SQL queries I have seen on Mr. Excel's Access forum might be related to MS Access's SQL also.

Thanks for the link. I have saved it my favourite.
Also, sir, would you suggest any MS Access book for me.

@Chihiro sir has advised to use forum and web searches.
However, I guess given my learning capacity / type, book will help me a lot.
 

NARAYANK991

Excel Ninja
Hi ,

You are right ; Access is a DBMS , but the major difference is that Access also has VBA as a programming language while other RDBMS have their own programming language in addition to SQL or as extensions of SQL ; Oracle , for example has PL/SQL.

Thus , Access is easier to learn than Oracle , though comparing the two is like comparing chalk and cheese.

However , unless you intend to graduate to being a hard-core programmer working on large data systems , Access is good enough for most small applications.

You can develop applications in Access without having to learn too much of SQL , but if you intend to work exclusively on large systems , then SQL is a must.

Once you have gone through a couple of books , then the only way to progress in your knowledge is to join online forums , since questions asked on these are from real-life , and deal with practical applications of SQL ; see this link for some of the good forums :

https://www.johnsansom.com/top-5-sql-forums/

Narayan
 

shrivallabha

Excel Ninja
From what I've been told and saw on web searches, "Access 2010: The Missing Manual" by Mathew MacDonald comes up as one of the top recommendation. "For Dummies" series seems popular.
I have a hard copy of the Missing Manual but never got beyond few pages. Spent time on Excel instead.

My opinion on this book or online part is more philosophical. Are you willing to finish it? If the answer is yes then read any one. Over the course of time you will get there where you want to be.

If I remember this thread say after 2 years and ask the status (none of my business really) then what will be the OP's answer? Please do not get me wrong here. I am not discouraging from learning.
 

ThrottleWorks

Excel Ninja
Hi @NARAYANK991 sir, now my confusion is clear.
Access also has VBA as a programming language while other RDBMS have their own programming language in addition to SQL or as extensions of SQL
Thanks for the help. Good night.


Hi @Chihiro sir, thanks a lot for the help.
"Access 2010: The Missing Manual"
Have a nice day ahead. :)

Hi @shrivallabha sir, I know you are not discouraging me. I am sure about this.
I am not discouraging from learning
Good night. :)
As a childhood habit, I am comfortable with hard copy.
 

ThrottleWorks

Excel Ninja
While reading Access book, I realized one important aspect of this platform (at least from my point of view).

In MS access, one needs to learn Overall System Design, Report Design (Output), Basic Data Design, Table Design & User Interface Design.

When I work on MS excel, I need to focus only on Report Design (Output) and a bit for User Interface Design as compared to MS Access.

Not sure what is ‘Overall System Design’ from excel’s POV, Basic Data Design and Table Design is already in place.

I would consider Basic Data Design and Table Design as input files so hardly we can alter anything about this aspect. My guess is, we need to build everything from scratch while working on MS access.

Also, 99% of my work involves trying to automate excel manual process with help of macro.

With access, I am not sure of exact nature of work would be. With excel, we have a report prepared manually and requirement would be to automate it.
 
Last edited:

shrivallabha

Excel Ninja
While reading Access book, I realized one important aspect of this platform (at least from my point of view).

In MS access, one needs to learn Overall System Design, Report Design (Output), Basic Data Design, Table Design & User Interface Design.

When I work on MS excel, I need to focus only on Report Design (Output) and a bit for User Interface Design as compared to MS Access.

Not sure what is ‘Overall System Design’ from excel’s POV, Basic Data Design and Table Design is already in place.

I would consider Basic Data Design and Table Design as input files so hardly we can alter anything about this aspect. My guess is, we need to build everything from scratch while working on MS access.

Also, 99% of my work involves trying to automate excel manual process with help of macro.

With access, I am not sure of exact nature of work would be. With excel, we have a report prepared manually and requirement would be to automate it.
If you really wish to really dig deeper and see what can be done within Excel and how it can be used extensively(not as database of course) then get a copy of "Professional Excel Development". Read its reviews and see if it you want to read it. I have a hard copy of this book.
 

NARAYANK991

Excel Ninja
When I work on MS excel, I need to focus only on Report Design (Output) and a bit for User Interface Design as compared to MS Access.

Not sure what is ‘Overall System Design’ from excel’s POV, Basic Data Design and Table Design is already in place.

I would consider Basic Data Design and Table Design as input files so hardly we can alter anything about this aspect. My guess is, we need to build everything from scratch while working on MS access.

With excel, we have a report prepared manually and requirement would be to automate it.
Hi ,

Excel application design is identical to Access application design is identical to application design using any other platform.

Application design means starting from scratch ; it involves clearly and comprehensively identifying user requirements , which means what kind of data is involved , how it is to be entered into a computer , how it is to be stored , what kind of processing is required , and what kind of outputs are required.

Application design is a standardized process and all good companies will either follow it in-house or demand it from their vendors.

Based on this identification , a document called Functional Specifications is prepared , which will need to be approved by the user / customer.

The next step is to prepare the Design documents , which will detail all the hardware and software measures required to meet the Functional Specifications.

Only after this design document is ready will any coding start !

Needless to say that most companies which use Excel believe application design using Excel does not need all this activity !

See these links for what the professionals think :

http://www.onlinepclearning.com/how-to-develop-bullet-proof-excel-applications-newsletter-no-2/

http://what-when-how.com/excel-vba/creating-excel-applications-for-others/

http://www.ozgrid.com/Excel/ExcelSpreadsheetDesign.htm

http://www.excelhelp.com/excel-spreadsheet-applications-10-attributes-of-the-best/

Narayan
 

ThrottleWorks

Excel Ninja
@NARAYANK991 sir, thanks for the help and links.
Just read first link. It will be very useful for me. To be honest, till date, never had realized few important points mentioned in the article.

Have a nice day ahead. :)
 

ThrottleWorks

Excel Ninja
Just joined tuition for learning MS Access and SQL.
This training will focus on front end of both Access and SQL. Will not cover VBA part.

Since I do not have any knowledge about Access and SQL joined a tuition as well.

Will continue VBA part of Access through online help.
Few things I realized, Macro and VBA are different when it comes to Access.

Cannot write comments for own reference in query editor.
This is very useful in VBA. Have used extensively.

Also, I was fascinated by learning use of SQL queries in excel front end to derive Access data.

Just learnt ‘how to use option’ I am yet to figure out where and why to use this feature.
But this looks good. Though this is just a start for me, I guess, writing simple queries should not be too complex.
 

ThrottleWorks

Excel Ninja
Hi,
I am following below mentioned steps to write a query in MS Access.

Create --> Query design --> Close --> Click on SQL

Our teacher has advised me to ignore query designer table reflected in bottom of MS Access view.
My doubt is, is there any alternate to write query. Is there a way, where I can skip few steps mentioned above. Thanks.

PS - Was playing with the options and hide a query, un-hiding seems to be very tricky. Unlike Excel ! :)
 
Last edited:

Chihiro

Excel Ninja
Nope, that's about the quickest way to SQL view in Access.

Or use keyboard combo.
Alt, then c then qd then esc. You need to click on SQL view still though.
 

ThrottleWorks

Excel Ninja
Hi,

I am writing a query to get unique records. Original table as blank values as well. When I run 'SELECT DISTINCT MyNames FROM my_database;' I get blank value as well. How to skip blank values.

Can anyone please help me in this.
 

Chihiro

Excel Ninja
Code:
SELECT DISTINCT MyNames
FROM my_database
WHERE MyNames is not null And MyNames <> ""
Not sure if it's true blank (null) or blank text. So checked for both.
 
Top