Re: Understanding what makes Access tick

• Previous message: JJ: "Access97 database recovery"
• In reply to: ITMA: "Understanding what makes Access tick"
• Next in thread: Brett Collings [429338]: "Re: Understanding what makes Access tick"
• Messages sorted by: [ date ] [ thread ]

Date: Tue, 26 Oct 2004 00:21:12 -0600

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.

-- 
Albert D. Kallal   (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@msn.com
http://www.attcanada.net/~kallal.msn

• Previous message: JJ: "Access97 database recovery"
• In reply to: ITMA: "Understanding what makes Access tick"
• Next in thread: Brett Collings [429338]: "Re: Understanding what makes Access tick"
• Messages sorted by: [ date ] [ thread ]