cut and paste hierarchical data

P

Phil T.

Hi. I'm a pretty low-level user, so I'm sure this has been answered before,
but I can't seem to find it.


I have a database set up hierarchically with many one to many relationships.
A table called "site" sits at the top of the hierarchy with "Boxes" within
sites, "bags" within boxes, "items" within bags and so forth. All
relationships cascade and enforce referential integrity. Problem is that I
entered information about items in a bag under the wrong "site", but all
other information is correct. All I want to do is copy and paste the
information from one site to another without having to re-enter everything.
Can anyone help me?
 
K

krissco

Hi. I'm a pretty low-level user, so I'm sure this has been answered before,
but I can't seem to find it.

I have a database set up hierarchically with many one to many relationships.
A table called "site" sits at the top of the hierarchy with "Boxes" within
sites, "bags" within boxes, "items" within bags and so forth. All
relationships cascade and enforce referential integrity. Problem is that I
entered information about items in a bag under the wrong "site", but all
other information is correct. All I want to do is copy and paste the
information from one site to another without having to re-enter everything.
Can anyone help me?
Click to expand...
Problem is that I entered information about items in a bag under the wrong "site"
Click to expand...
Do you mean entered information about items in a bag in a box under
the wrong site?

It sounds to me like you need an update query.
update box
set siteID = {New SiteID}
where boxID = {The BoxID}

That seems too simple. I have probably misunderstood what you need.

-Kris
 
J

John W. Vinson

Hi. I'm a pretty low-level user, so I'm sure this has been answered before,
but I can't seem to find it.


I have a database set up hierarchically with many one to many relationships.
A table called "site" sits at the top of the hierarchy with "Boxes" within
sites, "bags" within boxes, "items" within bags and so forth. All
relationships cascade and enforce referential integrity. Problem is that I
entered information about items in a bag under the wrong "site", but all
other information is correct. All I want to do is copy and paste the
information from one site to another without having to re-enter everything.
Can anyone help me?
Click to expand...

Do you have a separate table for each level? If so you may be able to just run
an Append query to append the data into the correct table. This assumes more
knowledge than you've posted about the exact structure of the tables or their
relationships though!

John W. Vinson [MVP]
 
P

Phil T.

Yes, there is a separate table for each level of information and I just want
to move all of the information associated with one "bag" (or one "box") to
another site. It would be cool if there was a way to just kind of drag and
drop.
 
J

John W. Vinson

Yes, there is a separate table for each level of information and I just want
to move all of the information associated with one "bag" (or one "box") to
another site. It would be cool if there was a way to just kind of drag and
drop.
Click to expand...

The simplest way would be an Append query. I'd help you create it if I knew
anything about the structure of your tables, but I don't.

John W. Vinson [MVP]
 
P

Phil T.

Also, I should probably be a little clearer about how the relations between
the tables work:

Say you have a database with a bunch of hierarchical info. At the top of
the hierarchy is a "Site" table and a "Box" table. One site can contain many
boxes; one box can belong to only one site. Typical one to many relationship.


SiteID is an autonumbered primary key that joins the "Site" table to the
"Box" table. Now, I made a mistake and entered a record into the "Box" table
that is associated with the wrong Site (Therefore all the information below
the "box" table such as "bags" in the box and "items" within the bags are
also associated with the wrong "site").

All I want to do is take that one "Box" record and change it's association
(and therefore all the bags and items below it--also in similar one:many
relations) from the incorrect "Site" record to the correct one.

I'm not moving any data between tables, so it's my understanding that an
append query will not work, and I have an autonumbered "SiteID" pirmary key,
so just editing the "SiteID", or doing an update query will not work. Also,
I need to keep entering sites, so I can't turn the autonumber off because my
understanding is that once you do that you can't turn it on again.

Hope this is a little clearer. Thanks!!
 
K

krissco

Also, I should probably be a little clearer about how the relations between
the tables work:

Say you have a database with a bunch of hierarchical info. At the top of
the hierarchy is a "Site" table and a "Box" table. One site can contain many
boxes; one box can belong to only one site. Typical one to many relationship.

SiteID is an autonumbered primary key that joins the "Site" table to the
"Box" table. Now, I made a mistake and entered a record into the "Box" table
that is associated with the wrong Site (Therefore all the information below
the "box" table such as "bags" in the box and "items" within the bags are
also associated with the wrong "site").

All I want to do is take that one "Box" record and change it's association
(and therefore all the bags and items below it--also in similar one:many
relations) from the incorrect "Site" record to the correct one.

I'm not moving any data between tables, so it's my understanding that an
append query will not work, and I have an autonumbered "SiteID" pirmary key,
so just editing the "SiteID", or doing an update query will not work. Also,
I need to keep entering sites, so I can't turn the autonumber off because my
understanding is that once you do that you can't turn it on again.

Hope this is a little clearer. Thanks!!
Click to expand...

So, your schema looks like this:

Site: {SiteID [PrimaryKey], other fields. . . }
Box: {BoxID [PK], SiteID [ForeignKey], other fields . . .}
Bag: {BagID [PK], BoxID [FK], other fields . . .}
Item: {ItemID [PK], BagID [FK], other fields . . .}

If that is the case, see my original post. You need an update query.

-Kris
 
J

Jason Lepack

Is there not a form so that you can do all this?

Is your data structured like this:

Sites:
site_id
site_name

Boxes:
box_id
site_id
box_info

Bags:
bag_id
box_id
bag_info

Items:
item_id
bag_id
item_info

If your data is structured in this fashion then all you need to do is
change the site_id in the Boxes table to point to the proper site.

You need to create an update query that will be like this:
UPDATE
Boxes
SET
site_id = (
SELECT
site_id
FROM
Sites
WHERE
site_name = the_right_site_name)
WHERE
Boxes.box_info = the_info_for_the_bad_box

Cheers,
Jason Lepack
 
P

Phil T.

Hi guys. Got it! Thanks for all of your help. I learned how to do the
update query, but the solution was actually much simpler than that. Problem
was, I had all my data dumped out into one big query and was trying to change
Site.SiteID (primary key) , which I obviously could not do, instead of
Box.SiteID (FK). whoops, lesson is pay attention to which field you're
actually trying to update!
 
Top