Minus Query

I want to have my query do a MINUS, but I know that I can't do that in Access so I need some help. I basically want to append records from one table to another. I have table A and B with both of them have the columns CN, FY, and CLN. Table A has more columns but the CN, FY, and CLN make up one distinct record. I want to append to the table B where only table B dosen't have that distinct record. I could have those three exact rows in table A with different dates but I only want them as 1 record in TAble B.

Basically this is how it should look like with a minus

INSERT INTO TableB ( CN, FY, CLN )
(SELECT CN, FY, CLN FROM TableA)
MINUS
(SELECT (CN, FY, CLN FROM TableB)

So I could have three rows in tableA

10/31/2003   10     3      5
10/25/2003    10    3      5
9/25/2003      10    3      5

and the row in table B should look like this after the append:

10    3     5  
Code_BuzzAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jadedataMS Access Systems CreatorCommented:
INSERT INTO TableB ( CN, FY, CLN )
SELECT CN, FY, CLN
FROM TableA
  LEFT JOIN TABLEB
    ON (TableA.CN=TableB.CN)
    AND (TableA.FY=TableB.FY)
    AND (TableA.CLN=TableB.CLN)
WHERE (TableB.CN is null);
0
heer2351Commented:
Try:

INSERT INTO TableB ( CN, FY, CLN )
(SELECT CN, FY, CLN FROM TableA
 left join TableB on TableA.CN=TableB.CN and TableA.FY=TableB.FY and TableA.CLN=TableB.CLN
  where TableB.CN is null)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
heer2351Commented:
Hmm too slow, good catch Jack.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Code_BuzzAuthor Commented:
This is the exact SQL code I tried to put in ( I shrank up the column names in my example)

INSERT INTO L37_Remarks ( ContractNumber, FY, CLIN )
SELECT ContractNumber, FY, CLIN
FROM 72D_L37
LEFT JOIN L37_Remarks
ON (72D_L37.ContractNumber=L37_Remarks.ContractNumber)
AND (72D_L37.FY=L37_Remarks.FY)
AND (72D_L37.CLIN=L37_Remarks.CLIN)
WHERE (L37_Remarks.ContractNumber is null);

I get a error::

Syntax error (missing operator) in query expression '72D_L37.ContractNumber=L37_Remarks.ContractNumber'.

I tried it heer2351 way and I got the same error...
0
heer2351Commented:
Try this:

INSERT INTO L37_Remarks ( ContractNumber, FY, CLIN )
SELECT 72D_L37.ContractNumber, 72D_L37.FY, 72D_L37.CLIN
FROM 72D_L37
LEFT JOIN L37_Remarks
ON (72D_L37.ContractNumber=L37_Remarks.ContractNumber
AND 72D_L37.FY=L37_Remarks.FY
AND 72D_L37.CLIN=L37_Remarks.CLIN)
WHERE
  L37_Remarks.ContractNumber is null;


0
jadedataMS Access Systems CreatorCommented:
INSERT INTO L37_Remarks ( ContractNumber, FY, CLIN )
SELECT [72D_L37].[ContractNumber], [72D_L37].[FY], [72D_L37].[CLIN]
FROM 72D_L37
  LEFT JOIN L37_Remarks
  ON ([72D_L37].[ContractNumber] = [L37_Remarks].[ContractNumber])
  AND ([72D_L37].[FY] = [L37_Remarks].[FY])
  AND ([72D_L37].[CLIN] = [L37_Remarks].[CLIN])
WHERE ([L37_Remarks].[ContractNumber] is null);

presuming all the datatypes on corresponding field are the same, and adding bracketing only as a gremblin check I see nothing wrong with the statement itself.
0
Code_BuzzAuthor Commented:
INSERT INTO L37_Remarks ( ContractNumber, FY, CLIN )
SELECT [72D_L37].ContractNumber, [72D_L37].FY, [72D_L37].CLIN
FROM 72D_L37 LEFT JOIN L37_Remarks ON ([72D_L37].ContractNumber = L37_Remarks.ContractNumber) AND ([72D_L37].CLIN = L37_Remarks.CLIN) AND ([72D_L37].FY = L37_Remarks.FY)
WHERE (((L37_Remarks.ContractNumber) Is Null));


Here is the statement.....weird....thanks.....

How do I give both points??
0
Code_BuzzAuthor Commented:
0
heer2351Commented:
My guess would be that in your statement and also my first statement the select was ambiguous, access did not know which table to use. So this has to specified explicitely.

More than one Expert helped. What do I do?
You split the points. Scroll down to the bottom of the question and click the "split points" button at the bottom of the page. Select the radio button of the comment who you want to Accept as the answer. Only one button can be selected. Set the point value (a text box above the comment) of how much you want this person to receive of the points. Then set the point values for each of the experts comments to whom you want to allocate points and these will be considered Assisted answers in helping you resolve the issue. Double check your information and then click the Submit button at the bottom of the page. One note: the total points of the splits must equal the amount you asked the question for itself, and no person can receive fewer than 20 points.  
0
heer2351Commented:
Saw that I posted too late, but your point splitting works as well :)
0
Code_BuzzAuthor Commented:
Yeah I saw that once I did the accept....This time I made a new questions with a link...I'll do that next time!! Thanks for the advice!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.