Hi Jesse,
First I'll go through the method that will not use the crosstabs. I'll use
your field names, and also group by year since you said that would be
preferred.
To start with, you need to define the individual queries that will select
the names from each individual field, so that they can be unioned in the next
step. We would like to know the name, the year, and how many times that name
occurs in that year for each field.
First, we'll define the select query for the PSS Names:
SELECT Personnel.PSS AS Person, Personnel.Year, Count(Personnel.PSS) AS
PSSCount, 0 AS PSS2Count
FROM Personnel
GROUP BY Personnel.PSS, Personnel.Year
HAVING Personnel.PSS Is Not Null;
This query will select all records from the Personnel table with non-null
entries in the PSS field. It will group the records by Name and Year, and
will provide the count of the number of records matching that name/year. It
will also enter a 0 in a column for PSS2Count (since those will be counted by
the next query).
Then, a similar query will count the PSS2 entries:
SELECT Personnel.PSS2, Personnel.Year, 0, Count(Personnel.PSS2)
FROM Personnel
GROUP BY Personnel.PSS2, Personnel.Year
HAVING Personnel.PSS2 Is Not Null;
The only differences being that I'm counting the PSS2 fields for those
records where PSS2 is not null, I didn't alias any of the fields (because the
union query in the next step will only recognize field names/aliases from the
first query anyway), and I entered the 0 in the PSSCount field and placed the
PSS2 count in the PSS2Count field.
Note that you could paste the sql from either of the queries above in the
sql view of a new query and see what they are doing. Note also that these
two queries are essentially doing the same thing as your existing crosstab
queries.
Next, we want to combine these results, so we use a union statement. To use
a union, you basically just type the word UNION between the sql of the two
queries, so our query becomes:
SELECT Personnel.PSS AS Person, Personnel.Year, Count(Personnel.PSS) AS
PSSCount, 0 AS PSS2Count
FROM Personnel
GROUP BY Personnel.PSS, Personnel.Year
HAVING Personnel.PSS Is Not Null
UNION
SELECT Personnel.PSS2, Personnel.Year, 0, Count(Personnel.PSS2)
FROM Personnel
GROUP BY Personnel.PSS2, Personnel.Year
HAVING Personnel.PSS2 Is Not Null;
This will give you the combination of the first two tables, which will be
similar to a combination of your existing crosstabs.
Next, we want to group the records in the union query by name and year, and
sum the counts. If the records from the union query above were in a table
called AllNames, the query would look like the following:
SELECT AllNames.Person, AllNames.Year, Sum(AllNames.PSSCount) AS PSSCount,
Sum(AllNames.PSS2Count) AS PSS2Count, Sum(AllNames.PSSCount) +
Sum(AllNames.PSS2Count) AS TotalNameCount
FROM AllNames
GROUP BY AllNames.Person, AllNames.Year
ORDER BY AllNames.Person, AllNames.Year;
But, instead of having the data in a table, we want to use our union query
as a source. We could save the union query, and reference it here in place
of AllNames. Or, we can just insert the sql for the query itself and alias
the resulting recordset as AllNames. When doing this, we enclose the source
sql in ()'s such as the following:
SELECT AllNames.Person, AllNames.Year, Sum(AllNames.PSSCount) AS PSSCount,
Sum(AllNames.PSS2Count) AS PSS2Count, Sum(AllNames.PSSCount) +
Sum(AllNames.PSS2Count) AS TotalNameCount
FROM (Place Union Query SQL Here) AS AllNames
GROUP BY AllNames.Person, AllNames.Year
ORDER BY AllNames.Person, AllNames.Year;
Therefore, inserting the Union Query SQL inside the ()'s gives the final
query sql:
SELECT AllNames.Person, AllNames.Year, Sum(AllNames.PSSCount) AS PSSCount,
Sum(AllNames.PSS2Count) AS PSS2Count, Sum(AllNames.PSSCount) +
Sum(AllNames.PSS2Count) AS TotalNameCount
FROM (SELECT Personnel.PSS AS Person, Personnel.Year, Count(Personnel.PSS)
AS PSSCount, 0 AS PSS2Count
FROM Personnel
GROUP BY Personnel.PSS, Personnel.Year
HAVING Personnel.PSS Is Not Null
UNION SELECT Personnel.PSS2, Personnel.Year, 0, Count(Personnel.PSS2)
FROM Personnel
GROUP BY Personnel.PSS2, Personnel.Year
HAVING Personnel.PSS2 Is Not Null) AS AllNames
GROUP BY AllNames.Person, AllNames.Year
ORDER BY AllNames.Person, AllNames.Year;
If you copy this sql and paste it into the sql view of a new query, I
believe that it will give you what you are looking for.
If you want to check against the results of your crosstabs, you can union
them by typing the following in a union query:
SELECT * FROM Xtab1
UNION ALL
SELECT * FROM Xtab2
Note that in this case I used Union All, because your counts in the Xtab
queries will be in the same column, and if you had records in the two Xtabs
that had the same values for all fields, a normal union query would exclude
them.
Then, you could do a summary query on the result of that union to get your
total count.
By, the way, if you want a query to give you the counts by name, without
grouping by year, the sql would be:
SELECT AllNames.Person, Sum(AllNames.PSSCount) AS PSSCount,
Sum(AllNames.PSS2Count) AS PSS2Count, Sum(AllNames.PSSCount) +
Sum(AllNames.PSS2Count) AS TotalNameCount
FROM [SELECT Personnel.PSS AS Person, Count(Personnel.PSS) AS PSSCount, 0 AS
PSS2Count
FROM Personnel
GROUP BY Personnel.PSS
HAVING Personnel.PSS Is Not Null
UNION SELECT Personnel.PSS2, 0, Count(Personnel.PSS2)
FROM Personnel
GROUP BY Personnel.PSS2
HAVING Personnel.PSS2 Is Not Null]. AS AllNames
GROUP BY AllNames.Person
ORDER BY AllNames.Person;
Hopefully this will help, and hopefully I got your table and field names
right. Post back if it doesn't work or if you have other questions.
-Ted Allen
