Solved

Not sure how to construct this query

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

Technology Partners: 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!

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

749 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