Copy data from one table to another

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi there,

I have been tasked with setting up an Access database to track 4 similar
(but different) entities. Under certain circumstances entities B & C could
metamorphose into entity A. Is it possible in Access 2002 to have a button on
an input form that will replicate most (but not all) fields from entity B (or
C) over to the table containing entity A?

Entity A will be indexed via an auto-number – will this add extra complexity
when trying to copy other entities over to it?

I’m familiar with database theory & basic Access but haven’t previously
designed & implemented a dbms in Access. Any responses gratefully received

Regards

Phil
 
Phil

?You have a separate table for each entity? Could you give an example?

Without more information, it's a little tough to imagine what you're
describing...

One possibility is that you could have one "entity" table with a field that
indicates "type".
 
Hi there,

I have been tasked with setting up an Access database to track 4 similar
(but different) entities. Under certain circumstances entities B & C could
metamorphose into entity A. Is it possible in Access 2002 to have a button on
an input form that will replicate most (but not all) fields from entity B (or
C) over to the table containing entity A?
Click to expand...

Yes; you could run an Append query.
Entity A will be indexed via an auto-number – will this add extra complexity
when trying to copy other entities over to it?
Click to expand...

It certainly will, if your separate tables have their own independent
autonumbers - the autonumber value in table B will certainly NOT be
appropriate for table A, and if there are any additional related
tables you'll have trouble keeping them linked.

This might be a case for the rather more advanced technique called
"Subclassing". This would have one master table with the fields in
common for entities A, B and C, related one-to-one to three tables,
one for each entity type.

John W. Vinson[MVP]
 
John/Jeff,

Thanks for your posts - certainly food for thought. Im thinking now that
I'll include entities A, B & C together in the same table and differentiate
them with a 'type' field (as suggested). I'll then have other tables for the
extra fields required for entities A, B & C, so that if the record changes
from type B to type A, the extra fields for type A appear in the form to be
completed by the user. Does this make sense and is it normal procedure in
Access?
 
Phil

I'm having trouble imagining if your (proposed) design is a good fit, mainly
because of a hyperactive imagination! I can imagine scenarios in which it
would NOT be a good idea.

Can you provide a simple set of sample data, as an example?

--
Regards

Jeff Boyce
<Office/Access MVP>
 
Hi Jeff,

OK then, I have a dbms to track building projects for the local council. I
need to keep track of the number of actual ‘Building Projects’ as well as
lesser pieces of work such as ’Maintenance’, or ‘Support to other Agencies’.
These then would be entities A, B & C but equally they could exist in one big
table (called ‘Work’ perhaps) with a field called ‘Type’ that would
distinguish between ‘Building Projects’, ’Maintenance’ or ‘Support to other
Agencies’. There may come a point, however, when a ‘Maintenance’ or ‘Support
to other Agencies’ job actually becomes a full scale ‘Building Project’ –
which is when I need to copy the data over to the Entity A ‘Building
Projects’ table OR simply change the type to ‘Building Project’ if I have
one big ‘Work’ table.

Problem is, although all these types of work have the same core fields (such
as ‘address’, ‘authorised by’, ‘start date’, ‘completion date’, ‘Area’ etc),
they also have their own fields specific to that work type. Eg the main
‘Building Projects’ will have a field for ‘Sub contractors’ and a ‘Priority’
field that the other types, or entities wont have. Also ‘Maintenance’ may
have fields specific to itself (such as Maintenance Class) which become
irrelevant if that ‘Maintenance’ subsequently becomes a ‘Building Project’ in
its own right.

Im assuming I shouldn’t try to capture *all* the data for *all* entities (or
types) in one large table (as there would be many blanks). However should I;

a. Have a main table that records all the fields that every entity has in
common & link to other smaller tables that include just the fields specific
to each entity
b. Keep the entities separate but then perhaps run into problems when I try
to duplicate a ‘Maintenance’ or ‘Support’ job over into a full scale
‘building project’ because the fields required are slightly different.

Does that make any sense at all?

Any input greatly received as this is keeping me awake at night!!

Regards

Phil
 
Phil

This is just one person's opinion, so take it with a pound of salt...

Another way to characterize what you've described as "entities" is as
'projects'. As suggested elsethread, you could use a single master table to
record 'projects', and use three related tables to record specifics about
the sub-class/type of project.

The one master table holds the common data, no copying around necessary.

The sub-class tables hold only information specific to that subclass/type,
plus the ID from the master table.

This approach would also have the benefit of allowing you to keep a history
of what various "levels" any given project went through, since you'd have a
single Master ID showing up in one/more of the subclass tables.

Does that make sense?

--
Regards

Jeff Boyce
<Office/Access MVP>
 
Back
Top