Solved

Not sure how to construct this query

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

Industry Leaders: 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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
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…
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

726 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