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
255 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
Technology Partners: 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!

 
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

Independent Software Vendors: 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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Familiarize people with the process of utilizing SQL Server views 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 Microsoft Access…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

713 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