![](/i/fill.gif) |
![](/i/fill.gif) |
|
![](/i/fill.gif) |
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
> Really? I had literally no idea that was possible. In 15 years of using
> Excel, I didn't know about this. (I wonder how many other people didn't.)
Odd, I've seen this used on pretty much every "database" style Excel
workbook from several different companies. Particularly for "action
list" type databases where it's essential to be able to easily filter
out certain rows.
> I meant stuff like if you have one spreadsheet with all the license
> keys, and another spreadsheet that tells you when each product was
> purchased, you're going to have fun trying to link one to the other.
> With a real database this would be a trivial table join.
Yes, it's quite tricky to get two tables linked in the same way a proper
DB would do it whilst keeping the GUI friendly, but usually there is an
easy enough hack using the lookup and data validation stuff that works.
Post a reply to this message
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
On 22/02/2011 02:51 PM, scott wrote:
> Yes, it's quite tricky to get two tables linked in the same way a proper
> DB would do it whilst keeping the GUI friendly, but usually there is an
> easy enough hack using the lookup and data validation stuff that works.
I guess fundamentally it just irks me that supposed IT "experts" are
using the wrong tool for the job. I don't suppose an Access database
would actually be any faster to access over an encrypted WAN link,
that's for sure...
Post a reply to this message
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
scott wrote:
> Excel is good for simple databases with infrequent updates for many
> reasons, the most important is that almost everyone has it installed on
> their machine and most people are familiar with it.
I read a while ago that while LOTOS 1-2-3 and visicalc were putting in
ever-more sophisticated math stuff, MS went and looked at how people were
actually using the product and realized some surprisingly large number of
spreadsheets had no formulas at all. So MS made it easier to keep lists of
stuff in Excel, and that's part of why it won out over those others.
> that only one person can edit it at a time.
Actually, I think nowadays you can set it up for multiple edits and it'll
coordinate. It seemed simple, but I never had two people actually try at
once, so take it with a grain.
>> How hard would it be to put this
>> stuff into a trivial Access database? Not very.
>
> If you have no experience with Access it's not trivial to set it up
> properly, and if the only perceived benefit is that more than person can
> edit it at a time it's unlikely to be seen as worthwhile.
Last I looked, Access was only one user at a time making changes too. The
one guy would lean back in his chair and bellow "IS THERE ANYONE IN THE
DATABASE?" That was their locking protocol.
--
Darren New, San Diego CA, USA (PST)
"How did he die?" "He got shot in the hand."
"That was fatal?"
"He was holding a live grenade at the time."
Post a reply to this message
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
On 22/02/2011 04:49 PM, Darren New wrote:
> I read a while ago that while LOTOS 1-2-3 and visicalc were putting in
> ever-more sophisticated math stuff, MS went and looked at how people
> were actually using the product and realized some surprisingly large
> number of spreadsheets had no formulas at all. So MS made it easier to
> keep lists of stuff in Excel
That wouldn't surprise me.
> and that's part of why it won out over those others.
Perhaps. I can well imagine people being too stupid to work out how to
use a proper database product...
>> that only one person can edit it at a time.
>
> Actually, I think nowadays you can set it up for multiple edits and
> it'll coordinate.
I've never seen that.
> Last I looked, Access was only one user at a time making changes too.
> The one guy would lean back in his chair and bellow "IS THERE ANYONE IN
> THE DATABASE?" That was their locking protocol.
I spent six months watching a group of three people in an office. They
had an Access database, which everybody had constantly open all day. So
clearly that's not the case.
On the other hand, I have seen "error - database in use", so I'm not
sure what's up with that...
Post a reply to this message
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
Invisible wrote:
> Perhaps. I can well imagine people being too stupid to work out how to
> use a proper database product...
Remember at the time that a "proper" database product cost roughly $100K per
seat.
You don't need a "proper" database product to keep your to-do list or your
org chart.
>>> that only one person can edit it at a time.
>>
>> Actually, I think nowadays you can set it up for multiple edits and
>> it'll coordinate.
>
> I've never seen that.
It was really trivial in the menus. I think it's something around
tools->Shared Workbook. One of the things under Tools should do it.
> I spent six months watching a group of three people in an office. They
> had an Access database, which everybody had constantly open all day. So
> clearly that's not the case.
Well, this *was* years ago. More than a decade, probably.
--
Darren New, San Diego CA, USA (PST)
"How did he die?" "He got shot in the hand."
"That was fatal?"
"He was holding a live grenade at the time."
Post a reply to this message
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
>> Actually, I think nowadays you can set it up for multiple edits and
>> it'll coordinate.
>
> I've never seen that.
It's under the Review ribbon, then "Share Workbook". But it only lets
you change basic stuff when shared (ie just cell contents, not the
layout, charts, etc), and the changes are only updated when you save the
file (or I think you can set it to automatically update at regular
intervals). If two people change the same cell, the first one to save
will succeed, the second one will get to choose which value to keep.
>> Last I looked, Access was only one user at a time making changes too.
>> The one guy would lean back in his chair and bellow "IS THERE ANYONE IN
>> THE DATABASE?" That was their locking protocol.
>
> I spent six months watching a group of three people in an office. They
> had an Access database, which everybody had constantly open all day. So
> clearly that's not the case.
I only worked on Access about 8 years ago so it's probably out of date
info now, but IIRC you could set up on a per-table basis how much of it
was locked when opened (entire table, single row, nothing). It also
took a fair bit of work to get it to play nicely with multiple people
opening it (ie catching all the errors that might come up, and
preventing people doing stuff that would screw it up).
Post a reply to this message
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
On 23/02/2011 08:44 AM, scott wrote:
>>> Actually, I think nowadays you can set it up for multiple edits and
>>> it'll coordinate.
>>
>> I've never seen that.
>
> It's under the Review ribbon, then "Share Workbook".
Ribbon?
As in, Office 2007 or later?
Well, that'll be why I haven't seen it then. ;-)
> But it only lets
> you change basic stuff when shared (ie just cell contents, not the
> layout, charts, etc), and the changes are only updated when you save the
> file (or I think you can set it to automatically update at regular
> intervals). If two people change the same cell, the first one to save
> will succeed, the second one will get to choose which value to keep.
I bet that's fun if you try to add a new row to the bottom. (This would
of course be a complete non-issue if you used... a database.)
>> I spent six months watching a group of three people in an office. They
>> had an Access database, which everybody had constantly open all day. So
>> clearly that's not the case.
>
> I only worked on Access about 8 years ago so it's probably out of date
> info now, but IIRC you could set up on a per-table basis how much of it
> was locked when opened (entire table, single row, nothing). It also took
> a fair bit of work to get it to play nicely with multiple people opening
> it (ie catching all the errors that might come up, and preventing people
> doing stuff that would screw it up).
It's not something I have a lot of expertise with, but I saw all this
set up 10 years ago, running under Windows 98. It can't be that hard.
Post a reply to this message
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
On 22/02/2011 05:11 PM, Darren New wrote:
> You don't need a "proper" database product to keep your to-do list or
> your org chart.
Not really, no. Access may be hopeless as a "real" database, but it's
pretty much ideal for quickly setting up small databases for
non-critical stuff.
Now, if only it wasn't so absurdly difficult to secure it...
Post a reply to this message
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
> I bet that's fun if you try to add a new row to the bottom. (This would
> of course be a complete non-issue if you used... a database.)
Actually it works fine. If two users both add a row to the bottom in
the same place it does what you would expect (ie add the 2nd row below
the 1st new row, and not ask if you want to overwrite it or not).
> It's not something I have a lot of expertise with, but I saw all this
> set up 10 years ago, running under Windows 98. It can't be that hard.
The problem with Access is that it's not included in the cheap Office
packs. Everyone here needs to ask IT to get a new license if they need
Access (which of course costs more). That is a big reason why people
use Excel for this kind of stuff.
Post a reply to this message
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
On 23/02/2011 10:24 AM, scott wrote:
>> I bet that's fun if you try to add a new row to the bottom. (This would
>> of course be a complete non-issue if you used... a database.)
>
> Actually it works fine.
MS really have worked hard on this, eh? ;-)
> The problem with Access is that it's not included in the cheap Office
> packs. Everyone here needs to ask IT to get a new license if they need
> Access (which of course costs more). That is a big reason why people use
> Excel for this kind of stuff.
Oh. Really?
I can't say about other sites, but here in the UK, every employee has
Word, Excel, Access, Power Point and Outlook installed by default. We
have volume licenses for all of them.
Which is just as well, since several vital items of operational data
*are* Access databases. (All things developed in the UK, not by the
central IT department. I'm sure they would have used Excel instead...)
Post a reply to this message
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |