-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Which version of Office should I buy.
- Thread starter andrew69
- Start date
Excel Facts
Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Okay, I have been watching this thread for a few days now and it's now somewhere that I have a definitive opinion... (Remember, this is just my opinion. I'm sure there's going to be someone unhappy with it.)
First, let me admit that I have not used Access extensively in years. Mostly by choice. So my observations my be outdated. I do run an occasional query in the 2010 version. And I have even written a macro in it to eliminate the tedium of one of my current processes. That's because the only database I have is Access. I wish it weren't.
IMHO, MS Access leaves a lot to be desired. I won't go into the fact that it's horrifically slow, or that you have to build another DB every 2 gigs of data. The most irritating thing about it to me is that there is nothing remotely intuitive about it. Query design is anything but understandable without some research. The reporting side is okay, but can be confusing for someone without experience.
A little background: In my last job I (along with another person) built a complete database and Web reporting system for a business that had 19K sales locations across the State. That database updates itself and the Web data every morning by 7 AM, after the delivery of 19 separate source files. That identical system was then converted for slightly different data and rolled over into 4 other States for their use. It has now been 5 years since I worked with it, but it is still running. I occasionally talk with the one person left who maintains it and he tells me it is mostly self-sufficient and he does little more than check on it and build new reports for the web. Currently, it sits at about 70 GB of data and grows a little every day. And users have access to all of the historical data as well as the current stuff via the web interface.
It was so well built, I got to go look for another job.
And now the part that will probably irritate: I did it all with Corel Paradox, which I feel is the fastest, most user friendly, easy to build and code database system out there. Unfortunately, Corel is not really doing anything with it since they bought it from Borland and the rumors for years say it may be coming to an end. If I thought there were a solid future for it, I would recommend it over Access any day.
First, let me admit that I have not used Access extensively in years. Mostly by choice. So my observations my be outdated. I do run an occasional query in the 2010 version. And I have even written a macro in it to eliminate the tedium of one of my current processes. That's because the only database I have is Access. I wish it weren't.
IMHO, MS Access leaves a lot to be desired. I won't go into the fact that it's horrifically slow, or that you have to build another DB every 2 gigs of data. The most irritating thing about it to me is that there is nothing remotely intuitive about it. Query design is anything but understandable without some research. The reporting side is okay, but can be confusing for someone without experience.
A little background: In my last job I (along with another person) built a complete database and Web reporting system for a business that had 19K sales locations across the State. That database updates itself and the Web data every morning by 7 AM, after the delivery of 19 separate source files. That identical system was then converted for slightly different data and rolled over into 4 other States for their use. It has now been 5 years since I worked with it, but it is still running. I occasionally talk with the one person left who maintains it and he tells me it is mostly self-sufficient and he does little more than check on it and build new reports for the web. Currently, it sits at about 70 GB of data and grows a little every day. And users have access to all of the historical data as well as the current stuff via the web interface.
It was so well built, I got to go look for another job.
And now the part that will probably irritate: I did it all with Corel Paradox, which I feel is the fastest, most user friendly, easy to build and code database system out there. Unfortunately, Corel is not really doing anything with it since they bought it from Borland and the rumors for years say it may be coming to an end. If I thought there were a solid future for it, I would recommend it over Access any day.
QBE is fine for simple queries, not so good for anything moderately complex.
FWLIW, I started off with dBase then moved to Paradox back in the late 1980s. Paradox and QBE were wonderful after dBase, but then I moved on to R:Base in the early 1990s and learned SQL. QBE now seems crude.
Anyone who can understand Excel formulas with more than 2 IF calls should be able to learn SQL reasonably well. It's only HAVING clauses which take time & effort.
FWLIW, I started off with dBase then moved to Paradox back in the late 1980s. Paradox and QBE were wonderful after dBase, but then I moved on to R:Base in the early 1990s and learned SQL. QBE now seems crude.
Anyone who can understand Excel formulas with more than 2 IF calls should be able to learn SQL reasonably well. It's only HAVING clauses which take time & effort.
xenou
MrExcel MVP
- Joined
- Mar 2, 2007
- Messages
- 16,836
- Office Version
- 2019
- Platform
- Windows
Having "learned" SQL I now typically use the QBE to create my queries but often view them in SQL view since I find them easier to read that way. There are some situations where complex queries are better written out, but I can't really see any reason to avoid the QBE grid when it's faster and produces the same result. Though FWIW, I did write out my SQL while I was learning it, and still do so in various situations.
One case where I would *always* use the QBE in Access - I have a table with 15 fields in it and I want to select 14 fields -- all but the arbitrary autonumber primary key. I open the QBE, add the table, double click in the field list, drag, and delete the first ID field. My query is written in 10 seconds instead of 2 minutes. If I want it as text I can still copy it from SQL view.
One case where I would *always* use the QBE in Access - I have a table with 15 fields in it and I want to select 14 fields -- all but the arbitrary autonumber primary key. I open the QBE, add the table, double click in the field list, drag, and delete the first ID field. My query is written in 10 seconds instead of 2 minutes. If I want it as text I can still copy it from SQL view.
SydneyGeek
MrExcel MVP
- Joined
- Aug 5, 2003
- Messages
- 12,251
shg, you're good at maths. Relational databases (and therefore SQL) are based on set theory. The syntax isn't that tough.
Maybe I'm biased... I like databases. But I use Excel for crunching a lot of that data, either for analysis or reports. And I like being able to use SQL to pull data into Excel.
As xenou said, Access 2010 has/had some stability issues. There are so many changes under the hood, I guess the bugs weren't all caught before release. Be interesting to see how Access runs in the next version.
Denis
Maybe I'm biased... I like databases. But I use Excel for crunching a lot of that data, either for analysis or reports. And I like being able to use SQL to pull data into Excel.
As xenou said, Access 2010 has/had some stability issues. There are so many changes under the hood, I guess the bugs weren't all caught before release. Be interesting to see how Access runs in the next version.
Denis
Seeing the topic has moved onto SQL, I'd encourage a little learning.
SQL is simple & super-powerful. As easy as one IF in Excel.
Simple example,
I'm a huge fan of SQL.
SQL is simple & super-powerful. As easy as one IF in Excel.
Simple example,
Code:
SELECT a_field, another_field, SUM(this_field) AS [Total]
FROM table
WHERE a_field Is Not Null and another_field > 10
GROUP BY a_field, another_field
HAVING SUM(this_field)>1000000I'm a huge fan of SQL.
