Append Query with relational databases?

[Guest]

Hi,

I have 5 databases. Each contains 2 tables (BirdData and SiteData) which are
related (one to many) via a field in one to a primary key in the other.

I need to merge these using an append query so that the 5 BirdData tables
are in 1 table, and the 5 SiteData tables are in 1 table, while maintaining
the relationships.

I have done simple append queries when there are no relationships, but I
believe this is quite a bit more complicated, especially since the primary
key is involved. I would appreciate any help at all.

Thanks in advance.

 

[Bob Barrows [MVP]]

Hi,

I have 5 databases. Each contains 2 tables (BirdData and SiteData)
which are related (one to many) via a field in one to a primary key
in the other.

I need to merge these using an append query so that the 5 BirdData
tables are in 1 table, and the 5 SiteData tables are in 1 table,
while maintaining the relationships.

I have done simple append queries when there are no relationships,
but I believe this is quite a bit more complicated, especially since
the primary key is involved. I would appreciate any help at all.

Thanks in advance.

It sounds as if you are overcomplicating this task, unless there are
autonumber fields involved.
I can't tell from your description* which table is the master table. But in
the absence of autonumber fields, it should simply be a matter of appending
all the master records into the master table first. Then append all the
child records into the child table. If this is a one-time task, then simply
manually create the queries against the 5 tables (which I assume you have
linked into a single database ... ). You could get fancy and use a single
insert statement using a union query:

insert into new_birddata (fieldlist)
select fieldlist from birddata1
union all fieldlist from birddata2
etc.

Bob Barrows

*Wordy descriptions rarely suffice. Nothing beats a list of relevant field
names and datatypes

 

[Guest]

Hi Bob,

The problem, I believe, is that the primary key in SiteData, which is the
link in the one-to-many join to a field ("SiteData_FK") in the BirdData
table, is an autonumber. I'm not sure how this will affect either the append,
or the relationships.

As the 5 databases are identical in structure (they all have the 2 tables,
the same fields in each table, and the same relationships), I was just going
to use the first one ("Database1") as the Master, to which the other 4 would
be appended to.

I have attempted this task using two dummy databases of the same structure,
with no success (the error message refers to a key violation). I'm not sure
what you mean by 'linking' the tables into a single database...I did copy
them into a single database to perform the append? This is a one-time task,
so nothing fancy required.

Hope that makes sense to you, so you can make sense of this to me!
Your help is much appreciated.
Thanks,
Theresa

 

[Bob Barrows [MVP]]

Hi Bob,

The problem, I believe, is that the primary key in SiteData, which is
the link in the one-to-many join to a field ("SiteData_FK") in the
BirdData table, is an autonumber. I'm not sure how this will affect
either the append, or the relationships.

Yes, that is the problem. You probably have 4 records (there are 4 source
tables, correct?) each of which have a value of 1 (for example) in the
SiteData primary key field.

What you are going to have to do is add a column to your destination table
that identifies the source of the data, and include that field in that
table's primary key. Get back to us if you don't know how to create
multi-field keys and indexes.

I'm not sure what you mean by 'linking' the tables into a
single database...I did copy them into a single database to perform
the append?

That's another way to get all your tables into the same database.

Bob Barrows

 

[Guest]

What you are going to have to do is add a column to your destination table
that identifies the source of the data, and include that field in that
table's primary key. Get back to us if you don't know how to create
multi-field keys and indexes.

That's great - and makes sense...although I don't know how to create the
multi-field keys and indexes.
Each table has a field "transcriber" which has the person's initials who
entered the data - I could use that in combination with the primary key as
the multi-field key. I tried to select "transcriber" in the table's design
view and hit the Primary key icon, yet I get an error message:
'You can't change the primary key. This table is the primary table in one or
more relationships. If you want to change or remove the primary key, first
delete the relationship in the Relationships window.'

So I'm obviously doing something wrong...advice?

Thanks,
Theresa

 

[Bob Barrows [MVP]]

That's great - and makes sense...although I don't know how to create
the multi-field keys and indexes.
Each table has a field "transcriber" which has the person's initials
who entered the data - I could use that in combination with the
primary key as the multi-field key. I tried to select "transcriber"
in the table's design view and hit the Primary key icon, yet I get an
error message: 'You can't change the primary key. This table is the
primary table in one or more relationships. If you want to change or
remove the primary key, first delete the relationship in the
Relationships window.'

So I'm obviously doing something wrong...advice?

Open the Relationships window and delete the relationship (you will recreate
it later).
Open your table in Design View, click into the field that is the current
primary key, and click the Primary Key toolbar button to remove the primary
key symbol from the field.Then hold the Ctrl key down to enable you to
select both fields that need to comprise the primary key and click the
toolbar button. The symbol should appear in from to both fields.

You will need to do this in the other table as well in order to create the
new relationship between the tables. Let me know if this makes sense.

 

[Guest]

Open the Relationships window and delete the relationship (you will recreate
it later).
Open your table in Design View, click into the field that is the current
primary key, and click the Primary Key toolbar button to remove the primary
key symbol from the field.Then hold the Ctrl key down to enable you to
select both fields that need to comprise the primary key and click the
toolbar button. The symbol should appear in from to both fields.

You will need to do this in the other table as well in order to create the
new relationship between the tables. Let me know if this makes sense.

Hi again,

I managed to delete the relationships, and I now have 2 fields in SiteData
that are the primary key. Yet when I try to re-establish the relationship (a
one-to-many with referential integrity), the error message I'm now getting is
"No unique index found for the referenced field of the primary table". Is it
because I'm trying to form a relationship between my SiteData table, which
has the multi-index field as the primary key ('Primary Key' - numerical, and
'Transcriber' - text) to a single index field ('SiteData_FK' - numerical) in
the BirdData table? If so, I added a new field 'Transcriber' in the BirdData
table, and made that a part of the primary key as well (multi-field index).
This doesn't seem to be working still.

Am I right in saying I need to first sort out the primary keys and the
relationships before I merge my tables? Or maybe the problem is that I need
to do the merge first, and then establish the primary keys and relationships?

If you can solve this problem solve, great. I can also send you a screen
capture of the relationships and the error message to your personal e-mail,
if it would help.

Thanks again for all your help.
Theresa

 

[Guest]

Yay! I think I got it.
The error message was popping up because there was a null value in one of
the primary keys - once that was fixed, the relationship was created without
a problem. I then performed an append query - parent tables first, then child
tables - and all seems well.

Thanks so much for your help!
Cheers,
Theresa