By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,483 Members | 1,449 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,483 IT Pros & Developers. It's quick & easy.

Joining Tables

P: 73
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?
Feb 8 '08 #1
Share this Question
Share on Google+
2 Replies


mwasif
Expert 100+
P: 801
Moved to Access Forum.
Feb 9 '08 #2

MMcCarthy
Expert Mod 10K+
P: 14,534
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.

Expand|Select|Wrap|Line Numbers
  1. SELECT tmpQry.Date_ID, tmpQry.[TO]
  2. FROM (
  3. SELECT PL_Input.Date_ID,
  4. Count(PL_Input.[1]+PL_Input.[2]+PL_Input.[3]+PL_Input.[4]+PL_Input.[5]) AS [TO]
  5. FROM PL_Input 
  6. WHERE ((([PL_Input].[1] & "" & [PL_Input].[2] & "" & [PL_Input].[3] & "" & [PL_Input].[4] & "" & [PL_Input].[5]) Like "*2*"))
  7. GROUP BY PL_Input.Date_ID
  8. UNION ALL
  9. SELECT RE_Input.Date_ID, 
  10. Count(([Six] & "" & [Seven] & "" & [Eight] & "" & [Nine])) AS [TO]
  11. FROM RE_Input 
  12. WHERE ((([Six] & "" & [Seven] & "" & [Eight] & "" & [Nine]) Like "*2*"))
  13. GROUP BY RE_Input.Date_ID) As tmpQry
  14. GROUP BY tmpQry.Date_ID;
  15.  
Feb 10 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.