Results 1 to 6 of 6
  1. #1
    coach32 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Aug 2011
    Posts
    47

    Showing zero values in a Union Query

    I have created a union query that counts the number of records that meet my criteria. There are a number of criteria, so I get a number of values. What I need to do ios also show the criteria that has no matches (zero). As this is a query I need to run daily, some of the criteria will have values most days but then there will be days that there are no matches to the criteria. I have included a sample of the code I use. Can anyone please advise me what I need to do to get the '0' to show in the Datasheet view of the query. Thank-you in anticipation of any help you can give.
    Code:
    SELECT Count([Compromised TFN - master].Team_Id) AS CountOfTeam_Id, [Compromised TFN - master].Acty_Priority, [Compromised TFN - master].Acty_Cmplxty
    FROM [Compromised TFN - master]
    GROUP BY [Compromised TFN - master].Team_Id, [Compromised TFN - master].Acty_Cntct_Typ_Nm, [Compromised TFN - master].Intractn_Sts, [Compromised TFN - master].Acty_Priority, [Compromised TFN - master].Acty_Cmplxty
    HAVING ((([Compromised TFN - master].Team_Id)=1743) AND (([Compromised TFN - master].Acty_Cntct_Typ_Nm)="REG Compromised TFN") AND (([Compromised TFN - master].Intractn_Sts)="Information Pending") AND (([Compromised TFN - master].Acty_Priority)="3-Medium") AND (([Compromised TFN - master].Acty_Cmplxty)="Low"))
    UNION
    SELECT Count([Compromised TFN - master].Team_Id) AS CountOfTeam_Id, [Compromised TFN - master].Acty_Priority, [Compromised TFN - master].Acty_Cmplxty
    FROM [Compromised TFN - master]
    GROUP BY [Compromised TFN - master].Team_Id, [Compromised TFN - master].Acty_Cntct_Typ_Nm, [Compromised TFN - master].Intractn_Sts, [Compromised TFN - master].Acty_Priority, [Compromised TFN - master].Acty_Cmplxty
    HAVING ((([Compromised TFN - master].Team_Id)=1743) AND (([Compromised TFN - master].Acty_Cntct_Typ_Nm)="REG Compromised TFN") AND (([Compromised TFN - master].Intractn_Sts)="Information Pending") AND (([Compromised TFN - master].Acty_Priority)="3-Medium") AND (([Compromised TFN - master].Acty_Cmplxty)="Medium"))


  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Anywhere you have this at the start of your select statement:

    Count([Compromised TFN - master].Team_Id) AS CountOfTeam_Id

    Use

    NZ(Count([Compromised TFN - master].Team_Id),0) AS CountOfTeam_Id

    The NZ function changes any null field to a defined value, in this case 0

  3. #3
    coach32 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Aug 2011
    Posts
    47

    Showing zero values in a Union Query

    Hi and thank-you. I replaced all with the statement you gave me and when I pressed Run the zero still didn't appear for the 2 fields that I knew it should. Here is how I typed it in:
    Code:
    SELECT NZ(Count([Compromised TFN - master].Team_Id),0) AS CountOfTeam_Id, [Compromised TFN - master].Acty_Priority, [Compromised TFN - master].Acty_Cmplxty
    FROM [Compromised TFN - master]
    GROUP BY [Compromised TFN - master].Team_Id, [Compromised TFN - master].Acty_Cntct_Typ_Nm, [Compromised TFN - master].Intractn_Sts, [Compromised TFN - master].Acty_Priority, [Compromised TFN - master].Acty_Cmplxty
    HAVING ((([Compromised TFN - master].Team_Id)=1743) AND (([Compromised TFN - master].Acty_Cntct_Typ_Nm)="REG Compromised TFN") AND (([Compromised TFN - master].Intractn_Sts)="Information Pending") AND (([Compromised TFN - master].Acty_Priority)="3-Medium") AND (([Compromised TFN - master].Acty_Cmplxty)="Low"))
    UNION ALL
    SELECT NZ(Count([Compromised TFN - master].Team_Id),0) AS CountOfTeam_Id, [Compromised TFN - master].Acty_Priority, [Compromised TFN - master].Acty_Cmplxty
    FROM [Compromised TFN - master]
    GROUP BY [Compromised TFN - master].Team_Id, [Compromised TFN - master].Acty_Cntct_Typ_Nm, [Compromised TFN - master].Intractn_Sts, [Compromised TFN - master].Acty_Priority, [Compromised TFN - master].Acty_Cmplxty
    HAVING ((([Compromised TFN - master].Team_Id)=1743) AND (([Compromised TFN - master].Acty_Cntct_Typ_Nm)="REG Compromised TFN") AND (([Compromised TFN - master].Intractn_Sts)="Information Pending") AND (([Compromised TFN - master].Acty_Priority)="3-Medium") AND (([Compromised TFN - master].Acty_Cmplxty)="Medium"))
    UNION ALL
    SELECT NZ(Count([Compromised TFN - master].Team_Id),0) AS CountOfTeam_Id, [Compromised TFN - master].Acty_Priority, [Compromised TFN - master].Acty_Cmplxty
    FROM [Compromised TFN - master]
    GROUP BY [Compromised TFN - master].Team_Id, [Compromised TFN - master].Acty_Cntct_Typ_Nm, [Compromised TFN - master].Intractn_Sts, [Compromised TFN - master].Acty_Priority, [Compromised TFN - master].Acty_Cmplxty
    HAVING ((([Compromised TFN - master].Team_Id)=1743) AND (([Compromised TFN - master].Acty_Cntct_Typ_Nm)="REG Compromised TFN") AND (([Compromised TFN - master].Intractn_Sts)="Information Pending") AND (([Compromised TFN - master].Acty_Priority)="3-Medium") AND (([Compromised TFN - master].Acty_Cmplxty)="High"))
    UNION ALL
    SELECT NZ(Count([Compromised TFN - master].Team_Id),0) AS CountOfTeam_Id, [Compromised TFN - master].Acty_Priority, [Compromised TFN - master].Acty_Cmplxty
    FROM [Compromised TFN - master]
    GROUP BY [Compromised TFN - master].Team_Id, [Compromised TFN - master].Acty_Cntct_Typ_Nm, [Compromised TFN - master].Intractn_Sts, [Compromised TFN - master].Acty_Priority, [Compromised TFN - master].Acty_Cmplxty
    HAVING ((([Compromised TFN - master].Team_Id)=1743) AND (([Compromised TFN - master].Acty_Cntct_Typ_Nm)="REG Compromised TFN") AND (([Compromised TFN - master].Intractn_Sts)="Information Pending") AND (([Compromised TFN - master].Acty_Priority)="4-Low") AND (([Compromised TFN - master].Acty_Cmplxty)="Low"))
    UNION ALL
    SELECT NZ(Count([Compromised TFN - master].Team_Id),0) AS CountOfTeam_Id, [Compromised TFN - master].Acty_Priority, [Compromised TFN - master].Acty_Cmplxty
    FROM [Compromised TFN - master]
    GROUP BY [Compromised TFN - master].Team_Id, [Compromised TFN - master].Acty_Cntct_Typ_Nm, [Compromised TFN - master].Intractn_Sts, [Compromised TFN - master].Acty_Priority, [Compromised TFN - master].Acty_Cmplxty
    HAVING ((([Compromised TFN - master].Team_Id)=1743) AND (([Compromised TFN - master].Acty_Cntct_Typ_Nm)="REG Compromised TFN") AND (([Compromised TFN - master].Intractn_Sts)="Information Pending") AND (([Compromised TFN - master].Acty_Priority)="4-Low") AND (([Compromised TFN - master].Acty_Cmplxty)="Medium"))
    UNION ALL
    SELECT NZ(Count([Compromised TFN - master].Team_Id),0) AS CountOfTeam_Id, [Compromised TFN - master].Acty_Priority, [Compromised TFN - master].Acty_Cmplxty
    FROM [Compromised TFN - master]
    GROUP BY [Compromised TFN - master].Team_Id, [Compromised TFN - master].Acty_Cntct_Typ_Nm, [Compromised TFN - master].Intractn_Sts, [Compromised TFN - master].Acty_Priority, [Compromised TFN - master].Acty_Cmplxty
    HAVING ((([Compromised TFN - master].Team_Id)=1743) AND (([Compromised TFN - master].Acty_Cntct_Typ_Nm)="REG Compromised TFN") AND (([Compromised TFN - master].Intractn_Sts)="Information Pending") AND (([Compromised TFN - master].Acty_Priority)="4-Low") AND (([Compromised TFN - master].Acty_Cmplxty)="High"))
    UNION ALL
    SELECT NZ(Count([Compromised TFN - master].Team_Id),0) AS CountOfTeam_Id, [Compromised TFN - master].Acty_Priority, [Compromised TFN - master].Acty_Cmplxty
    FROM [Compromised TFN - master]
    GROUP BY [Compromised TFN - master].Team_Id, [Compromised TFN - master].Acty_Cntct_Typ_Nm, [Compromised TFN - master].Intractn_Sts, [Compromised TFN - master].Acty_Priority, [Compromised TFN - master].Acty_Cmplxty
    HAVING ((([Compromised TFN - master].Team_Id)=1743) AND (([Compromised TFN - master].Acty_Cntct_Typ_Nm)="REG Compromised TFN") AND (([Compromised TFN - master].Intractn_Sts)="Information Pending") AND (([Compromised TFN - master].Acty_Priority)="2-High") AND (([Compromised TFN - master].Acty_Cmplxty)="Low"))
    UNION ALL
    SELECT NZ(Count([Compromised TFN - master].Team_Id),0) AS CountOfTeam_Id, [Compromised TFN - master].Acty_Priority, [Compromised TFN - master].Acty_Cmplxty
    FROM [Compromised TFN - master]
    GROUP BY [Compromised TFN - master].Team_Id, [Compromised TFN - master].Acty_Cntct_Typ_Nm, [Compromised TFN - master].Intractn_Sts, [Compromised TFN - master].Acty_Priority, [Compromised TFN - master].Acty_Cmplxty
    HAVING ((([Compromised TFN - master].Team_Id)=1743) AND (([Compromised TFN - master].Acty_Cntct_Typ_Nm)="REG Compromised TFN") AND (([Compromised TFN - master].Intractn_Sts)="Information Pending") AND (([Compromised TFN - master].Acty_Priority)="2-High") AND (([Compromised TFN - master].Acty_Cmplxty)="Medium"))
    UNION ALL SELECT NZ(Count([Compromised TFN - master].Team_Id),0) AS CountOfTeam_Id, [Compromised TFN - master].Acty_Priority, [Compromised TFN - master].Acty_Cmplxty
    FROM [Compromised TFN - master]
    GROUP BY [Compromised TFN - master].Team_Id, [Compromised TFN - master].Acty_Cntct_Typ_Nm, [Compromised TFN - master].Intractn_Sts, [Compromised TFN - master].Acty_Priority, [Compromised TFN - master].Acty_Cmplxty
    HAVING ((([Compromised TFN - master].Team_Id)=1743) AND (([Compromised TFN - master].Acty_Cntct_Typ_Nm)="REG Compromised TFN") AND (([Compromised TFN - master].Intractn_Sts)="Information Pending") AND (([Compromised TFN - master].Acty_Priority)="2-High") AND (([Compromised TFN - master].Acty_Cmplxty)="High"));

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    I have to admit that when I first looked at your query I just looked for why the 0's were not showing up in your query but on second look is there a reason you're not doing this in a non-union query?

    You're querying the same three fields from the same table you just have different sets of criteria

    Try this query:

    Code:
    SELECT NZ(Count([Compromised TFN - master].Team_Id),0) AS CountOfTeam_Id, [Compromised TFN - master].Acty_Priority, [Compromised TFN - master].Acty_Cmplxty
    FROM [Compromised TFN - master]
    GROUP BY [Compromised TFN - master].Acty_Priority, [Compromised TFN - master].Acty_Cmplxty, [Compromised TFN - master].Team_Id, [Compromised TFN - master].Acty_Cntct_Typ_Nm, [Compromised TFN - master].Intractn_Sts
    HAVING ((([Compromised TFN - master].Acty_Priority)="3-Medium") AND (([Compromised TFN - master].Acty_Cmplxty)="Low") AND (([Compromised TFN - master].Team_Id)=1743) AND (([Compromised TFN - master].Acty_Cntct_Typ_Nm)="REG Compromised TFN") AND (([Compromised TFN - master].Intractn_Sts)="Information Pending")) OR ((([Compromised TFN - master].Acty_Priority)="3-medium" Or ([Compromised TFN - master].Acty_Priority)="4-low" Or ([Compromised TFN - master].Acty_Priority)="2-high") AND (([Compromised TFN - master].Acty_Cmplxty)="Medium" Or ([Compromised TFN - master].Acty_Cmplxty)="high" Or ([Compromised TFN - master].Acty_Cmplxty)="low") AND (([Compromised TFN - master].Team_Id)=1743) AND (([Compromised TFN - master].Acty_Cntct_Typ_Nm)="REG Compromised TFN") AND (([Compromised TFN - master].Intractn_Sts)="Information Pending")) OR ((([Compromised TFN - master].Acty_Priority)="2-high"));

  5. #5
    coach32 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Aug 2011
    Posts
    47
    The reason I tried a Union Query is that I thought it would reduce the number of queries I had to create. Your solution however has made me realise that it is not necessary for as many queries as I thought. I have taken note of your suggested Select query and I thank-you; however, when I run the query I get the values for 4 of the criteria, but there is another 5 criteria that have '0' on this particular occasion, but the zero's do not show. Here is my code
    Code:
    SELECT NZ(Count([Compromised TFN - master].[Team_Id]),0) AS CountOfTeam_Id, [Compromised TFN - master].Acty_Priority, [Compromised TFN - master].Acty_Cmplxty, [Compromised TFN - master].Team_Id, [Compromised TFN - master].Acty_Cntct_Typ_Nm, [Compromised TFN - master].Intractn_Sts
    FROM [Compromised TFN - master]
    GROUP BY [Compromised TFN - master].Acty_Priority, [Compromised TFN - master].Acty_Cmplxty, [Compromised TFN - master].Team_Id, [Compromised TFN - master].Acty_Cntct_Typ_Nm, [Compromised TFN - master].Intractn_Sts
    HAVING ((([Compromised TFN - master].Acty_Priority)="3-Medium") AND (([Compromised TFN - master].Acty_Cmplxty)="Low") AND (([Compromised TFN - master].Team_Id)=1743) AND (([Compromised TFN - master].Acty_Cntct_Typ_Nm)="REG Compromised TFN") AND (([Compromised TFN - master].Intractn_Sts)="For Approval")) OR ((([Compromised TFN - master].Acty_Priority)="3-Medium") AND (([Compromised TFN - master].Acty_Cmplxty)="Medium") AND (([Compromised TFN - master].Team_Id)=1743) AND (([Compromised TFN - master].Acty_Cntct_Typ_Nm)="REG Compromised TFN") AND (([Compromised TFN - master].Intractn_Sts)="For Approval")) OR ((([Compromised TFN - master].Acty_Priority)="3-Medium") AND (([Compromised TFN - master].Acty_Cmplxty)="High") AND (([Compromised TFN - master].Team_Id)=1743) AND (([Compromised TFN - master].Acty_Cntct_Typ_Nm)="REG Compromised TFN") AND (([Compromised TFN - master].Intractn_Sts)="For Approval")) OR ((([Compromised TFN - master].Acty_Priority)="4-Low") AND (([Compromised TFN - master].Acty_Cmplxty)="Low") AND (([Compromised TFN - master].Team_Id)=1743) AND (([Compromised TFN - master].Acty_Cntct_Typ_Nm)="REG Compromised TFN") AND (([Compromised TFN - master].Intractn_Sts)="For Approval")) OR ((([Compromised TFN - master].Acty_Priority)="4-Low") AND (([Compromised TFN - master].Acty_Cmplxty)="Medium") AND (([Compromised TFN - master].Team_Id)=1743) AND (([Compromised TFN - master].Acty_Cntct_Typ_Nm)="REG Compromised TFN") AND (([Compromised TFN - master].Intractn_Sts)="For Approval")) OR ((([Compromised TFN - master].Acty_Priority)="4-Low") AND (([Compromised TFN - master].Acty_Cmplxty)="High") AND (([Compromised TFN - master].Team_Id)=1743) AND (([Compromised TFN - master].Acty_Cntct_Typ_Nm)="REG Compromised TFN") AND (([Compromised TFN - master].Intractn_Sts)="For Approval")) OR ((([Compromised TFN - master].Acty_Priority)="2-High") AND (([Compromised TFN - master].Acty_Cmplxty)="Low") AND (([Compromised TFN - master].Team_Id)=1743) AND (([Compromised TFN - master].Acty_Cntct_Typ_Nm)="REG Compromised TFN") AND (([Compromised TFN - master].Intractn_Sts)="For Approval")) OR ((([Compromised TFN - master].Acty_Priority)="2-High") AND (([Compromised TFN - master].Acty_Cmplxty)="Medium") AND (([Compromised TFN - master].Team_Id)=1743) AND (([Compromised TFN - master].Acty_Cntct_Typ_Nm)="REG Compromised TFN") AND (([Compromised TFN - master].Intractn_Sts)="For Approval")) OR ((([Compromised TFN - master].Acty_Priority)="2-High") AND (([Compromised TFN - master].Acty_Cmplxty)="High") AND (([Compromised TFN - master].Team_Id)=1743) AND (([Compromised TFN - master].Acty_Cntct_Typ_Nm)="REG Compromised TFN") AND (([Compromised TFN - master].Intractn_Sts)="For Approval"));
    The only difference here is the Status is now "For Approval". Thank-you for your patience and guidance, I'm just about at the end of my wits trying to get it to work.

  6. #6
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    You changed my SQL but this is exactly the same query with just a different criteria:

    Code:
    SELECT NZ(Count([Compromised TFN - master].[Team_Id]),0) AS CountOfTeam_Id, [Compromised TFN - master].Acty_Priority, [Compromised TFN - master].Acty_Cmplxty, [Compromised TFN - master].Team_Id, [Compromised TFN - master].Acty_Cntct_Typ_Nm, [Compromised TFN - master].Intractn_Sts
    FROM [Compromised TFN - master]
    GROUP BY [Compromised TFN - master].Acty_Priority, [Compromised TFN - master].Acty_Cmplxty, [Compromised TFN - master].Team_Id, [Compromised TFN - master].Acty_Cntct_Typ_Nm, [Compromised TFN - master].Intractn_Sts
    HAVING ((([Compromised TFN - master].Acty_Priority)="3-Medium" Or ([Compromised TFN - master].Acty_Priority)="4-Low" Or ([Compromised TFN - master].Acty_Priority)="2-High") AND (([Compromised TFN - master].Acty_Cmplxty)="Low" Or ([Compromised TFN - master].Acty_Cmplxty)="Medium" Or ([Compromised TFN - master].Acty_Cmplxty)="High") AND (([Compromised TFN - master].Team_Id)=1743) AND (([Compromised TFN - master].Acty_Cntct_Typ_Nm)="REG Compromised TFN") AND (([Compromised TFN - master].Intractn_Sts)="For Approval"));

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Please help me out with union query
    By radicrains in forum Access
    Replies: 1
    Last Post: 10-29-2010, 01:48 AM
  2. Sql query not showing all the values
    By usr123 in forum Access
    Replies: 0
    Last Post: 02-24-2010, 07:32 AM
  3. UNION two tables with same primary key values
    By carillonator in forum Queries
    Replies: 1
    Last Post: 02-02-2010, 08:54 PM
  4. Need help with a Union Query
    By jdowdy in forum Access
    Replies: 1
    Last Post: 10-13-2009, 05:24 PM
  5. Null Values not showing up in a Query
    By Valli in forum Queries
    Replies: 0
    Last Post: 01-04-2006, 03:53 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums