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
253 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
  • 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Suggested Solutions

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

773 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