On Sat, 29 Nov 2008 22:28:31 GMT, "RxbGxxdwxn" <u47884@uwe> wrote:

>I will have more than 3 but let's start here.
>I want a final merged single list of distinct names with the sum of 1 or 2 or
>3 values as may be.
>My basic knowledge of joins and the basic query types isn't helping.
>I have a very basic101 level of knowledge of SQL.
>
>So how do I do this?
>
>table1
>names values
>name1 1
>name2 2
>name3 3
>name4 4
>
>table2
>name1 2
>name2 3
>name3 4
>name5 5
>
>table3
>name1 3
>name2 4
>name3 5
>name4 6
>
>end.

First off... you should NOT store sums in *any* table. If you store them on
disk in a table, then any change to the value in any of the underlying tables
will cause your sum to be WRONG, with no easy way to detect the error.
Instead, use a Totals query to calculate the total on the fly.

Secondly, if you have multiple tables (your table1 and table2) with the same
fieldnames and structures... your database design is probably incorrect. What
are these tables, really? Why do you need to sum across two tables?

Thirdly, how do you get your sums? They don't make sense to me: name3 is 3 in
table1 and 4 in table2, shouldn't the sum be 7 rather than 5?
--

John W. Vinson [MVP]

On Sun, 30 Nov 2008 12:34:17 GMT, "RxbGxxdwxn via AccessMonster.com"
<u47884@uwe> wrote:

>Thanks for your response. My mistake in setting up the problem statement led
>to confusion.
>
>The "data" is drawn from a larger database and consists of a periodic
>snapshot of names with associated counts of say emails sent. Each periodic
>list is ranked from most to least and each snapshot will have a varying set
>of names depending on who has sent emails and the # sent.
>
>I'm using Access not to develop an actual database but in an attempt to get
>the total emails per person sent for a quarter to develop an average for each
>person for the quarter; the numerator being the total sent and the
>denominator being the # of times the person appears on the list, say 3 to
>7/8 times.
>
>The value for name3 in Table3 is not a sum but an independent datum.
>
>I could use a Totals query (after seing the structure) for an on-the-fly look
>though the data is fixed. I thought a Query, or a Query on queries, the way
>to go but I'm completely open to any option or series of steps to get to the
>final result.

I guess I really don't understand. Are these "periodic lists" separate
queries? or separate tables generated by some process? Do you perhaps need a
UNION query to bring these separate records into one recordset, on which you
can then base a Totals query? What does the "ranked most to least" have to do
with the desired result?

--

John W. Vinson [MVP]