adding 2 fields including null entries

[Guest]

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

 

[Guest]

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.

 

[Guest]

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."

 

[Guest]

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.

 

[Guest]

..everything seems to work. i went through and changed the query, in Word, for
all the other fields in my table and everything seems to be working. one more
final question that might not be as difficult. Is there anyway i can set up a
query or report that searches every record in my Start Up table and groups
all the records in which a specific name is found in any field. Almost like a
Find function that if the name is anywhere in any field of the record, that
record is displayed. Thanks a ton for the query help. I can't tell you how
gracious i am.

 

[Paul Frey via AccessMonster.com]

Ted,

My name is Paul and I have a similar issue to Jesse's. I read your reply about using a union query but I am having trouble understanding the code. I have one table (Recipient Menu) and two of its columns (MainDish1) & (MainDish2) can have results of the same name in both. I need to do a name count for both columns with one total for each name. I would appreciate any assistance that you could provide.

Thanks,
Paul

 

[Guest]

Hi Paul,

A basic union query should give you what you need. To put together a union
query, you just create two (or more) select queries, and then add the word
UNION between them. The union query will use the field names from the first
query, and the fields will be combined according to their order.

In your case, I think that all you would need to be able to get your summary
counts is a query that gives the original field name, and the field value.
You could then run any kind of summary query you wanted.

The trickiest thing about creating UNION queries when you are new to them is
learning the SQL syntax. But, as a tool, you can use the regular query
design window to set up the query, then switch to sql view to see the sql.
If you do that with the two queries, you can then create a third query,
define it as a union query, paste the first query sql, delete the ; at the
end, hit return to add a new line below, type "UNION ALL" (a regular UNION
will dump duplicates, UNION ALL will not), and then paste the sql of the
second query.

In your case, it would probably look something like this:

(Recipient Menu) and two of its columns (MainDish1) & (MainDish2)

SELECT "MainDish1" AS SourceField, RM.MainDish1 AS MainDish FROM [Recipient
Menu] AS RM
UNION ALL
SELECT "MainDish2", RM.MainDish2 FROM [Recipient Menu] AS RM
ORDER BY MainDish, SourceField;

You can try pasting this sql into the sql view of a new query, It should
work for you if I didn't make any typos (which I often do). If you wanted to
get the total of each type for each field, you could create a query based on
this query to group by SourceField and by MainDish, and count the MainDish
field, or if you wanted the total in both fields, you could just group by
MainDish and Count MainDish.

Of course, the query could be written in such a way to do all of this at
once, but it does get more complex and harder to follow the SQL if you aren't
used to SQL syntax. Post back if you really want to do it all at once and I
can help with that. If you do though, post a little more info on what you
want to see as far as the totals go (do you want one line per main dish, with
subtotal columns for each field, and then a total column, whether you want to
count or omit nulls, etc).

HTH, Ted Allen