Oddity in nested queries

[RichardMGreen]

Hi all

I've got some nested queries that should work (famous last words).
All the sub-queries return the correct values (in this case 8 records for the month), but this is not showing on the final query which outputs an amalgamated total.

The SQL for the final query is:-

SELECT qry_Monthly_Client_Referrals_Total.CountOfClientId AS Monthly_Referrals, qry_Monthly_Client_Successful_Referrals_Total.CountOfClientId AS Monthly_Successful_Referrals, qry_YTD_Client_Referrals_Total.CountOfClientId AS YTD_Referrals, qry_YTD_Client_Successful_Referrals_Total.CountOfClientId AS YTD_Successful_Referrals
FROM qry_Monthly_Client_Successful_Referrals_Total, qry_Monthly_Client_Referrals_Total, qry_YTD_Client_Referrals_Total, qry_YTD_Client_Successful_Referrals_Total;

As you can see, there are no links (and none are necessary as far as I can make out) as it is just displaying totals already calculated in previous queries.

Can anyone make any suggestions as to why it's not pulliong through any data?

 

[CharlesChuckieCharles]

Should this Be a Union query ( or maybe a step below thisshould be a union query to allow for the final output)

 

[RichardMGreen]

Not as far as I can see. I've built other queries using the same method and they all work fine.
It's just this one that's not pulling through the final data.

 

[RichardMGreen]

Might have found the issue, now just need a solution.

It looks like it's only returning blanks when
qry_Monthly_Client_Successful_Referrals_Total.CountOfClientId AS Monthly_Successful_Referrals
is blank.
I thought it would have been zero or null but obviously not.
I'll try wrapping it in an iif/isnull statement and see how I get on.

::edit::
Well, that didn't work.

 

[RichardMGreen]

Right, I've narrowed it down to this query (here's the SQL).

SELECT qry_Monthly_Client_Successful_Referalls.Month_Number, Count(qry_Monthly_Client_Successful_Referalls.ClientId) AS CountOfClientId
FROM qry_Monthly_Client_Successful_Referalls
GROUP BY qry_Monthly_Client_Successful_Referalls.Month_Number;

What I want to happen is that if qry_Monthly_Client_Successful_Referalls.ClientId is null then I get zero not a null return.
I've tried wrapping it in an iif/isnull statement but then I get error messages about aggregate functions.

Anyone any ideas?

 

[RichardMGreen]

Right, solved it but there has to be a better way.
I've created a calendar table that lists all the months with month numbers and used that as a master table (left outer join) in the query.
I'm then restricting that to the month I want so I end with a zero (where necessary) that pulls through to the final query.

I don't know if there's a better way (I'm open to suggestions) but this works.
I still don't understand why it wiped out the other queries in the final one.

 

[xenou]

Your first post lists a cartesian join of four queries. The last post doesn't seem remotely the same. I'm confused.

As far as "wrapping in isnull/nz goes", I *think* it would work this way:

SELECT 
    t.Month_Number, 
    Count([COLOR="RoyalBlue"]NZ(t.ClientId,0)[/COLOR]) AS CountOfClientId
FROM 
    qry_Monthly_Client_Successful_Referalls t
GROUP 
    t.Month_Number;

 

[RichardMGreen]

The first post listed the outputs (or lack of them) from 4 other queries that were built on other queries and so on. It was simply a way to get all the data I needed into one output. The last bit I posted was my workaround for the query that was causing the problem.

Thanks for the SQL, can I drop that straight in to my Access query if I view SQL in design mode?

 

[xenou]

Yes, I think you can drop it in in SQL view. You can use the same expression in Query Builder as an Expression:

SQL:
Count(NZ(t.ClientId,0)) AS CountOfClientId

Query Builder:
CountOfClientID: Count(NZ(t.ClientID,0))

But you need to select Expression rather than Count below in the field type.

 

[RichardMGreen]

That's got it sorted, thanks.