Solved

Not sure how to construct this query

Posted on 2012-03-29
2
293 Views
Last Modified: 2012-03-29
My problem is this: we have a ton of agent commission data in flat files and we are in the process of outsourcing a conversion.  Unfortunately, because we don't have any relational data there is no way for them to piece together commission hierarchies.  So it's up to me to reconstruct them, and I'm a rookie when it comes to Access. There are two tables in my Access database: AGENTS and AGCOMCREV

I need a query to eliminate all the records from AGCOMCREV except for those with the field "AgentCode" matching those found in the field of the same name in AGENTS.  This will reduce my dataset so I can move on to the next step and show you guys what I really need to accomplish.

Thanks for your help!
0
Comment
Question by:kbdaemon
2 Comments
 
LVL 9

Accepted Solution

by:
armchair_scouse earned 500 total points
ID: 37782990
Try

DELETE * FROM AGCOMREV WHERE AgentCode NOT IN (SELECT AgentCode FROM AGENTS)
0
 

Author Closing Comment

by:kbdaemon
ID: 37783466
Works great, but I missed the one character difference in "AgentCode" between the two tables so I just deleted 124,404 rows of data!!! It took too much memory so it was unrecoverable...starting over :(
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
This video shows where to find the word count, how to display it, and what it breaks down to in Microsoft Word.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…

786 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