How to combine 2 queries?

[nst]

I have the following 2 queries:

SELECT DISTINCTROW tbl_members.surname, Count(tbl_years.year) AS CountOfyear
FROM tbl_members INNER JOIN (tbl_years INNER JOIN tbl_subscriptions ON (tbl_years.ID_year = tbl_subscriptions.ID_year) AND (tbl_years.ID_year = tbl_subscriptions.ID_year)) ON tbl_members.ID_member = tbl_subscriptions.ID_member
GROUP BY tbl_subscriptions.subscription_fee, tbl_members.surname
HAVING (((tbl_subscriptions.subscription_fee)=0));

This query displays a list with the surname of the member and the Count of the Years he/she did not pay the annual subscription, hence where subscription_fee = 0

TRANSFORM Sum(tbl_subscriptions.subscription_fee) AS SumOfsubscription_fee
SELECT tbl_members.surname, tbl_members.name, tbl_members.mobilephone
FROM tbl_members INNER JOIN (tbl_years INNER JOIN tbl_subscriptions ON (tbl_years.ID_year = tbl_subscriptions.ID_year) AND (tbl_years.ID_year = tbl_subscriptions.ID_year)) ON tbl_members.ID_member = tbl_subscriptions.ID_member
WHERE (((tbl_years.year)>Year(Date())-"6"))
GROUP BY tbl_members.surname, tbl_members.name, tbl_members.mobilephone
PIVOT tbl_years.year;

This query displays a list with the surname, name, mobile phone of the member along with the money he/she paid for the last 5 years as you can see from

WHERE (((tbl_years.year)>Year(Date())-"6"))

My question is: is it possible to combine those 2 lists and have one where all of the following columns will be listed?
Surname, Name, Mobilephone, Count of years with 0 payment, a column for each year of the last 5

Thanks in advance.

 

[macca the hacke]

Yes - use a union query. This will be something along the lines of:

SELECT [query1].[surname], [query1].[name], [query1].[mobilephone],[query1].[count of years with 0 payment], [query1].[year1], [query1].[year2],[query1].[year3],[query1].[year4],[query1].[year5] from [query1]

UNION SELECT

[query2].[surname], [query2].[name], [query2].[mobilephone],[query2].[count of years with 0 payment], [query2].[year1], [query2].[year2],[query2].[year3],[query2].[year4],[query2].[year5] from [query2];

Obviously you will need to amend the names of the queries and the fields