Hi all, well i've got myself really confused.. and have a major headache..
I have 2 tables..
1st is Cases
and 2nd is NETANFImports
i've already got an update query that works perfectly.. it updates fields in Cases from that of NETANFImports
however.. the problem i'm having is, i cannot seem to get it to add new records from NETANFImports to Cases
I really would love to be able to get both querys into 1, so it can update and then add new.. but thats way out of my compresion, so i have to do it step by step..
This is what i have for the Update code:-
And this is what i have for the Adding new records to the Cases:-
I know, its not the best, but the updating works perfectly..
If someone can give me a hand, then i'll be really gratefull, and hopefully wont have a headache.. lol
I have 2 tables..
1st is Cases
and 2nd is NETANFImports
i've already got an update query that works perfectly.. it updates fields in Cases from that of NETANFImports
however.. the problem i'm having is, i cannot seem to get it to add new records from NETANFImports to Cases
I really would love to be able to get both querys into 1, so it can update and then add new.. but thats way out of my compresion, so i have to do it step by step..
This is what i have for the Update code:-
Code:
UPDATE NETANFImports,
Cases SET Cases.Comments = [NETANFImports].[Tracking Comments],
Cases.[E-mail Address] = [NETANFImports].[Email Address],
Cases.[Home Phone] = [NETANFImports].[Phone Number],
Cases.Address = [NETANFImports].[Address],
Cases.City = [NETANFImports].[City],
Cases.[State/Province] = [NETANFImports].[State],
Cases.[ZIP/Postal Code] = [NETANFImports].[Zip Code],
Cases.MC = [NETANFImports].[Master Case Number],
Cases.[Last Name] = [NETANFImports].[Last Name],
Cases.[First Name] = [NETANFImports].[First Name],
Cases.[SP End Date] = [NETANFImports].[Service Plan End],
Cases.[Last Contact] = [NETANFImports].[Last Contact],
Cases.[SP Begin Date] = [NETANFImports].[Service Plan Begin],
Cases.[EF Contract Signed] = [NETANFImports].[EF Contract Signature Date],
Cases.[Month of import to db] = [NETANFImports].[Month],
Cases.Status = [NETANFImports].[Status],
Cases.Title = NETANFImports.[Title]
WHERE (((Cases.MC)=[NETANFImports].[Master Case Number])
AND ((Cases.[Last Name])=[NETANFImports].[Last Name])
AND ((Cases.[First Name])=[NETANFImports].[First Name]));
And this is what i have for the Adding new records to the Cases:-
Code:
INSERT INTO Cases ( [First Name], [Last Name], [Home Phone], MC, [SP End Date], [Last Contact], [SP Begin Date], [Month of import to db], Title )
SELECT DISTINCT
NETANFImports.[First Name],
NETANFImports.[Last Name],
NETANFImports.[Phone Number],
NETANFImports.[Master Case Number],
NETANFImports.[Service Plan End],
NETANFImports.[Last Contact],
NETANFImports.[Service Plan Begin],
NETANFImports.Status,
NETANFImports.Title
FROM NETANFImports LEFT JOIN Cases ON (NETANFImports.[Title] = Cases.[Title]) AND (NETANFImports.[Status] = Cases.[Status]) AND (NETANFImports.[Month] = Cases.[Month of import to db]) AND (NETANFImports.[Service Plan Begin] = Cases.[SP Begin Date]) AND (NETANFImports.[Last Contact] = Cases.[Last Contact]) AND (NETANFImports.[Service Plan End] = Cases.[SP End Date]) AND (NETANFImports.[Master Case Number] = Cases.[MC]) AND (NETANFImports.[Phone Number] = Cases.[Home Phone]) AND (NETANFImports.[Last Name] = Cases.[Last Name]) AND (NETANFImports.[First Name] = Cases.[First Name])
WHERE (((Cases.[First Name]) Is Null) AND ((Cases.[Last Name]) Is Null) AND ((Cases.MC) Is Null));
I know, its not the best, but the updating works perfectly..
If someone can give me a hand, then i'll be really gratefull, and hopefully wont have a headache.. lol