Access Delete Query not equal to max query

[Michiel Soede]

Hi,



I have a table "TBL_DATA_RISICO_PROFIEL" of which I want to delete old records (meaning older than the most recent timestamp of every forecast in the table).
There are 5 companies, so I have made a Max query on that same table ("TBL_DATA_RISICO_PROFIEL") that shows all the most recent time stamps per forecast per company.
I was hoping that I could create a delete query and delete all records that not show any value if I left join these three fields. However that does not work.

Is there anyone with a suggestion how to delete the older records with a joined table like this?

Code until now:

DELETE TBL_DATA_RISICO_PROFIEL.*, TIMESTAMP_MAX.COMPANYFROM TBL_DATA_RISICO_PROFIEL LEFT JOIN TIMESTAMP_MAX ON (TBL_DATA_RISICO_PROFIEL.TIMESTAMP = TIMESTAMP_MAX.MaxOfTIMESTAMP) AND (TBL_DATA_RISICO_PROFIEL.FORECAST = TIMESTAMP_MAX.FORECAST) AND (TBL_DATA_RISICO_PROFIEL.COMPANY = TIMESTAMP_MAX.COMPANY)
WHERE (((TIMESTAMP_MAX.COMPANY) Is Null));

Many thanks so far,

Michiel

[ranman256]

you cannot delete joined tables.
You must use the IN keyword and a sub query.
use the query wizard and create a FIND DUPLICATES QUERY.
save then design the query, you'll notice the criteria in the field: IN (SELECT field in table)

making a delete query this way WILL work. Like:
delete * from table where [id] = ([ID] IN (select [id] from table2)

[Michiel Soede]

you cannot delete joined tables.
You must use the IN keyword and a sub query.
use the query wizard and create a FIND DUPLICATES QUERY.
save then design the query, you'll notice the criteria in the field: IN (SELECT field in table)

making a delete query this way WILL work. Like:
delete * from table where [id] = ([ID] IN (select [id] from table2)

Thanks for your reaction. I will try it tonight.

[Michiel Soede]

I was not able to fully reconstruct a proper query on basis of the above. Would a query like below work out? (it is not working correct yet where Access keeps on running):

DELETE TBL_DATA_RISICO_PROFIEL.*
FROM TBL_DATA_RISICO_PROFIEL
WHERE (((TBL_DATA_RISICO_PROFIEL.TIMESTAMP)<>(SELECT Max(TIMESTAMP) AS MaxTimestamp FROM TBL_DATA_RISICO_PROFIEL AS Dupe      
   WHERE (Dupe.COMPANY = TBL_DATA_RISICO_PROFIEL.COMPANY)	                      
   AND (Dupe.FORECAST = TBL_DATA_RISICO_PROFIEL.FORECAST))));