Solved

Not sure how to construct this query

Posted on 2012-03-29
2
294 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

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

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

Suggested Solutions

Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
The viewer will learn how to make their project stand out over others by learning how to change colors and shapes, add spaces, change directions, and add bullets to their charts.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

856 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