Queries not working when joined

[RichardMGreen]

Hi all

I have two queries that work correctly when run separately.
When I try to use them as subqueries to join the two together, they stop working.
Here's the SQL:-
Monthly

SELECT Month([contactDatetime]) AS Month_Number, Count(ext_Contacts.clientId) AS CountOfclientId, ext_Contacts.formOfContact, Year([contactDatetime]) AS [Year]
FROM ext_Contacts
GROUP BY Month([contactDatetime]), ext_Contacts.formOfContact, Year([contactDatetime])
HAVING (((Month([contactDatetime]))=[Month Number]) AND ((ext_Contacts.formOfContact)<>"Telephone" And (ext_Contacts.formOfContact)<>"Email" And (ext_Contacts.formOfContact)<>"Letter") AND ((Year([contactDatetime]))=[Year]));

This returns zero results for May which is expected.


Year-to-Date

SELECT Count(ext_Contacts.clientId) AS CountOfclientId, ext_Contacts.formOfContact, Year([contactDatetime]) AS [Year]
FROM ext_Contacts
GROUP BY ext_Contacts.formOfContact, Year([contactDatetime])
HAVING (((ext_Contacts.formOfContact)<>"Telephone" And (ext_Contacts.formOfContact)<>"Email" And (ext_Contacts.formOfContact)<>"Letter") AND ((Year([contactDatetime]))=[Year]));

This returns 1 result for this year which is correct.


Now, when I amalgamate the two queries (which gives the following SQL)

SELECT qry_Monthly_General_F2F.CountOfclientId, qry_YTD_General_F2F.CountOfclientId
FROM qry_Monthly_General_F2F, qry_YTD_General_F2F;

I get no results. The 1 from the year-to-date section has disappeared.

I know the two queries are not linked but I haven't linked other queries of similar design and they work OK.

Anyone any ideas?

 

[CharlesChuckieCharles]

Shouldn't you use a UNION Query?

 

[RichardMGreen]

Can't use a UNION unfortunately, the queries have slightly different outputs and a different number of columns.

 

[CharlesChuckieCharles]

Why not change the month one to accept start month and end month, and ditch the year to date

(((Month([contactDatetime]))=>[START Month Number]) AND (((Month([contactDatetime]))<=[END Month Number])

 

[chewi]

Can't use a UNION unfortunately, the queries have slightly different outputs and a different number of columns.

I have overcome this in UNON queries by creating Null or "" fields in one of the queries so they DO match.

eg I have a tbllive and an tblarchive , the archive tbl has a field tblarchive.archive_date field that the live does not, otherwise same structure.

A query on both is made with a Union of 2 similar queries using "" for the archive_date for the tbllive query, then a JOIN on the now matching queries.

 

[RichardMGreen]

Thanks for the input folks, it's now sorted.