help with a table join (i can't crack this nut)

[jonnycattt]

Greetings all. I've been wracking my brains for a few days on this one, and I think it's time I ask the forums.

First, the data:

TABLE 1:

ArticleID Issue Edit Check ProofCheck
-----------------------------------------
7687 Jan 1 KM GW MKM
7688 Jan 1 JL GW HC
7689 Jan 1 COB HC MKM
7690 Jan 15 CA KS GJ

and so on.....

The initials in the Edit, Check, and ProofCheck columns reference another table:

Editors Table:

EdInitials(PK) IC
-------------------
GJ no
GW no
HC no
KM no
KS no
MKM no
CA yes
COB yes
JL yes
and so on...

Finally, the Issue in the first table references an "Issues" table, with data like this:

Issues table:

Issue | year
---------------------------
Jan 1 | 2002
Jan 15 | 2002
Feb 1 | 2002
Feb 15 | 2002
Mar 1 | 2002
and so on......

So, what I Need is a query that returns a count of how many articles each editor edited for all editors, and how many articles each editor checked and proofchecked (just for editors where IC is 'no'). and I need this grouped by Issue

So my result would look like this:

Issue EdInitials EditCount CkCount PrCkCount
---------------------------------------------
Jan 1 GJ 5 8 3
Jan 1 GW 3 5 9
Jan 1 KM 9 2 2
Jan 1 JL 9 NULL NULL
and so on for each issue, each editor...

JL is Null b/c he is an IC and thus would not have his initials anywhere in the Check or ProofCk columns (only in the Edit Column).

I can write 3 separate queries and get all of this information easily. What I'd like is to merge all of these queries into one query (or else join these queries) so that I'm looking at one result set and not 3.

I can't imagine why this is so hard, because it doesnt' seem like it should be. Still, I am having no luck figuring it out.

I'd appreciate any help.

Thanks.

Marc

[This message has been edited by jonnycattt (edited 02-11-2002).]

[This message has been edited by jonnycattt (edited 02-11-2002).]

 

[jonnycattt]

OK, figured it out with the help of a friend:

here's the result:

query 1:
select a.Issue,a.Edit as EdInitials,count(*) as EditCount,0 as CheckCount,0 as ProofCkCount
from [2002 ji papers] as a
group by a.issue,a.Edit
UNION
select a.Issue,a.check as EdInitials,0 as EditCount,count(*) as CheckCount,0 as ProofCkCount
from [2002 ji papers] as a inner join editors on a.ProofCk=editors.edinitials
where editors.ic=false
group by a.issue,a.check
UNION select a.Issue,a.ProofCk as EdInitials,0 as EditCount,0 as CheckCount,count(*) as ProofCkCount
from [2002 ji papers] as a inner join editors on a.ProofCk=editors.edinitials
where editors.ic=false
group by a.issue,a.ProofCk;

Then query 2, which gets me the result I need:

SELECT qry_getWorkSummary.Issue, qry_getWorkSummary.EdInitials, Editors.IC, Sum(qry_getWorkSummary.EditCount) AS SumOfEditCount, Sum(qry_getWorkSummary.CheckCount) AS SumOfCheckCount, Sum(qry_getWorkSummary.ProofCkCount) AS SumOfProofCkCount
FROM Editors INNER JOIN (Issues INNER JOIN qry_getWorkSummary ON Issues.Issue = qry_getWorkSummary.Issue) ON Editors.EdInitials = qry_getWorkSummary.EdInitials
WHERE (((qry_getWorkSummary.EdInitials)<>'AWMR' And (qry_getWorkSummary.EdInitials)<>'Killed' And (qry_getWorkSummary.EdInitials)<>'HOLD'))
GROUP BY qry_getWorkSummary.Issue, qry_getWorkSummary.EdInitials, Editors.IC, Issues.IssueID, Issues.IssueID, qry_getWorkSummary.EdInitials
ORDER BY Issues.IssueID, Editors.IC, Issues.IssueID;

 

[Ziggy1]

I would put the 3 tables together in a query.
Then link the related fields.

set the join properties to include all records from table1

select "Totals" from the view menu and on the "Totals" row select "Count" function for the fields that you want to count.

It works but I am not sure if it is what you are looking for.