Re: Reconcilliation

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Roji. P. Thomas (lazydragon_at_nowhere.com)
Date: 03/02/04 

Date: Tue, 2 Mar 2004 18:14:50 +0530

Assuming that your client table has one to one realtionship with other
tables doing a left outer join will give u the result

here is an example from pubs.

SELECT j.job_id,
CASE WHEN e.job_Id IS NULL THEN 'NO' ELSe 'YES' END
FROM jobs j
LEFT OUTER JOIN employee e
on j.job_Id = e.job_id 

-- 
Roji. P. Thomas
SQL Server Programmer
"Peter Newman" <anonymous@discussions.microsoft.com> wrote in message
news:EDFD3406-B0B2-4CEE-91B0-0BE05D6FE94F@microsoft.com...
> I have severay tables all linked by a Client_Ref Field. Im need to find
out if any of the tables are missing records for any of the clients
>
> TAdmin
> Client_Ref  VARCHAR(6)
> CompanyName VARCHAR(25)
>
> TComms
> Client_Ref  VARCHAR(6)
>
> TAccounts
> Client_Ref  VARCHAR(6)
>
> TFees
> Client_Ref  VARCHAR(6)
>
> TUsers
> Client_Ref  VARCHAR(6)
>
>
> Im trying to get a result to show any clients that do NOT have a record in
all tables
>
>
> Client_Ref       CompanyName       TAdmin     TComms      TAccounts
TFees     TUsers
> 121212           Test Company 1          YES           NO              YES
YES         NO
> 225225           Test Company 2           NO           NO               NO
YES         NO
>
> Can anybody help?

Relevant Pages

  • Access2k + SQL 7.0 Lock problem
    ... I have an Access database which has tables linked to SQL Server 7.0 ... Clients ... SummaryMaxDate INNER JOIN ... SummaryMaxDate.ClientKey = Clients.ClientKey LEFT OUTER JOIN ...
    (microsoft.public.access.queries)
  • Re: is there a "does not include" query in Access
    ... You can use the In operator or the EXISTS Clause. ... you can simple use a Left Outer Join like: ... FROM tblClient AS C LEFT JOIN ... > create a report that shows me which clients we have not received work for. ...
    (microsoft.public.access.queries)
  • Re: Newbie: Select records in Table A not in Table B
    ... You use an Outer Join to do this. ... "Terry" wrote in message ... I have another table B of actual clients. ... > potential clients only. ...
    (microsoft.public.access.queries)