Delete records based on antoher table

[lmh329]

I have two tables. One table is a table I maintain with maybe 10 policy ids. The other table is created everyday from system data that is generated into excel and I import into a table in Access. I want to write a query that will delete those 10 policies every time I pull in new data. I've attached an image of my query design. When I try to run this I receive an error message "Could not delete from Specified Tables." This query need to be part of a macro that is ran everyday.



Any help will be greatly appreciated.

[ranman256]

you cannot join tables and do the delete. (thats way too simple)
but you can do it using the IN command like the FIND DUPLICATES query.

use the query wizard and do the steps to

make a FIND DUPLICATES query
when complete , look at the design,
in the key field with the duplicate you have another SQL statement
" in (select key from table2)

make your delete query accordingly.

[Micron]

Sometimes you can if the DISTINCTROW predicate is used and I have done so. Sometimes you still can't if there's no PK on either side as it appears is the case here.
You can also provide criteria from the linked table if need be.

All in all though, I would try to avoid deletions with links unless I totally understand the effects in all cases, which I don't. There may be better options for you anyway, such as only appending to Access, or linking from Excel. I would not continually delete and recreate a table if that's what you are doing.

[lmh329]

ranman256==> Thank you so much. this is perfect !

[lmh329]

Whoops! I spoke to soon. I'm doing something wrong. you can see the table name and field names in the image above. What am I doing wrong?


In (SELECT [Policy ID(7)] FROM [tbl_Duos renewed from trad policy] As Tmp GROUP BY [Duo Policy ID(7)] HAVING Count(*)>1 )

[Micron]

you don't have a [Policy ID(7)] in [tbl_Duos renewed from trad policy]?

[lmh329]

Micron:

What should it be?

when I use this, it doesn't delete any records.

In (SELECT [Policy ID(7)] FROM [tbl_Duos renewed from trad policy] As Tmp GROUP BY [Duo Policy ID(7)] HAVING Count(*)>1 )

[Micron]

I'm afraid I don't know as I'm not familiar with the method.
My post was trying to point out that if you consider SELECT [Policy ID(7)] FROM [tbl_Duos renewed from trad policy] as simple English, there is no field with that name in that table. That field exists in the other table, while the table you specify in that sql portion has 2 fields with similar but different names. I'm surprised you didn't say you were getting a parameter prompt asking for a value for [tbl_Duos renewed from trad policy].[Policy ID(7)].

I guess the big question is does this work by itself:
SELECT [Policy ID(7)] FROM [tbl_Duos renewed from trad policy] As Tmp GROUP BY [Duo Policy ID(7)] HAVING Count(*)>1

If yes, then perhaps there is no hope for me

Another possibility is that you create a relationship between the 2 tables and opt for referential integrity using cascade updates and deletes. That way if you delete from one side, it should automatically delete from the other side. I should point out that your design looks suspect but cannot be sure (looks like repeating data at least). Can be sure that you should read up on how to name things, though. I'll post my usual links for those and other subjects in case you're interested.

Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.com...on-part-i.html
and/or
http://holowczak.com/database-normalization/

Entity-Relationship Diagramming: Part I, II, III and IV
http://rogersaccessblog.blogspot.ca/...ng-part-i.html

How do I Create an Application in Microsoft Access?
http://rogersaccessblog.blogspot.ca/...cation-in.html

One source about how to name things - http://access.mvps.org/access/general/gen0012.htm
What not to use in names - http://allenbrowne.com/AppIssueBadWord.html
About Auto Numbers
- http://www.utteraccess.com/wiki/Autonumbers
- http://access.mvps.org/access/general/gen0025.htm

The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm
Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp
About calculated table fields - http://allenbrowne.com/casu-14.html
About Multi Value Fields - http://www.mendipdatasystems.co.uk/m...lds/4594468763

[lmh329]

I got it to work.
When I changed the >1 to >0, it worked

In (SELECT [Duo Policy ID(7)] FROM [tbl_Duos renewed from tradpolicy] As Tmp GROUP BY [Duo Policy ID(7)] HAVING Count(*)>0 )


thanks all.

[Micron]

Ummm...you also changed the field name being used, from [Policy ID(7)] to [Duo Policy ID(7)]??