?
Solved

Minus Query

Posted on 2003-12-01
11
Medium Priority
?
2,047 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 2000 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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
 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
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…
Suggested Courses

839 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