Duplicate a Record

[Iram]

Hello,

I have two tables that are related by Case# by a one to one relationship.
Each table has about 150 fields. Is there an easy way to duplicate a record
that spans across two tables? Not sure if this helps but I have a continuous
form that shows all of my records with about 10 fields, 5 fields from each
table. In this continous form I have a button that currently doesn't do
anything but I would like to change it that upon clicking it access will
duplicate the record that spans two tables (about 300 fields total). There
are few records that I don't want to duplicate, in which I would rather leave
them blank during the duplicating process but if that is going to make it
more difficult I will settle just for the full duplication and manually clean
the fields afterwards.

Is this possible?
Your help is greatly appreciated.


Thanks.
Iram/mcp

 

[Dorian]

What do you mean by:
duplicate a record?
spans across two tables?

Do your tables have primary keys?
You realize you cannot duplicate a primary key right?
Also a table row cannot contain 300 fields.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[Iram]

The two tables have information all about a person. The db is working fine,
we are able to add hundreds of records without an issue. The problem is when
the same person comes in again we would like to duplicate almost all of his
attributes.
Besides the normalizing of the db which is not an option right now, is there
a way to duplicate a record that spans two tables?
Btw, I do have primary keys on Autonumber and upon the duplication process
the new duplicate would need the the next autonumber.

Thanks.
Iram/mcp

 

[John W. Vinson]

The two tables have information all about a person. The db is working fine,
we are able to add hundreds of records without an issue. The problem is when
the same person comes in again we would like to duplicate almost all of his
attributes.
Besides the normalizing of the db which is not an option right now, is there
a way to duplicate a record that spans two tables?
Btw, I do have primary keys on Autonumber and upon the duplication process
the new duplicate would need the the next autonumber.

You'll need to run two append queries, one to each table. Just don't include
the autonumber in the append and it will increment normally.

That said... the longer you delay normalizing the deeper you'll get stuck in
the mud. It's not that huge a job; you can use a "Normalizing Union Query"
(google for it) to migrate your data into tall-thin tables. Post back with a
table description if you'ld like help.

BTW thanks for the kind words in the "suggestion to microsoft"; Microsoft does
recognize the efforts of volunteers, rather generously. See:

http://mvp.support.microsoft.com/default.aspx

for a description of the program.

 

[Iram]

The database tracks every time a person applies for a position. All of the
fields describe all of the attributes of a person. Since each table can only
hold 250 fields I had to span into a seperate table using a One to One
relationship. I like the append query idea by John Vinson but I am not to
familiar with that feature.

John Vinson- How should I go about using the Append query.



Thanks.
Iram/mcp

 

[Iram]

PieterLinden- I just read up on Normalization. Normalizing might make the
duplicating process more difficult. If I were to normalize my two existing
tables they would turn into about 30 tables. Just imagen running an append
query over that many tables....

 

[John W. Vinson]

The database tracks every time a person applies for a position.

Then you should have *ONE* table for people - each person and their attributes
appear once and once only - related one to many to a table of Applications.
This table would have a foreign key PersonID (a link to the table of people,
who is applying), PositionID (what they're applying to), application date, and
other information about the application as an entity in its own right.

All of the
fields describe all of the attributes of a person.

What are some of these attributes? I find it difficult to imagine over 250
*discrete, atomic, nonrepeating* characteristics of a person that would be
needed. If you have (for example) fields for HighSchoolGradDate,
College1GradDate, College1Name, College2GradDate, College2Name,
College3GradDate, College3Name etc., then.... you're committing spreadsheet,
and need to split out these "attributes" into several related tables.

Since each table can only
hold 250 fields I had to span into a seperate table using a One to One
relationship. I like the append query idea by John Vinson but I am not to
familiar with that feature.

John Vinson- How should I go about using the Append query.

Let's get your table design fixed first, but... use the Query Design Window
and choose the "Append" query option. It lets you take data from one table and
insert it into another. However, doing so with your current design would be
*simply wrong* and would dig you deeper into an improper structure!

 

[Iram]

Thanks for your thourough response.
I say that I would need to duplicate most of the data because there are
minor things that would need to be changed such as the new date of applying
and the new Autonumber. Essentially we want to keep a complete record of
every time the person applies. We need the ability to go back to any
application and print it out wholey. Our Access application forms are setup
so that so that all 300 fields are on two forms. The forms were perfectly
desgined for both data entry and printing. So if I had normalization going on
with say 30 tables I don't understand how you could pull up each of the
applications since each of the 30 tables would have one to many
relationships...

Since you won't say what sort of attribute data you are storing I guess we
will have to take your word for it that there are about thirty discrete
entities, each with an average of 8-10 fields, that you are recording for
each person. It's hard to imagine what those might be. I'm glad I don't
need to give up that much personal information to any of the organizations
storing my data. I certainly would not do so voluntarily.

As John and others have mentioned, there should be one record for each person.
If you are duplicating all of the information about every person every time
they submit an application you will soon be lost in a maze of data. It is
possible to duplicate a record along with related subform records. I doubt
it will help, but here's a link:
http://allenbrowne.com/ser-57.html
You would have to adapt the code to accomodate the extra 25 or so tables

You worte that "when the same person comes in again we would like to
duplicate almost all of his
attributes." Why is that? What is wrong with the attributes from the first
time the person came in? Attributes subject to change can be stored, but
most likely as part of a related record. Other attributes such as
DateOfBirth are not going to change, so should be stored just once.

PieterLinden- I just read up on Normalization. Normalizing might make the
duplicating process more difficult. If I were to normalize my two existing
tables they would turn into about 30 tables. Just imagen running an append
query over that many tables....

Hello,

[quoted text clipped - 21 lines]

What business problem are you trying to solve? Maybe you should post some of
your structure... something's fishy.

--
Message posted via AccessMonster.com


.