Deleting multiple records from multiple tables

[Guest]

I have about 10 tables that have a few duplicates on them.
They all have a primary unique key assigned to them.
Is there a way to create a query or something to delete all the dups?
Oh and I am quite new on Access 2007 so please use KISS theroy.

 

[John W. Vinson]

I have about 10 tables that have a few duplicates on them.
They all have a primary unique key assigned to them.
Is there a way to create a query or something to delete all the dups?
Oh and I am quite new on Access 2007 so please use KISS theroy.

Do you have dups *across tables* - i.e. would two records in different tables
with the same unique key constitute a "dup"? Or would some other field or
combination of fields constitute a dup? Or are the dups in the same table?

If you're deleting records from ten different tables you will probably need
ten delete queries - or a rethink of your table structure! Having ten
identically-structured tables for different subsets of "the same kind of data"
is not a good idea, for this very reason.

John W. Vinson [MVP]

 

[Guest]

John:
What I have is different primary keys , but the same data spread across
that table. There is no redundancy in the tables.

Do you have dups *across tables* - i.e. would two records in different tables
with the same unique key constitute a "dup"? Or would some other field or
combination of fields constitute a dup? Or are the dups in the same table?

What I have is different keys with the same data in all tables. The tables
all have diferent data, there is no redundancy in the tables.

 

[John W. Vinson]

What I have is different keys with the same data in all tables. The tables
all have diferent data, there is no redundancy in the tables.

I'm sorry, Benny, I read that sentence as a direct contradiction.

Storing the same data in different tables IS redundancy.

You say both " different keys with the same data " and " the tables all have
different data ". Either the data is the same, or it's different!

WHY do you need ten tables for the same kind of data? That's the root of your
problem: shouldn't it all be in one table?

John W. Vinson [MVP]

 

[Guest]

Ok let me start from the top:
10 tables -
What I have is 2 different primary keys - these 2 keys contain the exact
same data throughout the various tables.

Table layouts:
The only part of the tables that are similar are the primary keys field.
The rest of the tables contain different field names.
I apologise if I explained this incorrectly or incoherently before.

 

[John W. Vinson]

Ok let me start from the top:
10 tables -
What I have is 2 different primary keys - these 2 keys contain the exact
same data throughout the various tables.

Table layouts:
The only part of the tables that are similar are the primary keys field.
The rest of the tables contain different field names.
I apologise if I explained this incorrectly or incoherently before.

Ok... it sounds like you have one to one relationships between the tables.

One to one relationships are QUITE rare. If you're not "Subclassing" or using
"field level security" it's quite possible that your relationships are
incorrect.

Also, a table cannot have "two primary keys" - only one. That Key can consist
of one field, or two fields, or ten fields - but it's only one primary key.

Could you explain what real-world Entity - person, place, or thing - is
represented by a few of these tables? What information are you storing about
the entity? If all ten tables have the same primary key, why use ten tables
rather than one table (which can be queried for subsets of the data)?

John W. Vinson [MVP]

 

[Guest]

John:
There ended up only being a few dups so I went thru and deleted them out
of the tables one at a time.

I do have another question:
I am tryign to create a report. But when I select the create tab the report
area is greyed out.
How can I fix this?

 

[John W. Vinson]

I do have another question:
I am tryign to create a report. But when I select the create tab the report
area is greyed out.
How can I fix this?

Are you using a .mdb file (which lets you design reports) or a .mde file
(which doesn't)? Do you have exclusive use of the database?

John W. Vinson [MVP]

 

[Guest]

I am using an .accde, but I can get into the tables and make changes and get
into queries andmake changes

 

[John W. Vinson]

I am using an .accde, but I can get into the tables and make changes and get
into queries andmake changes

An .accde does NOT allow design changes to forms or reports. You must use the
..accdb from which it was made, and then make a new .accde.

That's the whole purpose of a .mde or .accde file: to lock design changes. Why
you can restructure tables in such a database is a mystery to me.

John W. Vinson [MVP]

 

[Guest]

Can I import the accde into a new access file and then have all the
functionality that I need?

 

[John W. Vinson]

Can I import the accde into a new access file and then have all the
functionality that I need?

No.

The database was developed as an accdb file.

When it was converted to accde, all of the VBA code was compiled, and then the
actual code was DELETED. It *simply doesn't exist any more*.

You must - no option, no choice in the matter - talk to whoever developed the
accdb file and converted it to an accde file, and obtain a copy of the accdb.

If you are the person who did so, and you didn't keep a copy of the accdb,
then you'll have to start over from scratch. You can't go back.

John W. Vinson [MVP]

 

[Guest]

Well then it looks like I am Sh** out of luck then. The person that created
the database is gone and will not return phone calls.