Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Totals Query (97)

    I have three totals query that I use in another query. I use the aggregate function count. I use a calculation in the query. The problem is if one of the queries has nothing to count it leaves that record out. I would like it to give me a 0 so I can use it in the calculation. Any suggestions?

  2. #2
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Totals Query (97)

    Take a look at the NZ function in the online help. When using it in queries you have to be explicit with the valueifnull argument.

  3. #3
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Totals Query (97)

    Also look at the options for joining objects in your queries. To do so, double-click the join line between the field lists for the tables or queries. Look at the Join Properties dialog box to see the join options.

    If you are using a natural join, records which do not link to the other object(s) will be omitted. You may need to use an outer join to select all the records.

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Totals Query (97)

    Linda,

    Does this thread relate to this one and this one? If so, it would be better to work through your question in a single thread rather than asking essentially the same question over and over again, which is also a violation of Lounge <!rule=16>Rule 16<!/rule>. You asked virtually the same question in this post so I assume this is all the same.

    In any event, you can't force a non-existent record to return a zero, as I believe was pointed out in another thread. At best, you can use an Nz() function in the calculation to handle nulls, but you can't use it to create a zero without any records behind it.
    Charlotte

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Totals Query (97)

    I didn't realize I violated rule 16. Each question I posted pertained to a different situation. I only have one database that I maintain so the scenarios might sound the same. The first Totals Query question was answered. I had a different question pertaining to a totals query. I posed another question in the first post but I figured it wasn't going to be answered buried in a post that was already answered. Sorry if I broke any rules.

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Totals Query (97)

    It isn't a criminal offense, just a suggestion that you be careful not to start a bunch of threads on the same question. <img src=/S/smile.gif border=0 alt=smile width=15 height=15> Having information split across multiple threads makes it difficult for anyone else to find the answers because you may have entirely different responses in different threads or the same people might wind up posting the same answers in multiple threads.

    If a thread seems stagnant, post a new reply to your original post asking for more help. That will turn on the new post indicator so that anyone looking at the index will see that a new post has been made. You can also change the subject of the new post to indicate that you need more help or to be more specific about the request.
    Charlotte

  7. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    More Help

    I have the the trhee total queries in another query. One query is for Beginning Inventory, one for Jobs Assigned, one for jobs completed. The last field in the query that contains the 3 queries is a calculated field that gives the ending inventory - (after adding in jobs assigned and subtracting jobs that are complete.) When one of the total queries is null, it doesn't put anything in the field so the calculation can't give me an ending inventory. I don't know how to use the function Nz. I was thinking of using it some way in the calculated field of the query. This is the calculation that I am using in the query:

    EndTot: ([Forcrosstabqry]![CountOfGroupNumber]+[forcrosstabqrycountadded]![CountOfGroupNumber])-([forcrosstabqryjobscompleted]![CountOfGroupNumber])

  8. #8
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: More Help

    I believe the proper usage in this example would be:

    EndTot: (Nz([Forcrosstabqry]![CountOfGroupNumber],0)+Nz([forcrosstabqrycountadded]![CountOfGroupNumber],0))-(Nz([forcrosstabqryjobscompleted]![CountOfGroupNumber],0))

  9. #9
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: More Help

    Thank you, thank you, thank you. It works perfectly.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •