No Zero in query (Access 97 Win2k)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

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

Reply | Quote

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;

Reply | Quote

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

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

Reply | Quote