Help with Delete Query

[eizquierdo]

Lets see if anyone here can help me. I am trying to keep a running count of the number of times a decal number appears in a report and then decal that decal from the database ones it disappears. Let me try to explain what I have so far and where I am stuck. I am trying to building this quickly just as a simply way to test out a theory if it works it will move out into development by the real software developers. *All the records have a unique decal number



1. I have a sheet in excel titled "Portal", this sheet is updated everyday.

2. I have a table in access titled "Day Count", this table is appended everyday from the excel sheet called "Portal".

3. I have a query in access that counts the number of duplicates in "Day Count".

Up to this point it works perfect in showing me how many times that record has appeared in the report. The issue then comes when that record is no longer in the report and I need to stop tracking it. I need to stop tracking it because that record can later on reappear in the report and I need the count to start over rather than continue from where it left of previously. This is what I had in mind:

4. I have second table in access called "Portal" this table is a simple link to the excel "Portal" sheet. (These would be the active records)

5. I have a query called "Cleared - Day Count" that will display every record located in the "Day Count" table that is not located in the "Portal" table. (These are the records I need to delete, trying not to do it manually because it gets into the thousands)

6. I am attempting to create a delete query the will delete all the rows with the data in table "Day Count" that appears in query "Cleared - Day Count"


Can anyone think of a quick and dirty way to take care of this? I attempted to do a match type function in the query and then change it over the delete but it kept asking me to enter a decal number.

[June7]

You want to delete unmatched records? Review http://www.utteraccess.com/forum/Del...ded&pid=529904

[eizquierdo]

Ok this seems like it might work. That SQL looks like to pointing back to delete "red" though. I am trying to delete anything in the other table how can I have it do a MATCH function sort of thing.

[eizquierdo]

It seems to be working like this:

DELETE [Day Count].*, [Day Count].Decal, [Day Count].Decal
FROM [Day Count]
WHERE ((([Day Count].Decal) In (SELECT [Cleared].[Decal]
FROM [Cleared] LEFT JOIN [Day count] ON [Cleared].[Decal] = [Day Count].[Decal]
WHERE ((([Day Count].[Decal]) Is Null))));


But it is deleting zero records, maybe I have it looking backwards?

[eizquierdo]

I got it! Thank you for pointing me in the right direction. This was how I resolved it. I turned the "Cleared - Day Count" query into a table after that I created a new delete query as follows:

DELETE [Day Count].*, [Day Count].Decal
FROM [Day Count]
WHERE ((([Day Count].Decal) In (SELECT [Decal] FROM [Cleared])));

Turned out to be pretty simple!