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
251 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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
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…

705 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now