Delete Query

[stan.chernov@gmail.com]

IN SHORT:
Is there a way to create delete query that deletes records from a table based a query that's also based on the same table?


BACKGROUND: I have a daily report that has a 7 day history... I would like to set up a db to keep track of all of the historical data in it. In addition i would like foir this db to run the daily summary on latest day within available data.

i've set up the db, where once a day i'll simply drop a report into a folder, then open access db and run "import" macro. the macro simply imports the data into my main table.

my main problem is that i am basically adding data for each record 7 times (as the same data from one day will show up in 7 daily reports (daily report with 7 day history)... i'd l ike to set up some sort of delete query to clean my main table of duplicate rows.

the data has "import date" field (default value Date()... so it basically keeps track of when i've uploaded the data. I'd like to use that import date as a flag to decide if a record shouold be deleted. I have a subQ that pulls data elements for the record (excluding record ID and import date) to list a number of unioque records in the database. then i add "Max Import Date" field to show the last time this record was imported.

What i'd like to do is use that query to go throgh the table and delete all of the records that <> Max Import Date. It would seem like a simple delete query (i am new to them... so perhaps i am wrong)... i can run the select query and it showes all the right records to be deleted. However when i change the query to "delete query" it does not remove the records and gives me this message:

"COULD NOT DELETE FROM SELECTED TABLE"



I am guessing it is because the query i use to identify the records to be deleted is based on the table where deletions are about to take place... is there a way around it? Or is this something else?

I've tried to make the query a "Make Table" query to use the table as a list of records to be deleted, but it's not very solid logic and it still doesn't work.

any advice would be of much help.

[stan.chernov@gmail.com]

and here is the code

DELETE distinctrow DATA.*
FROM DATA INNER JOIN [03 - SubQ - 01 - IDs for dup records in DATA] ON DATA.RecordID = [03 - SubQ - 01 - IDs for dup records in DATA].RecordID
WHERE ((([03 - SubQ - 01 - IDs for dup records in DATA].[Good RecordID]) Is Null)); 

[weekend00]

Access won't delete from a table join with a query.
make [03 - SubQ - 01 - IDs for dup records in DATA] a table,
then use your query to delete.

[oldman]

Try setting up a unique identifier for each record. I use the auto number feature for that. When you transfer your data, simply use a find unmatched approach to populating the historical record with an append query. If the unique identifier already exists in the history table, you do not write that record again. There is no need to ever delete it since no duplicates are created in the first place.
Let's take an example.
You append your records on Monday to your one week table and it adds whatever number of rows, auto numbered, to that table. The next query you run checks the autonumber field to decide whether or not to append a record to your history table. If it is last Friday's record, the autonumber value will already exist in the history file so no addition for that record will take place. That takes care of historical records. Now for the easy way to update the one week report content. The next query is one that simply retrieves records from the history table where the record date >= (Date()-7). Now you have this week's data in the report query, that last one, and the history file need never be purged because it never sees a duplicate. As a safety measure against inadvertent duplication in the history file, you could even set the property of the autonumber transfer field as a "key" field for that table. Even if your query tries to write a duplicate record, that will stop it.