ca1358
asked on
Adding Criteria to a Delete Statement
this statement works
DoCmd.RunSQL "Delete [CMTS_Daily_Extract].[CMMT _NUM] FROM [CMTS_Daily_Extract] WHERE [CMTS_Daily_Extract]![CMMT _NUM] IN (SELECT [DupsWithOnlyMINTime].[CMM T_NUM] FROM [DupsWithOnlyMINTime]);"
Want to add
'AND where([CMTS_Daily_Extract] .[CMMT_NUM ]=[DupsWit hOnlyMINTi me].[CMMT_ NUM]);"
'AND ([CMTS_Daily_Extract].[Tak enTime]=[D upsWithOnl yMINTime]. [TakenTime ]);"
I tried many ways and not found anything to work.
. I would appreciated you help!
DoCmd.RunSQL "Delete [CMTS_Daily_Extract].[CMMT
Want to add
'AND where([CMTS_Daily_Extract]
'AND ([CMTS_Daily_Extract].[Tak
I tried many ways and not found anything to work.
. I would appreciated you help!
ASKER
I getting this
Enter Parameter Value
SupsWithOnlyMinTime.CMMT_N um
Enter Parameter Value
SupsWithOnlyMinTime.CMMT_N
ASKER
I getting this
Enter Parameter Value
DupsWithOnlyMinTime.CMMT_N um
Enter Parameter Value
DupsWithOnlyMinTime.CMMT_N
try this :
DoCmd.RunSQL "Delete [CMTS_Daily_Extract].[CMMT_NUM] FROM [CMTS_Daily_Extract]
WHERE [CMTS_Daily_Extract]![CMMT_NUM] IN (SELECT [DupsWithOnlyMINTime].[CMMT_NUM] FROM [DupsWithOnlyMINTime])
AND ([CMTS_Daily_Extract]![CMMT_NUM]=[DupsWithOnlyMINTime].[CMMT_NUM])
AND ([CMTS_Daily_Extract]![TakenTime]=[DupsWithOnlyMINTime].[TakenTime]);"
ASKER
Still getting this I getting this
Enter Parameter Value
DupsWithOnlyMinTime.CMMT_N um
Microsfot Visual Basic Run-time error '3021'
Reserved Error
Enter Parameter Value
DupsWithOnlyMinTime.CMMT_N
Microsfot Visual Basic Run-time error '3021'
Reserved Error
now try this:
DoCmd.RunSQL "Delete [CMTS_Daily_Extract].[CMMT_NUM] FROM [CMTS_Daily_Extract]
WHERE [CMTS_Daily_Extract]![CMMT_NUM] IN (SELECT [DupsWithOnlyMINTime].[CMMT_NUM] FROM [DupsWithOnlyMINTime])
AND ([CMTS_Daily_Extract]![CMMT_NUM]=(SELECT [DupsWithOnlyMINTime].[CMMT_NUM] FROM [DupsWithOnlyMINTime]))
AND ([CMTS_Daily_Extract]![TakenTime]=(SELECT [DupsWithOnlyMINTime].[TakenTime] FROM [DupsWithOnlyMINTime]));"
ASKER
DoCmd.RunSQL "Delete [CMTS_Daily_Extract].[CMMT _NUM] FROM [CMTS_Daily_Extract] WHERE [CMTS_Daily_Extract]![CMMT _NUM] IN (SELECT [DupsWithOnlyMINTime].[CMM T_NUM] FROM [DupsWithOnlyMINTime])AND ([CMTS_Daily_Extract]![CMM T_NUM]=(SE LECT [DupsWithOnlyMINTime].[CMM T_NUM] FROM [DupsWithOnlyMINTime])AND ([CMTS_Daily_Extract]![Tak enTime]=(S ELECT [DupsWithOnlyMINTime].[Tak enTime] FROM [DupsWithOnlyMINTime]);"
getting runtime error run-time error 3075
getting runtime error run-time error 3075
Hi,
the issue here is that I don't know the structure of the data and what you want to filter in where clause, i.e. I'm not sure to what you are linking these conditions...
Can you provide the sample data from tables involved in this statement .
the issue here is that I don't know the structure of the data and what you want to filter in where clause, i.e. I'm not sure to what you are linking these conditions...
Can you provide the sample data from tables involved in this statement .
ASKER
Hello,
are these the rows which you want to delete :
are these the rows which you want to delete :
SELECT a.cmmt_num, a.takentime
FROM CMTS_Daily_Extract a, DupsWithOnlyMINTime b
where a.cmmt_num=b.cmmt_num
and a.takentime=b.takentime;
ASKER
I want to delete the entire record of CMTS_Daily_Extract which match CMMT_NUM&only the Minimum TakenTime out DupsWithOnlyMinTime Table
ASKER
I want to delete the entire record of CMTS_Daily_Extract which match CMMT_NUM&only the Minimum TakenTime out DupsWithOnlyMinTime Table
Leaqving the Max TakenTime in CMTS_Daily_Extract
Leaqving the Max TakenTime in CMTS_Daily_Extract
Hello,
a lot of issues here but i figure it out..
Here is the final result/query :
I added the ID column and made it primary key to help with the process...
I attached the access with saved tables and query....
Hopefully this solution is ok for you..
Del.mdb
a lot of issues here but i figure it out..
Here is the final result/query :
delete from CMTS_Daily_Extract
where id in (SELECT CMTS_Daily_Extract.id
FROM CMTS_Daily_Extract INNER JOIN DupsWithOnlyMINTime ON (CMTS_Daily_Extract.TakenTime = DupsWithOnlyMINTime.TakenTime) AND (CMTS_Daily_Extract.CMMT_NUM = DupsWithOnlyMINTime.CMMT_NUM));
I added the ID column and made it primary key to help with the process...
I attached the access with saved tables and query....
Hopefully this solution is ok for you..
Del.mdb
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you! Sorry for being late was out sick yesterday.
you have error in this statement :
'AND where([CMTS_Daily_Extract]
it should be like this :
'AND ([CMTS_Daily_Extract].[CMM
complete statement is:
Open in new window