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".

 

[J_Goddard via AccessMonster.com]

Hi -

When you have one record containing 300 fields (even if it is in two tables,
as you have), it strongly suggests that your data is not properly structured,
i.e. is not properly normalized. What data is stored in those 300 fields?

John

 

[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.

 

[PieterLinden via AccessMonster.com]

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

tangential comment...

What entity takes 300 columns to describe? Sounds like a normalization issue.
What business problem are you trying to solve? Maybe you should post some of
your structure... something's fishy.

 

[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

 

[J_Goddard via AccessMonster.com]

Hi -

How do you link the two tables for use in a query? If you are using the PK,
which is an autonumber in both tables, then STOP!!! There is NO guarantee
that you will get the same autonumber in both tables when you add new records.


What you need is an autonumber PK in one table, and a Long PK in the other,
which you would set using code. But, the PK issue does not arise if you have
another field (e.g. SSN) in both tables which uniquely identifies a person.

How to you have things set up?

John

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

Hi -

[quoted text clipped - 23 lines]

 

[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!

 

[BruceM via AccessMonster.com]

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....
[quoted text clipped - 21 lines]

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

 

[tom_willpa]

high quality Soccer jerseys NBA Jersey tracksuit and jackets, GHD
hairstraightener supplier from www.willpa.com

Are you a Retail businessman who bother by the purchase price? China
Cheapest TOP wholesale website can help you

we are specialize in replica sport goods manufacturing in china, we can
offer you all kinds of soccer jersey, NBA jersey,shoes and so on. they are
the best brand replica goods whih are look the same as the original goods.
excellent quality and steady supply for them. we have been marketed in Europe
and American for 3 year. all the goods we offer are AAA quality. our soccer
jersey are Thailand style. If any goods you buy from my company have problem,
we will refund or resend them again. Most of ourProducts have no minimum
order requirements,soyou can shop retail goods at wholesale prices. if you
can buy more than 300usd. We offer free shipping. The more you buy the more
discount for you.

National soccer jerseys: http://www.willpa.com
Club soccer jerseys: http://www.willpa.com
NBA Jerseys: http://www.willpa.com
T-shirt and shirt: http://www.willpa.com
Tracksuit: http://www.willpa.com
Hoody & Jackets: http://www.willpa.com
UGG boots: http://www.willpa.com
Hair style: http://www.willpa.com
shopping Index: http://www.willpa.com

EMS shipping. 7days arrive, paypal accept

want more information pls contact us or check our website: www.willpa.com

 

[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.

--



.

 

[BruceM via AccessMonster.com]

As several people have tried to explain, typically you store a person's
information once, then relate other records to that single record. If you
were managing employee payroll or training records would you duplicate
complete employee information for each paycheck or training session? If so,
you have a fundamental misunderstanding about how relational databases work.

Since you say the forms are perfectly set up for data entry and printing I
would guess you are not much interested in the suggestion that reports are
much better suited than forms for printing. Also, you are resistant to
describing the attribute data you think must be duplicated, and have stated
that everything about the design is satisfactory, so there is no way to
suggest another approach that may make things easier. I can only refer you
to John's suggestion that you need to use two append queries, one for each
table. Good luck.

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

[quoted text clipped - 28 lines]