The power of Access Design View?

[MSherfey]

I've been learning that staying within the Design View in Access will only get you so far. I was having a problem with unique values in a crosstab query and received this great code from ByteMyzer (http://www.access-programmers.co.uk/forums/showthread.php?t=168901). When I looked at it in Design View, I can't think of any way this could have been made.

Am I right to think that the Design View is a basic tool and to really get the power out of Access you need to work in the SQL View?

If so, should I buy a book on Access or SQL?

Thanks for any tips!

 

[Banana]

Well, I'd put this way:

Lazy developers use design view only.
Masochistic developers use SQL view only.
Smart developers use both views.


Here's an example:

Suppose I had three tables and I wanted UNION query, and I only want certain columns that happens to be grouped together in all three tables. I can type it everything thrice over in the SQL view or I can just drag'n'drop in design view, then switch over into SQL view and copy & paste the SQL there and editing just the table name and column name where necessary and adding UNION and I'm done.

Another common case is when I want to do several subqueries (and I don't like cascading queries- nothing wrong with them, just that it clutters up my object model pretty quickly), I use design view to build a query upon one table, go over to SQL, copy and start on the next level of query, paste the subquery's SQL in there.


Union query and passthrough queries are two queries where design view can't be of help, but for most parts, what you can do in SQL view, you can do in design view as well.

As for the reference, I can't think of a specific one for SQL aspects, though I've quite liked Alison Baltier's coverage on queries.

Best of luck.

 

[stopher]

My experience...

I studied SQL (I'm not very good at it!) when I studied relational databases before I got to Access. I have an SQL book on my shelf... but I've never opened it. I have several Access books and used them often.

The point is, SQL is a limited language. There are some tricks/techniques but these are finite. If you want to be an SQL expert then you need to do this by practice and experience.

The design view in Access will cover 80%+ of your requirements. For the rest I reckon you can google. There are plenty of online tutorials and problem solutions.

At least that's my experience.

Chris

 

[Banana]

Stopher, if you don't mind me asking but what makes you say "SQL is limited language"?

 

[stopher]

Stopher, if you don't mind me asking but what makes you say "SQL is limited language"?

Poor choice of words. I read it now and it looks like I'm saying that SQL is limited which of course it isn't!. What I meant to imply was that the core language is not that big (some 30 key words for Jet) i.e. learning the language vocabulary is quite easy. Learning how to use the language is rather more challenging.

Chris

 

[Banana]

Thanks for clarification. Yes that is definitely true, and I think much of this has to do with realizing that SQL has a different approach to it than VBA. VBA (and several programming languages out there) deals with statements that could involve iteration or branching, but SQL deals in sets, so it does require a different way of thinking about how we want to solve the problem using SQL.