Help adding new records using a Query

bitsbb01

Registered User.
Local time
Yesterday, 19:40
Joined
Apr 2, 2013
Messages
34
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:-
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
 

Users who are viewing this thread

Back
Top Bottom