I'm filling a table (Table4) with data from 3 other tables (Table1, Table2, & Table3)
From Table3 all I need is the count of number of records which match the criteria, from Tables1 & 2 I need actual data from the table.
I have an SQL statement:
Insert INTO Table4 (PID,SID,PrID,CDate,CStart,CEnd,CCode,Fax,Email,Phone,PACnt)
Select Table1.PID,Table2.SID,Table1.PrID,CDate,CStart,CEnd,CCode,Fax,Table2.Email,Phone, count (*) Table3 From
Table1 Full Outer Join Table2 on Table1.SID = Table2.SID Full Outer Join Table3 on Table3.SID = Table1.SID
where Table1.CDate >= '12/1/2013' and Table1.CDate <= '12/31/2013' and Table1.PID = 979 and Table1.UID = 225
If I use a field from Table3 - e.g. Field1.Table3 instead of the Count (*) it works except I don't get the count - just the value in that field. For any given match of SID Table3 might have 0 or more records. I just need the count of the number of records. As I said, everything else works fine - Table4 gets filled with the proper data (except the count of records in Table3).
Any help would be appreciated