RE: adding 2 fields including null entries

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Ted Allen (TedAllen_at_discussions.microsoft.com)
Date: 01/11/05 

Date: Tue, 11 Jan 2005 12:09:02 -0800

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

"Jesse" wrote:

> I'll just give you as much detail as i can.
> Xtab1 has (Field Name-Total-Crosstab) PSS-Group By-Row Heading, Year-Group
> By Column Heading, Total Of PSS-Count-Row Heading, and PSS-Count-Value. Xtab2
> has PSS2-Group By-Row Heading, Year-Group By Column Heading, Total Of
> PSS2-Count-Row Heading, and PSS2-Count-Value. Then I have a table, Personnel
> with field Names, which provides the names for PSS and PSS2, and the whole
> point of this is to find how many times those names appear, so im not sure if
> you need this table. So i need a list of the names with their Total Of PSS &
> Total of PSS2 combined. The Year Column heading is not neccessary but it
> would be nice. I hope that you can understand all of this. Let me know if you
> need any more info. Thank you
>
> "Ted Allen" wrote:
>
> > Hi Jesse,
> >
> > You can union the results of the two crosstabs, but then you would still
> > have to group the results and sum them to get the totals. The query I had
> > posted would do everything directly, so that you wouldn't need the two
> > crosstabs at all. I would be happy to break it down for you, but, if you
> > don't mind, post the names of your two name fields and I will first revise
> > the sql so that it will work for you without modification. Then, I'll break
> > that one down into pieces to help explain what it is doing.
> >
> > -Ted Allen
> >
> > "Jesse" wrote:
> >
> > > I'm having difficulting following the code. I have 2 queries, Xtab1 and
> > > Xtab2, then a table, Personnel. The names lie in Personnel but im not sure
> > > that what i need even has to use those names since the ones im adding are
> > > already in Xtab1 and Xtab2. Each querie has a list of names with their
> > > totals. I do in fact want the Union of both queries, in the case if the name
> > > is in both queries i'd like their totals combined. Your code may be right,
> > > but i was just having trouble following it. Do you think there's anyway you
> > > can take this information and enhance it a little? Thanks a bunch.
> > >
> > > "Ted Allen" wrote:
> > >
> > > > Hi Jesse,
> > > >
> > > > If I understand your post correctly, I believe that your solution will
> > > > likely have to involve a union query somewhere because a left or right join
> > > > between your crosstab queries will potentially omit names that exist only in
> > > > the other column.
> > > >
> > > > You may want to consider a different approach. The following query will
> > > > calculate the name counts using a union query as the source. The union query
> > > > counts the names in each column, then the query further groups the names and
> > > > adds the results. The query assumes a table name of tblNames and field names
> > > > of Name1 and Name2. If you replace those with the actual table/field names
> > > > and paste the following into sql view of a new query, I believe you will get
> > > > what you are looking for. Post back if you have any difficulty. Here is the
> > > > sql:
> > > >
> > > > SELECT AllNames.NameGrp,
> > > > Sum(AllNames.CountOfName1) AS CountOfName1,
> > > > Sum(AllNames.CountOfName2) AS CountOfName2,
> > > > Sum(AllNames.CountOfName1) + Sum(AllNames.CountOfName2) AS TotalCountOfNames
> > > > FROM (SELECT tblNames.Name1 AS NameGrp,
> > > > Count(tblNames.Name1) AS CountOfName1, 0 AS CountOfName2
> > > > FROM tblNames
> > > > GROUP BY tblNames.Name1
> > > > HAVING tblNames.Name1 Is Not Null
> > > > UNION SELECT tblNames.Name2, 0, Count(tblNames.Name2)
> > > > FROM tblNames
> > > > GROUP BY tblNames.Name2
> > > > HAVING tblNames.Name2 Is Not Null) AS AllNames
> > > > GROUP BY AllNames.NameGrp
> > > > ORDER BY AllNames.NameGrp;
> > > >
> > > > HTH, Ted Allen
> > > >
> > > > "Jesse" wrote:
> > > >
> > > > > I'm working on a database which has 2 fields which each hold a name from a
> > > > > list of names in a table. The 2 fields represent the same thing in the big
> > > > > picture of the database, but for accesibility reasons, i need two have 2
> > > > > fields. I need a query which can add the number of times each name appears in
> > > > > both fields and then combine them. I have 2 crosstab queries which count the
> > > > > number of times each name appears in each separate column. Some of the names
> > > > > in one field are not neccesarily in the other so when i try to make a query
> > > > > that adds the values of the 2 crosstab queries, it only gives the names and
> > > > > combined values of the names in both fields (the intersection of both
> > > > > columns). I'm aware that when adding two fields and if one is null the answer
> > > > > is null, but there has to be some way to get this to work. Thanks you. Help
> > > > > would be much appreciated!
> > > > >

Relevant Pages

  • Re: Please help me evolve this concept
    ... complex SQL SELECT statements including UNION, OUTER JOINS, GROUP BY, ... It sure will save you a lot of time and coding if all you need is to query ... I could modify this in code and qActorSelect stays 'dynamically ... creating a union query based on qActorSelect as opposed to ...
    (microsoft.public.dotnet.framework.adonet)
  • RE: Union of two crosstabs?
    ... The two crosstabs are saved as seperate queries. ... crosstab all in a query, or do I have to use macro's to sequence the steps. ... save each crosstab and then create a third query to do the union. ... TRANSFORM SumAS SumOfApplications ...
    (microsoft.public.access.queries)
  • RE: number of columns doesnt match bug in Access
    ... The naming of the fields using reserved words could be an source of problems ... Also you use as an alias in the SQL. ... as they must in a Union Join. ... I have a union query which regularily generates the "number of columns ...
    (microsoft.public.access.queries)
  • RE: number of columns doesnt match bug in Access
    ... Also you use as an alias in the SQL. ... as they must in a Union Join. ... I have a union query which regularily generates the "number of columns ... I use the VBA code On Error resume to get it to work - ...
    (microsoft.public.access.queries)
  • RE: report / Query
    ... CreditAgency field to store the CreditAgency ID number from a CreditAgency ... Then I would create a union query, which is simply two independent queries ... created both queries, switch to SQL View in one of them and ...
    (microsoft.public.access.gettingstarted)