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

 
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

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
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…

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