Delete Table Query

[NeonSky via AccessMonster.com]

Hello Everyone!

In the query design window I have table "A" & table "B". Table "A" and table
"B" share the same structure. I would like records from table "A" to be
deleted where table "A" has like records from table "B".

Presently I created the one to one relationship between the contingent fields
in the two tables, and pulled the "*" from the table where I want to delete
the record. What is peculiar is if I leave this as a select query it returns
the record I want to delete. Though if I set the query as a delete query a
msg box states " Could not delete from specified tables".

Happy to provide additonal info, thanks so much for thinking about my
question!!

 

[Jeff Boyce]

You've asked a "how do I?" question. I'm wondering "why?".

If you have two tables with identical structure, why? Are you doing
clean-up, with the thought of eliminating one of those (duplicate) tables?
Or are you keeping two copies and "moving" records between the tables?

If the latter, please describe what (not how) you want to do - the newsgroup
readers may be able to offer alternate approaches.

If the former, why have two tables with identical structure?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[NeonSky via AccessMonster.com]

Hello Jeff,

I have two tables with identical structure as one is the daily table, and the
other is a history table. when the data initially comes into the daily table
it is first scrubbed, then all the querie's and lists get generated. Right
off the bat I am looking to delete the records that come in on the daily
table that match specific criteria on the history table.

Of course I am open to your design recommendations as I am relatively new at
database design.

Thanks a bunch!

You've asked a "how do I?" question. I'm wondering "why?".

If you have two tables with identical structure, why? Are you doing
clean-up, with the thought of eliminating one of those (duplicate) tables?
Or are you keeping two copies and "moving" records between the tables?

If the latter, please describe what (not how) you want to do - the newsgroup
readers may be able to offer alternate approaches.

If the former, why have two tables with identical structure?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hello Everyone!

[quoted text clipped - 14 lines]

Happy to provide additonal info, thanks so much for thinking about my
question!!

 

[Jeff Boyce]

Right off the top of my head, what about the idea of using a single table,
but adding a "Scrubbed" field? That way, you could indicate that the record
had been "scrubbed", and use that field to ensure it wouldn't show up in a
query that returns "un-scrubbed" records.

If you need to know when it was scrubbed, use a date/time field. If you
only need to know THAT it was scrubbed, use a yes/no field.

The clear advantage, in my mind, is that you never have to "move" the
record.

I'm not clear on why/how you are comparing a new (daily) record to a
historical record.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hello Jeff,

I have two tables with identical structure as one is the daily table, and
the
other is a history table. when the data initially comes into the daily
table
it is first scrubbed, then all the querie's and lists get generated. Right
off the bat I am looking to delete the records that come in on the daily
table that match specific criteria on the history table.

Of course I am open to your design recommendations as I am relatively new
at
database design.

Thanks a bunch!

You've asked a "how do I?" question. I'm wondering "why?".

If you have two tables with identical structure, why? Are you doing
clean-up, with the thought of eliminating one of those (duplicate) tables?
Or are you keeping two copies and "moving" records between the tables?

If the latter, please describe what (not how) you want to do - the
newsgroup
readers may be able to offer alternate approaches.

If the former, why have two tables with identical structure?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hello Everyone!

[quoted text clipped - 14 lines]

Happy to provide additonal info, thanks so much for thinking about my
question!!

 

[NeonSky via AccessMonster.com]

Wonderful recommendations Jeff. Got me thinking in the right direction.

Thanks so much!

Right off the top of my head, what about the idea of using a single table,
but adding a "Scrubbed" field? That way, you could indicate that the record
had been "scrubbed", and use that field to ensure it wouldn't show up in a
query that returns "un-scrubbed" records.

If you need to know when it was scrubbed, use a date/time field. If you
only need to know THAT it was scrubbed, use a yes/no field.

The clear advantage, in my mind, is that you never have to "move" the
record.

I'm not clear on why/how you are comparing a new (daily) record to a
historical record.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hello Jeff,

[quoted text clipped - 34 lines]