Any golden rules?

[ITMA]

Yes yes we've all heard of normalisation, but they count for little when
your enthusiasm and need for a working database ends in tears over
gobbledegook like "invalid theta join" or "join key of table is not in
recordset". Can anyone offer any practical tips to actually get a working
Access database instead of just a load of frustration and angst?

 

[Joseph Meehan]

Yes yes we've all heard of normalisation, but they count for little
when your enthusiasm and need for a working database ends in tears
over gobbledegook like "invalid theta join" or "join key of table is
not in recordset". Can anyone offer any practical tips to actually
get a working Access database instead of just a load of frustration
and angst?

Most of us here fully understand what you are feeling. We have all been
there. Access has a very steep learning curve. Many of us have found the
not following the rules like normalization just makes that curve all the
more steep.

Look around for the thread on the Access Commandments. It is all very
true.

About the only hint I can suggest is take some close looks at Northwind
and use the wizards until you learn why not to.

 

[Dirk Goldgar]

Most of us here fully understand what you are feeling. We have
all been there. Access has a very steep learning curve. Many of us
have found the not following the rules like normalization just makes
that curve all the more steep.

Look around for the thread on the Access Commandments. It is all
very true.

About the only hint I can suggest is take some close looks at
Northwind and use the wizards until you learn why not to.

One caution I'd add is that, while the Northwind sample database can
show you some good ways to do things, it also does some things that IMO
are bad design and bad coding. Do not take Northwind as gospel.

And speaking of gospel, the "Ten Commandments of Access" can be found at
http://www.mvps.org/access/tencommandments.htm . Incidentally, just to
show what a sinner I am, I'd describe some (not all) of those as "really
good suggestions", which you can break *if* you know what you're doing.

 

[Ken Snell [MVP]]

And speaking of gospel, the "Ten Commandments of Access" can be found at
http://www.mvps.org/access/tencommandments.htm . Incidentally, just to
show what a sinner I am, I'd describe some (not all) of those as "really
good suggestions", which you can break *if* you know what you're doing.

Aarrgh.... I think I broke them before I knew the meaning of the word
"if"....!

 

[Albert D. Kallal]

You two things

Theory
and
How to use the program!

Can anyone offer any practical tips to actually get a working

Access database instead of just a load of frustration and angst?

You need to get a book. Grab anything that gives you good starting. I like
the "sams" in 21 days type books..but at least try a few books, as learning
this stuff by trial and error is just not going to cut it. Once you find
your way around ms-access, then you will be willing to go back, and realize
why some of that theory stuff is useful!

 

[(Pete Cresswell)]

Per ITMA:

Can anyone offer any practical tips to actually get a working
Access database instead of just a load of frustration and angst?

Naming conventions.

 

[ITMA]

The trouble with books is that they steer you through examples that work
just fine but when you set off on your own, with a real world database that
bears no similarity in purpose to the book examples, you hit problems and
the book has not given you any grounding in the fundamentals to equip you to
tackle your current dilemma intuitively. I am however plodding through the
SAMS Teach Yourself Access 2000 in 21 days at the moment actually.

What I think I'd really like to know is the logic that lays behind Queries
so that when I merrily click and drag away designing queries I can work out
why my particular combination of fields and tables doesn't do what I'm
expecting. I've yet to come across any book that teaches you that. Its
all, "Do this, then that, and hey presto, our own example works!", but what
about the infinite real world applications for each individual reader, who's
work doesn't work! The books leave you up a creak without a paddle.

The other problem with books is they tend focus on queries as a means to
pull together data from related tables, when that is normally child's play -
what I struggle with is the reverse process - how data input on a single
form can be made to magically disseminate itself out to the appropriate
tables, ready in the days and weeks ahead to be retrieved again. I for
instance typically end up with 'cannot update recordset' type messages or
else it appears to work fine but I get multiple insertions of a record in a
table on what is supposed to be the 'one' side of a relationship every time
an entry in the 'many' side is created, thereby defeating the whole purpose.

I did once see a small book called something like "understanding access
error messages" but sadly I didn't buy it and I've not seen it since.

 

[ITMA]

I am however plodding through the SAMS Teach Yourself Access 2000 in 21

days at the moment actually.

Talking of which, I've a second hand copy without any CD - does anyone know
where to download the sample files? They're not advertised on SAMS' website

 

[Joseph Meehan]

The trouble with books is that they steer you through examples that
work just fine but when you set off on your own, with a real world
database that bears no similarity in purpose to the book examples,
you hit problems and the book has not given you any grounding in the
fundamentals to equip you to tackle your current dilemma intuitively.
I am however plodding through the SAMS Teach Yourself Access 2000 in
21 days at the moment actually.
What I think I'd really like to know is the logic that lays behind
Queries so that when I merrily click and drag away designing queries
I can work out why my particular combination of fields and tables
doesn't do what I'm expecting. I've yet to come across any book that
teaches you that. Its all, "Do this, then that, and hey presto, our
own example works!", but what about the infinite real world
applications for each individual reader, who's work doesn't work! The
books leave you up a creak without a paddle.
The other problem with books is they tend focus on queries as a means
to pull together data from related tables, when that is normally
child's play - what I struggle with is the reverse process - how data
input on a single form can be made to magically disseminate itself
out to the appropriate tables, ready in the days and weeks ahead to
be retrieved again.

Normally basing the form on that same query will take care if it, unless
you have a relationship problem.

 

[Mike Painter]

The trouble with books is that they steer you through examples that
work just fine but when you set off on your own, with a real world
database that bears no similarity in purpose to the book examples,
you hit problems and the book has not given you any grounding in the
fundamentals to equip you to tackle your current dilemma intuitively.
I am however plodding through the SAMS Teach Yourself Access 2000 in
21 days at the moment actually.
What I think I'd really like to know is the logic that lays behind
Queries

That's why he said THEORY. There is a lot of information in help and any
number of references on relational databases on line

The other problem with books is they tend focus on queries as a means
to pull together data from related tables, when that is normally
child's play - what I struggle with is the reverse process - how data
input on a single form can be made to magically disseminate itself
out to the appropriate tables, ready in the days and weeks ahead to
be retrieved again.

You base the form on the query and without magic the information goes into
the correct tables.


I for instance typically end up with 'cannot

update recordset' type messages or else it appears to work fine but I
get multiple insertions of a record in a table on what is supposed to
be the 'one' side of a relationship every time an entry in the 'many'
side is created, thereby defeating the whole purpose.

Theory explains both situations.
You have joined two tables and are trying to fill in the the wrong field of
the two that make the join in the first case. If you look at the table on
the one side and it actually has multiple entries there is a problem.
Chances are you are looking at a query which shows the entry from the one
side for each entry on the many side.