Solved

Minus Query

Posted on 2003-12-01
11
2,011 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
ID: 9853813
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
ID: 9853819
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
ID: 9853822
Hmm too slow, good catch Jack.
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

Author Comment

by:Code_Buzz
ID: 9853957
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
ID: 9853983
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
 
LVL 32

Expert Comment

by:jadedata
ID: 9853994
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
ID: 9854046
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
ID: 9854059
0
 
LVL 23

Expert Comment

by:heer2351
ID: 9854071
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
ID: 9854081
Saw that I posted too late, but your point splitting works as well :)
0
 

Author Comment

by:Code_Buzz
ID: 9854083
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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

776 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