How do I work with one-to-many relations in Access queries?

[Guest]

I'm using Access 2002.
(1) I have two tables with a one-to-many relation between them. To make
this discussion clearer, let's call the two tables Table1 and Table2, where
field Title is unique in Table1 but may have the same value in multiple
records in Table2. Let's suppose there's also another field in Table2 called
Date.
For each record in Table1, I'd like to find, say, the largest value of Date
in records in Table2 associated with the record in Table2. That is, for a
record in Table1, I'd like to find the maximum value of Date among all
records in Table2 that have the same Title as the given record in Table1.
(Assume there are no ties in the value of Date in Table2 among records with
the same value of Title.)
(2) To complicate things further, suppose there's a third table, Table3, and
there's a one-to-many relation between Table2 and Table3 on another field,
say SubTitle. Suppose Table3 has a field called Amount. Now, for each
record in Table1, I'd like to find the average value of the Amounts
associated with the record in Table2 that has the maximum value of Date.
Surely these are easily done in queries. Unfortunately I'm a neophyte in
Access, so I don't know how to go about this. Can someone help?

 

[Edward G]

Understand that when you are building queries in Access, in addition to
querying tables
you can query queries. So for your example you would actually build three
queries. The first
would find max dates and only include tables 1 and 2. The second query would
only include
tables 2 and 3 and find avgs. The third query would query the first two
queries.
For your first query either use the design wizard or go to design view and
add tables 1 and 2 and the
two fields you are interested in. Then on the toolbar in design view click
the greek symbol Sigma. This is the totals button.
The design grid will now include a row called Totals. Your field called
Title? will show "Group by" in the totals row.For your date field select
"Max" from the drop down list in the totals row. You will probably
notice that the drop down list also includes "avg" as a choice. Use this in
your second query. Then construct your third query by choosing these first
two queries in the wizard or in design view.
Hope that helps.

Ed G