Solved

How do I create an Access query that will delete rows from a table based upon certain criteria?

Posted on 2011-09-21
7
258 Views
Last Modified: 2012-06-21
Hello,

I have a query that shows three fields: short ID, long ID and a count of the long ID. I also have a table that shows the same short and long ID fields and data. What I need is a query that checks the count number, if it is greater than 2 then the deletion query will remove data from the table based upon the short ID else if it is equal to 2 then it should be based on the long ID.

Hope you can follow the above,

f19l
0
Comment
Question by:f19l
[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
  • 3
  • 3
7 Comments
 
LVL 13

Expert Comment

by:Shanan212
ID: 36575170
Instead of deleting, can't you just make the query output the values that you want (not-deleted) to a table?
0
 
LVL 7

Expert Comment

by:BusyMama
ID: 36575236
I think this will work ...

DELETE Table.*, Query.CountOfLongID
FROM [Table], Query
WHERE (((Query.CountOfLongID)>2 And [Table].[ShortID]=[Query].[ShortID])) OR (((Query.CountOfLongID)=2 And [Table].[LongID]=[Query].[LongID]));
0
 

Author Comment

by:f19l
ID: 36578780
BusyMama, I tried to use you but I got a message stating "could not delete from specified tables".
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 7

Expert Comment

by:BusyMama
ID: 36580067
Observation/Question ....

The query which is counting the fields, is that one statement?  Because as I look at this more, I see what could potentially be a problem for you that I want to clarify.

If you have one query counting the fields, and the SQL is something like:

SELECT Table.ShortID, Table.LongID, Count(Table.LongID) AS CountOfLongID
FROM [Table]
GROUP BY Table.ShortID, Table.LongID
HAVING (((Count(Table.LongID))>=2));

Then in reality you are counting only the rows where the Short ID AND Long ID are the SAME in 2 or more occurrences (because you are "grouping by" both, that means both must be the same in order to be counted more than once as a group).

Is that what you intended?

Or, did you want to count the Short ID and Long ID separately and find occurrences of Short ID more than once, and find occurrences of Long ID more than once, and delete?

I want to make sure before I work on a solution to the error message you are receiving.

Thanks!
0
 

Accepted Solution

by:
f19l earned 0 total points
ID: 36580817
Hello BusyMama,

I think that I have come up with a solution to my problem, I have basically split the process in two. I have created two queries that count the number of times the longID appears. I have set the criteria on each query such that one will show the shortIDs when the longID appears only twice and the other will show only the longID when it appears more than twice. Next, I run the first query and delete from the table based upon the shortID and then I run the second that deletes based upon the longID. It may not be the cleanest of methods but it is simple enough for me to make it work.

Thanks for all your help and interest,

f19l
0
 
LVL 7

Expert Comment

by:BusyMama
ID: 36580910
As long as you are getting the results you need - then great!  :)  Good luck with your project!
0
 

Author Closing Comment

by:f19l
ID: 36708090
I was able to come up with a way to solve my own problem using my existing knowledge.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

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 …
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

617 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