adding 2 fields including null entries

[Jesse]

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!

 

[Ilan]

Hello,
I had a similar problem with the addition. The way to
solve your math issues is with an Nz field. In a query,
create a new calculated field and write an expression
like this:

NameofCalculatedField: Nz([FieldwithNullValue], 0)

Basically, create a new field and name it anything you
want. The query will read this line of code and if there
is a null value in the field (that i named for example
purposes) "FieldwithNullValue" then it will replace the
null with a zero. You can change the zero to whatever
value you want, but it sounds like you just want to add
it and count it as a zero.

Hope this was Helpful,
Ilan

 

[Jesse]

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!

It is still giving me the intersection of both columns, with the names that
are only in both fields. I need all the names in the first column with their
sum, all the names in the second with their their sum, and all the names in
both with the sum added from both columns.

 

[Ilan]

Check your join properties. Go to design view of the
query and right click on the relationship. Click join
properties. I think you should have a Left Join which is
the second option down. Right now, from what i can tell,
you sound like you have a plain inner join. Let me know.

Ilan

 

[Jesse]

Ok, i changed to the second option, it didnt work, and i tried the third
option which half way worked. I have a table with the names and 2 queries
with the two columns' totals. All 3 are in this query. Any other ideas?

 

[Ted Allen]

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]

Ok, first i tried:

SELECT * FROM Xtab1
UNION SLECT * FROM Xtab2

and this worked except the names that were in both showed up separate. I
then went and did a summary query but it still gave me the same info. Could
an IIf statement in the sql solve this? If i can find an answer to this i
might as well just forget about the sql doing the crosstabs b/c i know that i
can get this to work.

When it comes to the sql which does the the crosstab work for me, my person
field (i.e. "AllNames.Person") and my Year field, come from different tables,
and my PSS field also comes from a different table. So, should i need
something different in the FROM statements and something different in the
____.Person, and ___.Year? Thank you very much for your help so far. I'm
making progress. My email is (e-mail address removed) if you want to reply there
b/c finding this entry is getting tedious. Thanks a bunch.

 

[Ted Allen]

Hi Jesse,

Yes, the previous sql would have to be modified if the Year value is coming
from a different table. But, you didn't give any other details on the other
table so I can't really go any further with that.

The sql that I posted at the end of the previous message should still work,
it just won't have the year values.

In looking back at your previous post, I now realize that it looks like you
have set up the crosstabs to provide unique columns for each year. This will
create problems with the Union Query, because the Union will just line up the
colums by their order, not by name. So, if the first starts with Year 2000,
and the second with 2001, you will have mixed data, and the column will be
labeled according to the first query. So, before using the union query on
your crosstabs, you will need to assign fixed column headings to each to make
sure that they will always line up (you do this under the column headings
property of the query).

But, if you want the columns by year, and you want it to be more flexible,
it would be better to union the data first, then use that as the data source
for your crosstab.

If you post back with the table detail, I can give you the sql for that.

Right now it looks like you have a table Personnel, with Fields PSS and
PSS2, and a separate table somehow providing the year. Post some info on the
other table name, and how it joins to the Personnel table.

Or, post the SQL for your two existing crosstab queries (switch to sql view
and just copy/paste), that will probably give me all I need (your earlier
post only gave the values from the design grid, but that doesn't say what the
source tables are, or their relationships).

By the way, I try to keep the posts in the group so that others may benefit
as well if they need to do something similar. But, it should be pretty easy
to locate the post if you just search for your name (which is why I always
end my posts with my full name).

HTH, Ted Allen

 

[Jesse]

Alright, here are my two existing crosstab queries.

TRANSFORM Count([Start Up].[Pre-Ship Setup]) AS [CountOfPre-Ship Setup]
SELECT [Start Up].[Pre-Ship Setup], Count([Start Up].[Pre-Ship Setup]) AS
[Total Of Pre-Ship Setup]
FROM [Start Up]
GROUP BY [Start Up].[Pre-Ship Setup]
PIVOT [Start Up].Year;

and

TRANSFORM Count([Start Up].[Pre-Ship Setup 2]) AS [CountOfPre-Ship Setup 2]
SELECT [Start Up].[Pre-Ship Setup 2], Count([Start Up].[Pre-Ship Setup 2])
AS [Total Of Pre-Ship Setup 2]
FROM [Start Up]
GROUP BY [Start Up].[Pre-Ship Setup 2]
PIVOT [Start Up].Year;


Let me know if you need more information

 

[Ted Allen]

Thanks Jesse.

I think that should be enough. I assume then that these are the actual
table/field names, and the others that you had posted were shortened
versions? Or, is [Start Up] a query that pulls together the personnel and
year info? It's a little confusing because you had mentioned before that the
year info was coming from a different table than the PSS fields, but these
all appear to be coming from [Start Up].

In any case, I should be able to post the sql shortly using [Start Up] as
the source, and using the field names referenced in the crosstab sql.

In the meantime, post back if any of my assumptions above are incorrect.
Also, if [Start Up] is a query, you could post the sql for that query if you
would like for me to post a query based directly on the source queries rather
than that query. Finally, let me know what your ideal result would be. Do
you want just the total count by year, would you also like subtotals of each
type? Etc.

-Ted Allen

 

[Ted Allen]

OK, I think the following union query should give you a good basis to create
a pivot from (I added a field PSSType to identify which column the records
came from, in case you later decided to differentiate by those as well):

SELECT [Start Up].[Pre-Ship Setup] AS PSSName, [Start Up].Year, "PSS" AS
PSSType, Count([Start Up].[Pre-Ship Setup]) AS PSSCount
FROM [Start Up]
GROUP BY [Start Up].[Pre-Ship Setup], [Start Up].Year, "PSS"
HAVING [Start Up].[Pre-Ship Setup] Is Not Null
UNION SELECT [Start Up].[Pre-Ship Setup 2], [Start Up].Year, "PSS2" AS Type,
Count([Start Up].[Pre-Ship Setup 2])
FROM [Start Up]
GROUP BY [Start Up].[Pre-Ship Setup 2], [Start Up].Year, "PSS2"
HAVING [Start Up].[Pre-Ship Setup 2] Is Not Null;

If you save the union query as qUnionStartup, you could use the following
crosstab query:

TRANSFORM Sum(qUS.PSSCount) AS SumOfPSSCount
SELECT qUS.PSSName
FROM qUnionStartup AS qUS
GROUP BY qUS.PSSName
PIVOT qUS.Year;

Or, if you want to do it all in one shot, you can use the following:

TRANSFORM Sum(qUS.PSSCount) AS SumOfPSSCount
SELECT qUS.PSSName
FROM (SELECT [Start Up].[Pre-Ship Setup] AS PSSName, [Start Up].Year, "PSS"
AS PSSType, Count([Start Up].[Pre-Ship Setup]) AS PSSCount
FROM [Start Up]
GROUP BY [Start Up].[Pre-Ship Setup], [Start Up].Year, "PSS"
HAVING [Start Up].[Pre-Ship Setup] Is Not Null
UNION SELECT [Start Up].[Pre-Ship Setup 2], [Start Up].Year, "PSS2" AS Type,
Count([Start Up].[Pre-Ship Setup 2])
FROM [Start Up]
GROUP BY [Start Up].[Pre-Ship Setup 2], [Start Up].Year, "PSS2"
HAVING [Start Up].[Pre-Ship Setup 2] Is Not Null) AS qUS
GROUP BY qUS.PSSName
PIVOT qUS.Year;

Post back if you have any problems, or if you would like any other summary
info.

-Ted Allen

 

[Jesse]

Well, i think we're almost there. It seems to work. Start Up is actually a
table, and Year has its own table, but it is used as a lookup for one of the
fields in Start Up. Along with the totals for each year, at total of all the
years combined would be useful too. And, finally I have about 3 more sets of
fields i have to do this with in my Start Up table. If i just substitute the
field names that should be fine shouldnt it? Thank you very much.
Jesse

 

[Jesse]

ok, ive been messing around with it and i copied and pasted what you gave me
and it worked. i tried another query and just substituted the fields and it
gave me an error when i tried to save:

Syntax error in query. Incomplete query clause.

I kept getting that error and then one time i copied the first sql which
worked and it gave me the same thing even though the first one worked. i dont
understand why it wont run the exaxt same query.

 

[Ted Allen]

I'm not sure why the error. Try posting the sql of the revised query and
I'll see if I see anything unusual. Sometimes Access revises the SQL,
especially if you switch to design view. If that continues to be a problem,
it may be better to save the union query as one query, and base the pivot on
that (although I set up a test table on my end, and I haven't had any
problems - could be diff versions of Access).

Here is revised sql for a query that will give subtotals for each type, as
well as an overall total.

TRANSFORM Sum(qUS.PSSCount) AS SumOfPSSCount
SELECT qUS.PSSName, Sum(qUS.PSSCount) AS TotalPSS, Abs(Sum((PSSType =
"PSS")*PSSCount)) as SubTotalPSS1, Abs(Sum((PSSType = "PSS2")*PSSCount)) as
SubTotalPSS2
FROM (SELECT [Start Up].[Pre-Ship Setup] AS PSSName, [Start Up].Year, "PSS"
AS PSSType, Count([Start Up].[Pre-Ship Setup]) AS PSSCount
FROM [Start Up]
GROUP BY [Start Up].[Pre-Ship Setup], [Start Up].Year, "PSS"
HAVING [Start Up].[Pre-Ship Setup] Is Not Null
UNION SELECT [Start Up].[Pre-Ship Setup 2], [Start Up].Year, "PSS2" AS Type,
Count([Start Up].[Pre-Ship Setup 2])
FROM [Start Up]
GROUP BY [Start Up].[Pre-Ship Setup 2], [Start Up].Year, "PSS2"
HAVING [Start Up].[Pre-Ship Setup 2] Is Not Null) AS qUS
GROUP BY qUS.PSSName
PIVOT qUS.Year;

To answer your earlier question, you should be able to use the same syntax
to compare other pairs of fields. Just past the text in Word, find/replace
the field names, then copy/paste the sql to a new query.

Post back and let me know how it goes.

-Ted Allen

 

[Jesse]

If i wanted just the combined total and Years columns without the 2 subtotals
what would i have to take out. And I'm still getting that error when i try to
save the same query with different field names. Here is what will not save:

TRANSFORM Sum(qUS.PSSCount) AS SumOfPSSCount
SELECT qUS.PSSName, Sum(qUS.PSSCount) AS TotalPSS, Abs(Sum((PSSType =
"PSS")*PSSCount)) AS SubTotalPSS1, Abs(Sum((PSSType = "PSS2")*PSSCount)) AS
SubTotalPSS2
FROM [SELECT [Start Up].[Plant Install] AS PSSName, [Start Up].Year, "PSS"
AS PSSType, Count([Start Up].[Plant Install]) AS PSSCount
FROM [Start Up]
GROUP BY [Start Up].[Plant Install], [Start Up].Year, "PSS"
HAVING [Start Up].[Plant Install] Is Not Null
UNION SELECT [Start Up].[Plant Install 2], [Start Up].Year, "PSS2" AS Type,
Count([Start Up].[Plant Install 2])
FROM [Start Up]
GROUP BY [Start Up].[Plant Install 2], [Start Up].Year, "PSS2"
HAVING [Start Up].[Plant Install 2] Is Not Null]. AS qUS
GROUP BY qUS.PSSName
PIVOT qUS.Year;

Its the same case except instead of Pre-Ship Setup its Plant Install. And it
still will not save the original sql when i try to make another copy of it.

 

[Jesse]

when i get the error, it goes back to the sql and the cursor is in between
the s and the f in the word Transform.

 

[Ted Allen]

Hi Jesse,

Sorry it took so long to respond, I was out of the office all day. I have
to head out now, but it should only take a few minutes to revise the sql in
the morning to get rid of the subtotal fields. All you really have to do is
get rid of the two parts containing the abs() function, but if you don't need
those I can simplify the overall sql by getting rid of the type designation
as well.

I'll also look over the sql that you posted and test it on my end to see
what I can find.

-Ted

 

[Ted Allen]

OK, I believe the sql below should work for your two queries, note that I
made the column labels more general for the second in case you want to use
that as a base for future similar queries.

First query, revised original query without subtotals:

TRANSFORM Sum(qUS.PSSCount) AS SumOfPSSCount
SELECT qUS.PSSName, Sum(qUS.PSSCount) AS TotalPSS
FROM (SELECT [Start Up].[Pre-Ship Setup] AS PSSName, [Start Up].Year,
Count([Start Up].[Pre-Ship Setup]) AS PSSCount
FROM [Start Up]
GROUP BY [Start Up].[Pre-Ship Setup], [Start Up].Year
HAVING [Start Up].[Pre-Ship Setup] Is Not Null
UNION SELECT [Start Up].[Pre-Ship Setup 2], [Start Up].Year, Count([Start
Up].[Pre-Ship Setup 2])
FROM [Start Up]
GROUP BY [Start Up].[Pre-Ship Setup 2], [Start Up].Year
HAVING [Start Up].[Pre-Ship Setup 2] Is Not Null) AS qUS
GROUP BY qUS.PSSName
PIVOT qUS.Year;

Second Query, similar, but for Plant Install fields:

TRANSFORM Sum(qUS.FieldCount) AS SumOFieldCount
SELECT qUS.FieldName, Sum(qUS.FieldCount) AS FieldCount
FROM (SELECT [Start Up].[Plant Install] AS FieldName, [Start Up].Year,
Count([Start Up].[Plant Install]) AS FieldCount
FROM [Start Up]
GROUP BY [Start Up].[Plant Install], [Start Up].Year
HAVING [Start Up].[Plant Install] Is Not Null
UNION SELECT [Start Up].[Plant Install 2], [Start Up].Year, Count([Start
Up].[Plant Install 2])
FROM [Start Up]
GROUP BY [Start Up].[Plant Install 2], [Start Up].Year
HAVING [Start Up].[Plant Install 2] Is Not Null) AS qUS
GROUP BY qUS.FieldName
PIVOT qUS.Year;

Regarding the syntax error that you are getting, I think it is related to
the parenthesis surrounding the inner SELECT statement. Access often
converts these to square brackets, with a period after the closing square
bracket (this was the syntax used in Access 97, but later versions supposedly
use the ()'s, but still seem to convert to the square brackets). I'm not
sure exactly what is going on, but it appears to me that if you write or
paste the sql using parenthesis the first time, the query will work, even
after Access converts them to square brackets with a trailing dot. But, if
you write/paste the sql with the square brackets in the first place, Access
will not accept the syntax. Seems weird, but that's what seems to be
happening. When I changed the square brackets to ()'s in the sql you posted,
the query worked. Then, after closing and reopening, the sql had been
converted to square brackets, but continued to work.

If this gets to be a pain for you, you may want to save the inner select
statement (the union query) as a standalone query, and then use that as the
source for the crosstabs. If you want to do that, do the following:

Paste all sql text inside the ()'s or square brackets into a new query, add
a semi-colon at the end, and save.

For example, in the first query above, this would be:

SELECT [Start Up].[Pre-Ship Setup] AS PSSName, [Start Up].Year, Count([Start
Up].[Pre-Ship Setup]) AS PSSCount
FROM [Start Up]
GROUP BY [Start Up].[Pre-Ship Setup], [Start Up].Year
HAVING [Start Up].[Pre-Ship Setup] Is Not Null
UNION SELECT [Start Up].[Pre-Ship Setup 2], [Start Up].Year, Count([Start
Up].[Pre-Ship Setup 2])
FROM [Start Up]
GROUP BY [Start Up].[Pre-Ship Setup 2], [Start Up].Year
HAVING [Start Up].[Pre-Ship Setup 2] Is Not Null;

For our example, lets say you saved this as Query1, your crosstab query
would then become:

TRANSFORM Sum(qUS.PSSCount) AS SumOfPSSCount
SELECT qUS.PSSName, Sum(qUS.PSSCount) AS TotalPSS
FROM Query1 AS qUS
GROUP BY qUS.PSSName
PIVOT qUS.Year;

Note that all I did there was replace everything inside the parenthesis (and
the parenthesis as well) with the name of the union query. I kept the alias
for the union query, so that it wouldn't be necessary to change all of the
other field references.

Hope that helps. Post back and let me know how it goes.

-Ted Allen

Hi Jesse,

Sorry it took so long to respond, I was out of the office all day. I have
to head out now, but it should only take a few minutes to revise the sql in
the morning to get rid of the subtotal fields. All you really have to do is
get rid of the two parts containing the abs() function, but if you don't need
those I can simplify the overall sql by getting rid of the type designation
as well.

I'll also look over the sql that you posted and test it on my end to see
what I can find.

-Ted

If i wanted just the combined total and Years columns without the 2 subtotals
what would i have to take out. And I'm still getting that error when i try to
save the same query with different field names. Here is what will not save:

TRANSFORM Sum(qUS.PSSCount) AS SumOfPSSCount
SELECT qUS.PSSName, Sum(qUS.PSSCount) AS TotalPSS, Abs(Sum((PSSType =
"PSS")*PSSCount)) AS SubTotalPSS1, Abs(Sum((PSSType = "PSS2")*PSSCount)) AS
SubTotalPSS2
FROM [SELECT [Start Up].[Plant Install] AS PSSName, [Start Up].Year, "PSS"
AS PSSType, Count([Start Up].[Plant Install]) AS PSSCount
FROM [Start Up]
GROUP BY [Start Up].[Plant Install], [Start Up].Year, "PSS"
HAVING [Start Up].[Plant Install] Is Not Null
UNION SELECT [Start Up].[Plant Install 2], [Start Up].Year, "PSS2" AS Type,
Count([Start Up].[Plant Install 2])
FROM [Start Up]
GROUP BY [Start Up].[Plant Install 2], [Start Up].Year, "PSS2"
HAVING [Start Up].[Plant Install 2] Is Not Null]. AS qUS
GROUP BY qUS.PSSName
PIVOT qUS.Year;

Its the same case except instead of Pre-Ship Setup its Plant Install. And it
still will not save the original sql when i try to make another copy of it.

 

[Jesse]

for both of the queries i get an error when i run them saying:
"The Microsoft Jet Database does not recognize "[Start Up].[Pre-Ship Setup
2/ Plant Install 2]" as a valid field name or expression."

 

[Jesse]

just forget about that last post. i went back and just took the Abs() lines
out of the queries that worked. im going to test it out some more and i'll
post back in a little while.