Understanding what makes Access tick

ITMA · Oct 25, 2004

I've been trying to create useable Access applications for about the last
four years and am getting really fed up always running into messy deadends
about a day after trying to create a new .mdb file! All the text books
follow the same, "If you do this, this, this and that, then .... ta da!
Look what happens!", without any explanation of the fundamentals from whihc
you can learn to create your own applications from scratch. Has anyone any
tips of websites or books that for instance:

Show the logic behind how Access processes Queries, and how they sometimes
automatically 'pull-in' the related field on a table not included in the
Query, but which is the subject of a table's lookup relationship. How
Access uses Queries for instance to identify information that is in one
table but not in another (a kind of negative-addition, verging on black
magic) is a great mystery to me.

Why it is so easy to make a query that filters / collates data from many
tables, but going the other way, creating forms based on more than one
table, that update all those necessary related tables behind the scenes,
normally just results in the frustrating "Cannot update recordset" error
message.

Why do some but not all wizard-created table lookups not create a
relationship in the Show All relationships window, and what else is Access
hiding from us?

Why does the Group By option in Queries so annoyingly not have a simple
"Value" option in addition to Sum, Avg, Max and so on to make it useful?!
For instance, I've a table that shows what you might call "Current Tasks"
and a related table that shows the history of progress for those tasks. The
history progress table has fields of HistoryID, TaskID, Current Status,
DateRecordEntered. Common sense says that if I want a form or query that
extracts the latest Status of multiple tasks I need a Select Query that
Groups the TaskID, selects the maximum of the DateRecordEntered (so as to
get the latest entry for that TaskID, and I want the VALUE of the
CurrentStatus text field, but no! Access doesnt give that option!!
Argh!!!!

John Vinson · Oct 26, 2004

Answers inline.

I've been trying to create useable Access applications for about the last
four years and am getting really fed up always running into messy deadends
about a day after trying to create a new .mdb file! All the text books
follow the same, "If you do this, this, this and that, then .... ta da!
Look what happens!", without any explanation of the fundamentals from whihc
you can learn to create your own applications from scratch. Has anyone any
tips of websites or books that for instance:

"Access 2003 Inside Out" by my friend and colleague John Viescas is an
excellent reference of this type.

Show the logic behind how Access processes Queries, and how they sometimes
automatically 'pull-in' the related field on a table not included in the
Query, but which is the subject of a table's lookup relationship.

Lookup Fields are decried by most serious developers (see
http://www.mvps.org/access/lookupfields.htm for a critique). They are
both too intrusive and too ineffective; and they're not necessary. If
you instead use the Relationships Window to define your joins, Access
will create the needed foreign-key indexes; when you create a Query
using the two tables, Access will automatically join them
appropriately, and you can change the join fields or join type if need
be.

How Access uses Queries for instance to identify information that is in one
table but not in another (a kind of negative-addition, verging on black
magic) is a great mystery to me.

This is called a "Frustrated Outer Join" query, and it's kind of neat.
You don't need to use the wizard to create one; let's walk through an
example. If you want to find all records in tblA which do NOT have any
matching records in tblB you can take the following steps:

- Create a Query joining tblA to tblB. This will use the default
"Inner Join", showing only those records which DO have a match.
- Select the Join line in query design and choose option 2 (or 3) -
"Show all records in tblA and matching records in tblB". Those records
which don't have a match will have NULL values in all tblB fields.
- Select the joining field (the Foreign Key) from tblB, and put a
criterion on it of

IS NULL

This will limit the retrieval to just those records which have a NULL
value in the (required) joining field - that is, those records in tblA
for which there is no corresponding record in tblB.

Why it is so easy to make a query that filters / collates data from many
tables, but going the other way, creating forms based on more than one
table, that update all those necessary related tables behind the scenes,
normally just results in the frustrating "Cannot update recordset" error
message.

Attempting to create a Grand Master Query that does everything is
neither necessary nor a good way to go. Instead, use the power of
Forms; if you have a one to many relationship, consider using a Form
for the "one" side table, and a Subform for the "many". Access will
use the joining fields defined in the relationships window as the
Master/Child Link Fields and will keep the tables in synch, and you
won't have updatability problems or repeating field values as you will
in a Query.

Why do some but not all wizard-created table lookups not create a
relationship in the Show All relationships window, and what else is Access
hiding from us?

It's hiding the extra, redundant indexes which will slow update
performance and contribute to database bloat; the relationships
probably exist but just aren't shown, sometimes even if you select the
Show All option in the relationship window.

Why does the Group By option in Queries so annoyingly not have a simple
"Value" option in addition to Sum, Avg, Max and so on to make it useful?!
For instance, I've a table that shows what you might call "Current Tasks"
and a related table that shows the history of progress for those tasks. The
history progress table has fields of HistoryID, TaskID, Current Status,
DateRecordEntered. Common sense says that if I want a form or query that
extracts the latest Status of multiple tasks I need a Select Query that
Groups the TaskID, selects the maximum of the DateRecordEntered (so as to
get the latest entry for that TaskID, and I want the VALUE of the
CurrentStatus text field, but no! Access doesnt give that option!!
Argh!!!!

Ok... hypothetical question. Suppose you add the DateRecordEntered
field twice; on one of them you select Max to get the most recent
date, and on the other you select Min to get the earliest date. What
is the appropriate Value for CurrentStatus? How can Access tell which
record you want?

The correct way to do this is to use a "Subquery" - put a criterion

=(SELECT Max([DateRecordEntered]) FROM [Current Tasks] AS X WHERE
X.TaskID = [Current Tasks].TaskID)

on the DateRecordEntered field.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

Albert D. Kallal · Oct 26, 2004

I will say right away that your questions are actually quite good!

And, I will even more so say that there are some good answers. to each of
these questions.

John has given you some real good stuff..

Show the logic behind how Access processes Queries, and how they sometimes
automatically 'pull-in' the related field on a table not included in the
Query, but which is the subject of a table's lookup relationship.

As mentioned, the built in table lookup feature is a source of much
confusing. When you just start out using ms-access, that lookup feature at
the table level seems quite cool. After a while..it just gets in your way.
Hence, lets assume you need to grab data from a whole bunch of tables, then
it is best to use the query builder. For example, you might have a form that
has many combo boxes that display some "text" value, but in fact store the
id of the other table....this approach in ms-access is a fine and common
good practice (but, this is different then the confusing lookup feature at
the table level...simply don't use them!!).

Now, when it comes time to make a report, how do you grab, and get all those
"text" values into a nice query for all those combo boxes that make the
report easy to write?

The solution is very simple. All you do is drop in the "main" table into the
query builder. Now, if there is 4, or 5 lookup values, then just drop in
those 4 or 5 other tables. You now have to draw a join line FROM the main
table to the child tables. MOST IMPORTANTLY, you then must double click on
the join line, and select a left join. If you don't do this..then you will
only be allowed to drop in ONE table. By LEFT join we simply mean that we
allow any "main" record..and "maybe" a child record. Once all the join lines
a drawn, then you simply drop in any old field you need into the query
grid. And, you also can now drop in those "text" fields you need from the
other tables. And, if you wish, you can also include the "id" values that
the combo boxes stored..but we don't care much at this point..do we? What
is real nice is that now our report can display, and even group on these
"text" values. This brings complete control back to YOU..and no surprises
will
occur when using grouping in a report. You group by on "text" values..and
they also sort correctly here.

How Access uses Queries for instance to identify information that is in
one table but not in another (a kind of negative-addition, verging on
black magic) is a great mystery to me.

John gave you a nice answer...

Why it is so easy to make a query that filters / collates data from many
tables, but going the other way, creating forms based on more than one
table, that update all those necessary related tables behind the scenes,
normally just results in the frustrating "Cannot update recordset" error
message.

Actually, as mentioned, for "one" to many relationships, you use sub-forms
anyway. However, you CAN edit relational data in a single form. You have to
use LEFT joins (as mentioned above), and you usually have to expose the
primary key in the main table, and also should include both the foreign key
field (the field used to link to the main table), and also include the
autonumber key from this child table also. If you do this..then the
table in most cases will be updatable.

Why do some but not all wizard-created table lookups not create a
relationship in the Show All relationships window, and what else is Access
hiding from us?

Well, from day one, I avoided those lookup tables..as they are painful.
Just use the query builder as I noted above..and life will be nice!

extracts the latest Status of multiple tasks I need a Select Query that
Groups the TaskID, selects the maximum of the DateRecordEntered (so as to
get the latest entry for that TaskID, and I want the VALUE of the
CurrentStatus text field, but no! Access doesnt give that option!!

Actually, you can use sql to do this. I assume the first table is a list of
tasknumbers...and then you have your above related table of tasks assigned..
So, I am assuming that you have a simple query that lists all task numbers.
Perhaps this is a parent table. However, if it is in fact ONE table, then
simply build a query that groups by taskNumber, and is distinct. What we
want here is a simply list of tasknumbers.

select TaskNumbers from OurListOfTaskNums

Ok, so, we got a list of tasknubers here...(no duplicates..right...??).

Now, lets assume you have your table of all that task information you just
talked about.

We go:

select Tasknumbers,
(select top 1 CurrentStatus from tblTasks where TaskNumber =
OurListOfTaskNums.TaskNumber
order by desc DateReocrdEntered) as lastStatus.
From ourListOfTaskNumber

.. You mentioned that it is easy to get
the last date, but you actually what another column from that table based on
the last date!..(right??). The above shows a nice way of doing this. Also,
if there is the possibility of two dates on the same day, then simply add to
the order by clause the ID of the child table, thus we get:

select Tasknumbers,
(select top 1 CurrentStatus from tblTasks where TaskNumber =
OurListOfTaskNums.TaskNumber
order by desc DateReocrdEntered,ID) as lastStatus.
From ourListOfTaskNumber

So, with the above, you can easily pluck out any column based on the last
date..and that column does not have to be the lastdate column!

This is not really a ms-access issue as it is learning sql.

I hope the above helps you. I can only say that when you got a problem, ask
here..as many will help you! I can really say that I always believe there is
a
reasonable solution to the common problems that people like you and I
encounter on a regular basis. After all..you are not the only one
encountering these types of obstacles!

I can also say with ease that your questions
were actually very good ones.

Brett Collings [429338] · Oct 26, 2004

I've been trying to create useable Access applications for about the last
four years and am getting really fed up always running into messy deadends
about a day after trying to create a new .mdb file! All the text books
follow the same, "If you do this, this, this and that, then .... ta da!
Look what happens!", without any explanation of the fundamentals from whihc
you can learn to create your own applications from scratch. Has anyone any
tips of websites or books that for instance:

Show the logic behind how Access processes Queries, and how they sometimes
automatically 'pull-in' the related field on a table not included in the
Query, but which is the subject of a table's lookup relationship. How
Access uses Queries for instance to identify information that is in one
table but not in another (a kind of negative-addition, verging on black
magic) is a great mystery to me.

Why it is so easy to make a query that filters / collates data from many
tables, but going the other way, creating forms based on more than one
table, that update all those necessary related tables behind the scenes,
normally just results in the frustrating "Cannot update recordset" error
message.

Why do some but not all wizard-created table lookups not create a
relationship in the Show All relationships window, and what else is Access
hiding from us?

Why does the Group By option in Queries so annoyingly not have a simple
"Value" option in addition to Sum, Avg, Max and so on to make it useful?!
For instance, I've a table that shows what you might call "Current Tasks"
and a related table that shows the history of progress for those tasks. The
history progress table has fields of HistoryID, TaskID, Current Status,
DateRecordEntered. Common sense says that if I want a form or query that
extracts the latest Status of multiple tasks I need a Select Query that
Groups the TaskID, selects the maximum of the DateRecordEntered (so as to
get the latest entry for that TaskID, and I want the VALUE of the
CurrentStatus text field, but no! Access doesnt give that option!!
Argh!!!!

Cheers,
Brett

ITMA · Oct 26, 2004

Anyone know why when I read this in my Outlook Express I see only one reply
but in Google I can see three?

ITMA · Nov 13, 2004

Anyone know why when I read this in my Outlook Express I see only one reply

but in Google I can see three?

And why in Gravity newsreader I can only see one reply?!

How can I see all postings as evidenced by whats on Google's Newsgroup
section and have the facility to filter and reply to them? Clearly
Gravity and Outlook Express are not fool-proof, and Gravity seems
horrendously complicated in fact

Project Online Timesheets Actuals Mismatches - data not publishing to projects	0	Sep 12, 2017
Having problems understanding sub-form selections and uses of comb	2	Feb 22, 2010
Changes in Form (based on query) are reflected to the wrong table	2	Jun 21, 2009
Access 2007: Need more info on 'concatenante fields'	1	May 15, 2010
Table Design - stuck!	7	Nov 14, 2009
Are Lookup Fields Really Evil?	6	Apr 21, 2006
Practical concern with relationships	10	Aug 17, 2008
Understanding relationships	6	Oct 24, 2006

Understanding what makes Access tick

ITMA

John Vinson

Albert D. Kallal

Brett Collings [429338]

ITMA

ITMA

Ask a Question

Similar Threads