Solved

Minus Query

Posted on 2003-12-01
11
1,999 Views
Last Modified: 2007-12-19
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  
0
Comment
Question by:Code_Buzz
  • 5
  • 4
  • 2
11 Comments
 
LVL 32

Expert Comment

by:jadedata
Comment Utility
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
 
LVL 23

Accepted Solution

by:
heer2351 earned 500 total points
Comment Utility
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
 
LVL 23

Expert Comment

by:heer2351
Comment Utility
Hmm too slow, good catch Jack.
0
 

Author Comment

by:Code_Buzz
Comment Utility
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
 
LVL 23

Expert Comment

by:heer2351
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 32

Expert Comment

by:jadedata
Comment Utility
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
 

Author Comment

by:Code_Buzz
Comment Utility
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
 

Author Comment

by:Code_Buzz
Comment Utility
0
 
LVL 23

Expert Comment

by:heer2351
Comment Utility
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
 
LVL 23

Expert Comment

by:heer2351
Comment Utility
Saw that I posted too late, but your point splitting works as well :)
0
 

Author Comment

by:Code_Buzz
Comment Utility
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

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now