Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Minus Query

Posted on 2003-12-01
11
Medium Priority
?
2,043 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

618 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