Joining Tables

[Supermansteel]

I am joining these 2 tables together in Access 2003 and can't figure out the exact way of writing this script......Can anyone help?

I have the following SQL:

SELECT PL_Input.Date_ID,Count(PL_Input.[1]+PL_Input.[2]+PL_Input.[3]+PL_Input.[4]+PL_Input.[5]) AS [TO]

FROM Country INNER JOIN PL_Input ON Country.Country_ID = PL_Input.Country_ID

WHERE ((([PL_Input].[1] & "" & [PL_Input].[2] & "" & [PL_Input].[3] & "" & [PL_Input].[4] & "" & [PL_Input].[5]) Like "*2*"))

GROUP BY PL_Input.Date_ID


UNION ALL


SELECT RE_Input.Date_ID, Count(([Six] & "" & [Seven] & "" & [Eight] & "" & [Nine])) AS [TO]

FROM Country INNER JOIN RE_Input ON Country.Country_ID = RE_Input.Country_ID

WHERE ((([Six] & "" & [Seven] & "" & [Eight] & "" & [Nine]) Like "*2*"))

GROUP BY RE_Input.Date_ID;



I am basically trying to count how many TO (total Occurences) where it is equal to 2. I have a form that I built and has check boxes for Pass/Fail that gets inputed into 1,2,3,4,5 and Six,Seven,Eight,Nine for 2 Different Products RE (Real Estate) and PL (Personal Loan). How ever I am having a hard time joining the 2 tables together properly to reflect how many TO happened for the month. The above join was the closest I came to joining them and it does give me the correct results, however, it lists them as such.

Date_ID TO
11/1/2007 1
12/1/2007 4
1/1/2008 1
11/1/2007 1
12/1/2007 3
1/1/2008 1

Is there a way to group by the Date_ID to reflect only one date?

Or is there a better way of joining these tables together?

[mwasif]

Moved to Access Forum.

[MMcCarthy]

This should work. However, once you paste it into Access and run it be careful not to edit it or the compiler will probably screw it up trying to impose brackets on it.

SELECT tmpQry.Date_ID, tmpQry.[TO]
FROM (
SELECT PL_Input.Date_ID,
Count(PL_Input.[1]+PL_Input.[2]+PL_Input.[3]+PL_Input.[4]+PL_Input.[5]) AS [TO]
FROM PL_Input 
WHERE ((([PL_Input].[1] & "" & [PL_Input].[2] & "" & [PL_Input].[3] & "" & [PL_Input].[4] & "" & [PL_Input].[5]) Like "*2*"))
GROUP BY PL_Input.Date_ID
UNION ALL
SELECT RE_Input.Date_ID, 
Count(([Six] & "" & [Seven] & "" & [Eight] & "" & [Nine])) AS [TO]
FROM RE_Input 
WHERE ((([Six] & "" & [Seven] & "" & [Eight] & "" & [Nine]) Like "*2*"))
GROUP BY RE_Input.Date_ID) As tmpQry
GROUP BY tmpQry.Date_ID;