Return a result when no records are found?

[XelaIrodavlas]

Hi all,

Hopefully this isn't too difficult a question but experience tells me everyone that comes here is a genius... My question is, does anyone know of a way to build something into a sub-query that says 'if no records are found, return '0' or some other string'?

Otherwise is there a way to make a master query ignore sub-queries if they return no records?

Allow me to explain in more detail:

I have a series of sub-queries, most of these take the sum of several fields from a number of different tables, and I have a main query which combines all of these, to be used as the basis of a summary report.

These queries aren't a problem, but I have a few other essential queries which take the modal (most common....) entry for fields which aren't numerical. So I can't use the sum function.

Now, if all the sub-queries are working then so does the main query, however if one of them fails to find a result, then none of them show up in the main query. I don't know why.

The issue is that depending on the date range selected, some of the tables targeted by the sub-queries don't have any records at all, so when they are run they return nothing. The sum queries can handle this since they just return 0, but those searching for modal records just find nothing (not 0's and not null fields, just blank across all rows).​

So any ideas?

Here's an example of my sql statement for the modal queries. I pieced it together with the help of various online forums including this one (my obvious favorite)

SELECT TOP 1 Trends.Trend AS ModeTboxTalk, "1" AS [Key]

FROM Trends INNER JOIN [Toolbox Talks] ON Trends.TrendID = [Toolbox Talks].TrendID

GROUP BY Trends.Trend, [Toolbox Talks].TrendID, [Toolbox Talks].TalkDate

HAVING ((([Toolbox Talks].TalkDate)<=[Forms]![WeeklyReportSelect]![WeekBox] And ([Toolbox Talks].TalkDate)>[Forms]![WeeklyReportSelect]![WeekBox]-7) AND (([Toolbox Talks].SiteID)=[Forms]![WeeklyReportSelect]![SiteBox]))

ORDER BY Count([Toolbox Talks].TrendID) DESC;

- FYI the weekly select form is where users select the week and site they want to report against. So it would be really peachy if I could tell the above to say something like 'no trend this week' if indeed there were no records.


Thanks in advance for any ideas!

 

[CJ_London]

If nothing is found, your subquery returns a null value (not 0 or a zero length string (which is not a null). So a calcualtion which includes a null will also return null.

To resolve this wrap your subqueries with Nz i.e.

Nz((YourSubquery),0) + Nz((AnotherSubquery),0)

Note Nz should provide 0 or a zero length string depending on the context in which it is used - so the + in the above says context is numeric so really, you do not have to declare the 0 - i.e. nz((YourSubquery)) - however better to declare - you might want it to say nz((Yoursubquery),"Nothing Found") for example.

 

[XelaIrodavlas]

Thanks for the tip CJ but it's still not helping , the numeric fields have no issue but the Mode queries still need to return something, else they bomb the whole thing.

I see your point with Nz, Correct me if I'm wrong but the NZ function only seems to replace individual field results, so doesn't work for non-existent records because there's nothing to replace. So this suggests it should be used on the main query, like you suggested, but the query still returned nothing.

SELECT Nz([CountAir],0) AS Expr1, Nz([CountHONM],0) AS Expr2, Nz([CountTboxTalks],0) AS Expr3, Nz([ModeTboxTalk],0) AS Expr4
FROM CountAIR, CountHONM, CountToolboxTalks, ModeToolboxTrend;

* NB in this example only the mode Tboxtalk query fails to find any results on its own, but because of that nothing comes up in the combined query.

I then tried to use right joins to get the null queries to be ignored, but it didn't help. It almost seems like as soon as I add the failing query into the FROM clause it messes everything up, even if I don't select anything from that query.

I think the issue could be resolved if I come up with a better SQL for the mode queries, force it to produce a 0 by default, any tips on how to do that?

I'm gonna have to play around a bit more...

 

[CJ_London]

Is the query you posted in your first post a sub query or the one you are trying to resolve?

Perhaps you can post all of the eql to get a better understanding

 

[XelaIrodavlas]

Sure thing, and yes the original post was one of the subqueries, it counts the number of entries for each possible answer, lists them in descending order and then trims off the top 1 (hence giving the most common answer)

SELECT TOP 1 Trends.Trend AS TBoxMode

FROM Trends INNER JOIN [Toolbox Talks] ON Trends.TrendID = [Toolbox Talks].TrendID

GROUP BY [Toolbox Talks].TalkDate, [Toolbox Talks].SiteID, Trends.Trend

HAVING ((([Toolbox Talks].TalkDate)<=[Forms]![WeeklyReportSelect]![WeekBox] And ([Toolbox Talks].TalkDate)>[Forms]![WeeklyReportSelect]![WeekBox]-7) AND (([Toolbox Talks].SiteID)=[Forms]![WeeklyReportSelect]![SiteBox]))

ORDER BY Count([Toolbox Talks].TrendID) DESC;

just for clarity the other type of query is a simple count:

SELECT Count([O2O Reports].DiscussionID) AS CountO2O

FROM [O2O Reports]

HAVING ((([O2O Reports].DateRecorded)<=[Forms]![WeeklyReportSelect]![WeekBox] And ([O2O Reports].DateRecorded)>[Forms]![WeeklyReportSelect]![WeekBox]-7) AND (([O2O Reports].SiteID)=[Forms]![WeeklyReportSelect]![SiteBox]));

The 'Main' query looks like this

SELECT TOP 1 CountAIR.CountAIR, CountHONM.CountHONM, CountOpenAIR.CountOpenAir, CountToolboxTalks.CountTboxTalks, ModeO2OTrend.ModeO2O, ModeToolboxTrend.ModeTboxTalk

FROM CountAIR, CountHONM, CountOpenAIR, CountToolboxTalks, ModeO2OTrend, ModeToolboxTrend

GROUP BY CountAIR.CountAIR, CountHONM.CountHONM, CountOpenAIR.CountOpenAir, CountToolboxTalks.CountTboxTalks, ModeO2OTrend.ModeO2O, ModeToolboxTrend.ModeTboxTalk;

So it should just display the results as found by each individual query, but as I mentioned, this only works when all of the individuals find a result.

I think the easiest solution would be to solve the first sub-query here (modal trends), so that it returns a 0 if no records were found. But is this possible?

Thanks

 

[CJ_London]

I'm not quite sure that your structure is the best way of doing things - two of the queries do not seem to relate to your main query - however to demonstrate the use of nz, it would be like this. Not sure what the benefit of Top 1 and grouping is, each 'subquery' should only bring through 1 row.

SELECT TOP 1 nz(CountAIR.CountAIR,0), nz(CountHONM.CountHONM,0), nz(ntOpenAIR.CountOpenAir,0), nz(CountToolboxTalks.CountTboxTalks,0),nz(ModeO2OTrend.ModeO2O,0), nz(ModeToolboxTrend.ModeTboxTalk,0)

FROM CountAIR, CountHONM, CountOpenAIR, CountToolboxTalks, ModeO2OTrend, ModeToolboxTrend

GROUP BY  nz(CountAIR.CountAIR,0), nz(CountHONM.CountHONM,0), nz(ntOpenAIR.CountOpenAir,0), nz(CountToolboxTalks.CountTboxTalks,0),nz(ModeO2OTrend.ModeO2O,0), nz(ModeToolboxTrend.ModeTboxTalk,0)

;

 

[RainLover]

so doesn't work for non-existent records because there's nothing to replace

This is true. What would you expect.

You can create a successful calculation on something that does not exist. However you can enclose the incorrect calculation with the Nz Function to return "0" or some other result.