Summing 1 or 2 or 3 values from 3 tables

[RxbGxxdwxn]

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.

 

[John W. Vinson]

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?

 

[RxbGxxdwxn via AccessMonster.com]

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.

John, if you can help, my graditude.

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

[quoted text clipped - 24 lines]

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]

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?

 

[Roby Goodwin via AccessMonster.com]

This is a real world problem. I'm working with an older special use
application delivered in a unique installation by the vendor. I'm using email
counts as a substitute for the real, sensitive data.

The larger database/application came with limited reporting ability. I don't
have control over the parameters for reports or the output; either view it on
screen, print hard copies, or if I want digital data, I can only get CSV
files. I take these and import into Excel then Access. I could probably
import into Access directly and will try when I get to work this morning. So,
I'm not querying to get the data but working with canned Crystal Reports.

The data I get through 1 particular report is limited to the status of my
persons as of the date I run the report. If I run it the following day, I'll
get slightly different results depending upon what my persons have done in
the 24 hours that have transpired.

I want to develop an average per person for the quarter which may mean person
A is only on 1 list out of 6 or 7, or may be on 3 or 6 or 7, all depending on
their actions in this period.

So I want to merge 6 or 7 lists, depending up how many times this report is
run in a quarter, into one file. This would contain a distinct listing of
every person on the periodic lists I generate. The average will be based on
1 or up to 6/7 occurences on the lists. The sum of all emails sent, whether
1 or 7 counts, divided by the corresponding # of occurences on the lists,
gives me the average.

The ranking from most to least is a secondary issue that I can handle once I
get the consolidated list.

I hope this helps. I'm simply using Access as a tool to consolidate the
separate lists and generate an average, not to create a new database. I've
done this often in the past not knowing how to do this kind of thing in Excel.

Thanks for your response. My mistake in setting up the problem statement led
to confusion.

[quoted text clipped - 16 lines]

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?

 

[Roby Goodwin via AccessMonster.com]

John or anyone, I've decided this will be the start of a database with this
information the foundation on which to build and link other information.

So I would appreciate some advice here.

Rob

This is a real world problem. I'm working with an older special use
application delivered in a unique installation by the vendor. I'm using email
counts as a substitute for the real, sensitive data.

The larger database/application came with limited reporting ability. I don't
have control over the parameters for reports or the output; either view it on
screen, print hard copies, or if I want digital data, I can only get CSV
files. I take these and import into Excel then Access. I could probably
import into Access directly and will try when I get to work this morning. So,
I'm not querying to get the data but working with canned Crystal Reports.

The data I get through 1 particular report is limited to the status of my
persons as of the date I run the report. If I run it the following day, I'll
get slightly different results depending upon what my persons have done in
the 24 hours that have transpired.

I want to develop an average per person for the quarter which may mean person
A is only on 1 list out of 6 or 7, or may be on 3 or 6 or 7, all depending on
their actions in this period.

So I want to merge 6 or 7 lists, depending up how many times this report is
run in a quarter, into one file. This would contain a distinct listing of
every person on the periodic lists I generate. The average will be based on
1 or up to 6/7 occurences on the lists. The sum of all emails sent, whether
1 or 7 counts, divided by the corresponding # of occurences on the lists,
gives me the average.

The ranking from most to least is a secondary issue that I can handle once I
get the consolidated list.

I hope this helps. I'm simply using Access as a tool to consolidate the
separate lists and generate an average, not to create a new database. I've
done this often in the past not knowing how to do this kind of thing in Excel.
[quoted text clipped - 7 lines]

can then base a Totals query? What does the "ranked most to least" have to do
with the desired result?