Question

Locked

Help - MS Excel & Access before MS SQL Server?

By loadsoffood ·
I work on MS excel quite a lot but only at the very basic level. Now I am planning to get a bit more into IT towards data analysis side and according to couple of people i have spoken to SQL server is a good option. I am planning on polishing my Excel skills to intermediate/higher levels and get on with MS Access before jumping into SQL Server.

Any ideas if I am thinking and starting in the right direction or excel/access wouldn't make much difference?

I don't have IT background.

This conversation is currently closed to new comments.

3 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Answers

Collapse -

Depends....

by Tony Hopkinson In reply to Help - MS Excel & Access ...

Where you are aiming to be. Access will do the job in terms of the basics, but it's approachability is in part because it skips right over fundamental database concepts.
The best way to learn databases is raw sql.
So if you want to pick up the what and simple how quickly and cheaply, access is the way to go.
For why SQL Server Express and go to the new query page, and stay away from the graphical tools
WWW.W3C do some good sql tutorials by the way, it's a deceptively simple language. The power comes from combining simple functionality with the design of the database. The closer it matches your needs the simpler the sql you need.
Getting that particular trick right for non trivial scenarios is a steep learning curve though.

To illustrate
In access if you wanted an new table
You select new table, get a enter column names, pick properties from drop downs and you are off.
But what should the names be, what properties should you pick and what do they mean?

To write the raw SQL to do the same thing those things will be right in your face from the get go.

It looks a lot scarier than it is though.

Eventual aim is the main thing, a lot of people who start with access, really struggle when they try to move off it, they've had their hand held that much by it they can't go anywhere else by themselves....

Collapse -

hmmm

by loadsoffood In reply to Depends....

Wow, thanks for that. I think i'll just skip access and hit straight to basic sql, someone else suggested the same. However, i'll start with polishing my ms excel to intermediate or high level since lots of data is imported from it.

thanks

Collapse -

Excellent, may fortune favour the brave

by Tony Hopkinson In reply to hmmm

Fair few people here are decent at Sql, and prepared to answer a question or three, myself included. Don't ask us how to design a full inventory management system like one pratt though.
But explaining the 'rules' and helping with the bits that don't seem to be very english, we can do.

There are graphical tools in SqlServer (SQL Server Manager), and they'll get you going with the plumbing like creating a database and stuff.

A couple of newbie gotchas.

You can create table and field names with 'illegal' names.
So in the graphical tools you can create a Field called order or order number.
When you come to write the sql though, you have to wrap them

so you have
Select * From MyOrders Where OrderDate > '2000-12-21'

or

Select * From [My Orders] Where [Order Date] > '2000-12-21'

Order is bad choice because it's an Sql keyword as in

select * From Customers Order By CustomerName

if you want a pretty output to go into a report of something

Select SomeStrangeFieldName As [Number Of Orders in December] From [MyTable]

Tip two always put a primary key on your table, if there's no obvious one e.g. RowNumber or some such

You can do what's know as an auto number (Identity in SQL server)

Create MyTable(
RowNo int Not Null Identity (1,1),
RowText Text null,
Constraint PK_MyTable Primary Key (RowNo)
)
for instance

if you then insert some records with

Insert MyTable(RowText) Values("Hello")
Insert MyTable(RowText) Values("LoadsOfFood")

Because it's an identity you don't need to know what row number is it will be set automatically for you, They'll start at 1 and be increased by one each insert so you'd see

RowNo RowText
1 Hello
2 LoadsOfFood

With Select * From MyTable

* is just all the columns in the order the are in the create statement.

Select RowNo,RowText From MyTable
or
Select RowText,RowNo From MyTable

Or just Select RowText as [Some Nice Name] from MyTable

Another gotcha is nullability

In the above create I said RowNo couldn't be null (can't be on a key field by the way)

Null means optional basically so if you had a table of contacts where you had Name, Email, Phone and Fax
Name is a must, but the others could be not entered ie null.

When dealing with nulls

If you wanted all your contacts without an Email

Select * From Contacts Where Email is not null

Or all with

Select * From Contacts Where Email is null

you can't do
Where Email = Null
or Where Email <> Null (does not equal)

Well you can but they are never true, so you'll get no records returned no matter what.

Careful what you choose as a key, Name is not a good one in Contacts
Two chaps called Smith and you are stuffed...

That particular table is a good candidate for an identity column ContactNo say.

Then you can have as many Smiths as you like.

Another gotcha when I did the example with order Date
I had > '2010-10-21'
YYYY-MM-DD is the default works anywhere date format, don't rely on Locale (regional settings date format)

Some one twiddles with the system and
you don't know whether
9/8/2010 is the 9th of August or the 8th of September...

There's loads more to it, but it can be taken in, in gradual steps, if you've a mind to.

Back to Networks Forum
3 total posts (Page 1 of 1)  

Related Discussions

Related Forums