Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    No Zero in query (Access 97 Win2k)

    Hello Again

    I'm trying to work out the percentages from a count of certain results. I've used two queries to count the number of occasions that a record is 'won' or 'lost' by month, then put the queries into a third to allow me to set-up a calculated field. The thing works OK, right up until the point that I arrange for one of the values not to be present in any given month. The query gives me the number of occurrences where one of the values is present, and a blank for month where the value wasn't present.

    Here is the SQL for the query in question, I'm using the Max option as the count returns some really weird results and I couldn't see why.

    SELECT percentwon.Month, Max(percentwon.CountOfwonlost) AS MaxOfCountOfwonlost, Max(percentlost.CountOfwonlost) AS MaxOfCountOfwonlost1, Max([percentwon]![countofwonlost]+[percentlost]![countofwonlost]) AS sum
    FROM percentlost RIGHT JOIN percentwon ON percentlost.Month = percentwon.Month
    GROUP BY percentwon.Month;


    I vaguely remember that there are different join options available, but not offered on the Access pop-up menu, any suggestions as to what they are so I can try them in the SQL window?

    Thanks for you time

    Ian

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: No Zero in query (Access 97 Win2k)

    Your query has a right join of percentlost and percentwon, i.e. all records from percentwon are returned, regardless of whether there is a matching record in percentlost. If you make it into a left join, you get all records from percentlost. AFAIK, Access SQL doesn't have a two-sided outer join.

    If you want to return a record for each month that contains a record for percentwon or percentlost (or both), you can start by creating a union query

    SELECT DISTINCT Month FROM percentlost
    UNION
    SELECT DISTINCT Month FROM percentwon

    and then create a new query in which you have left joins from this union query to percentlost and percentwon.

    If you want to return a record too for months that have no entry in either percentlost or percentwon, you can create a table of month values and use that instead of the union query.

  3. #3
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: No Zero in query (Access 97 Win2k)

    Hans

    Thanks for the reply. I didn't make my problem very clear I'm afraid. The problem occurs where there is a month with, for example, a won value and no lost value. This will return a record for the won but a blank field for the lost, I was hoping for a zero. The problem with the blank field is that when I try to add the 'won' count to the 'lost' count I get a blank field if either 'won' or 'lost' is a blank field.

    For now I've got around the problem by using a third value 'ongoing', this will work for the system I'm working on at the minute, but I'd like to know if there is a 'proper' solution to my difficulty. I've checked out the web site with the working days calculation code on it, but they don't seem to have a solution, or I'm asking the search engine the wrong question, same as on here, loads of irrelevant answers or none at all :-)

    Again, thanks for the suggestion, I've learnt how to link queries in SQL so that's a plus on this morning.

    Ian

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

    Re: No Zero in query (Access 97 Win2k)

    If you try to add (or subract) a Null to a value, the result will always be Null. You have to use the Nz function to provide a substitute value like this:

    Nz([percentlost].[countofwonlost],0)

    You'll need to wrap any value that might be null in a month in an Nz function to avoid the problem you're having.
    Charlotte

  5. #5
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: No Zero in query (Access 97 Win2k)

    Charlotte

    Thanks, the advice worked fine, I now have '0' showing in the fields that were previously null. Unfortunately, the calculation field is not adding the two numbers together, it's combining them, for example won = 1 lost = 2 calculation field is set to won + lost the result is 12. Do I need to use the 'format' command to define the Nz fields as numeric? The original data being counted is text, but before I used the Nz function the counts added up correctly where there were two values.

    I'm living up to my user name on this one....

    Thanks again for the help so far.

    Ian

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

    Re: No Zero in query (Access 97 Win2k)

    Do you mean the calculated field in the query is returning a concatenation of values rather than a sum? The query should coerce the results of the Nz function into numbers ... unless you put quotes around the zero instead of just putting a numeric zero in as the default. Could that be what happened? You can use a numeric conversion like CLng() or even the Val() function to force them into numbers but it shouldn't be necessary, and Format will turn them into strings for certain.

    It's a bit hard to tell what might have gone wrong without being able to see exactly what you're doing. Could you post your revised SQL for the query? Then maybe someone can untangle it. A count is numeric regardless of what it counts.
    Charlotte

  7. #7
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: No Zero in query (Access 97 Win2k)

    Charlotte

    Thanks for the reply, just checked my dictionary, and yes, the query is concatenating (sp??) the two values. Here's the SQL for the query in question, it has thrown me as I already totally believed that any count would be a numeric value, from experience. To suddenly find that Access has the ability to concatenate two values, without me having to learn a lot of SQL or VBA is rather surprising <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    SELECT percentwon.Month, percentwon.Year, Nz([percentlost]![CountOfwonlost],0) AS Lost, Nz([percentwon]![countofwonlost],0) AS Won, [Lost]+[Won] AS Sum, [won]/[Sum] AS Percent_Won, [lost]/[Sum] AS [Percent Lost]
    FROM percentwon LEFT JOIN percentlost ON percentwon.Month = percentlost.Month
    ORDER BY percentwon.Month, percentwon.Year;

    I'm going to have a 'play' with the functions you suggested, just to see what they can do for me, and find out how to make them work with the Nz function.

    Is there a full list of functions available from the Access help, with a brief overview? I seem to remember seeing something like this, but while I've been looking for it recently can't find it. I ask as most of my 'problems' have simple solutions, once some one who knows points me to a certain fuinction.

    Thanks for the help so far.

    Ian

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

    Re: No Zero in query (Access 97 Win2k)

    Don't use Sum as an alias in your query. Sum is an aggregation operator and you're likely to confuse the query engine by using it as an alias ... assuming it doesn't throw error messages at you when you try. Try this instead.

    SELECT percentwon.Month, percentwon.Year, percentlost]![CountOfwonlost] AS Lost, percentwon]![countofwonlost AS Won, Nz([Lost],0)+Nz([Won],0) AS Total, Nz([won],0)/Nz([Total],0) AS Percent_Won, Nz([lost],0)/Nz([Total],0) AS [Percent Lost]
    FROM percentwon LEFT JOIN percentlost ON percentwon.Month = percentlost.Month
    ORDER BY percentwon.Month, percentwon.Year;
    Charlotte

  9. #9
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: No Zero in query (Access 97 Win2k)

    Hello All

    Sorted it out, tried the CDbl variant on the CLng function Charlotte suggested, wrapped it around the Nz function for the two counts, query now appears to be working fine (just got to count a few thousand records to be sure <img src=/S/smile.gif border=0 alt=smile width=15 height=15> ).

    Here's the final working SQL in case it explains anything to some one who's brighter than I am.

    SELECT percentwon.Month, percentwon.Year, Max(CDbl(Nz([percentlost]![CountOfwonlost],0))) AS Lost, Max(CDbl(Nz([percentwon]![countofwonlost],0))) AS Won, Max(CDbl(Nz([percentongoing]![totalongoing]))) AS [Total Ongoing], [Lost]+[Won]+[total ongoing] AS Total, [won]/[total] AS Percent_Won, [lost]/[total] AS [Percent Lost], [total ongoing]/[total] AS [Percent Ongoing]
    FROM percentongoing RIGHT JOIN (percentwon LEFT JOIN percentlost ON percentwon.Month = percentlost.Month) ON percentongoing.Month = percentwon.Month
    GROUP BY percentwon.Month, percentwon.Year
    ORDER BY percentwon.Year, percentwon.Month;


    Thanks again for the help and support.

    Ian

Posting Permissions

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