Generating a PK with autonumber- FRUSTRATION!

[Jolene L.U. Smith]

I am trying to join 2 tables [in a very poorly designed database] in order to
restore the entire contents of one field in a backup table to that same field
(Yes/No type) in my new table. I have tried an update query, but I keep
getting an error ("not enough temporary disk space...").

I've given up on that approach, after MUCH troubleshooting.

My next idea was to just do it by hand- after first creating a query that
returns all the records in the new table that correspond to the records in
the backup table where there is a YES in the field. This probably wouldn't
be so difficult if the original brilliant designer had assigned a PK, but
that never happened.

Now, when I try to add autonumber PKs to both tables, they both get arranged
differently, even if I sort them the same way first. Why?

Do I have any other options to fix the original mistake?

 

[Jeff Boyce]

Jolene

Without more description of what data elements are in each table and the
relationship between the tables, it's a bit tough to offer specifics.

Are you saying you expect two tables to have identical PKs, based on
Autonumber datatypes? If so, it won't happen! Autonumbers happen
independently in each table, and are NOT related between tables.

Are you saying that one table doesn't have a PK, and the second table would
use the first table's PK as a foreign key? What is the relationship between
the tables?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jolene L.U. Smith]

Here are some more details: The tables I am working with are called
tblArtifact
tblArtifactBackup


I accidentally deleted an entire field ("mends"- Yes/No) that I still need
in tblArtifact, but I need to retain the changes in the other records, while
replacing the deleted field's data with that from tblArtifactBackup.

The two tables started out as identical, now some records in tblArtifact
have been updated, but none have been added or deleted. Neither one has a
primary key (a combination of 3 fields makes each record unique). Because of
this fact, I'm having a hard time joining the tables.

Can I create my own sequential numbers to apply after I sort (so the records
in both tables will be in the same order)? I have no idea where to begin
with the code.

The end product that I need is a query that shows every record in
tblArtifact that has data (YES) in tblArtifactBackup.mends. I also need to
display tblArtifact.mends (which is currently empty) so I can check all the
boxes to YES.

Does this make any sense? It's just some cockamamy scheme I came up with
because I'm having so much trouble with the original update query.


--
Jolene Updike
Virginia Department of Historic Resources

Jolene

Without more description of what data elements are in each table and the
relationship between the tables, it's a bit tough to offer specifics.

Are you saying you expect two tables to have identical PKs, based on
Autonumber datatypes? If so, it won't happen! Autonumbers happen
independently in each table, and are NOT related between tables.

Are you saying that one table doesn't have a PK, and the second table would
use the first table's PK as a foreign key? What is the relationship between
the tables?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I am trying to join 2 tables [in a very poorly designed database] in order
to
restore the entire contents of one field in a backup table to that same
field
(Yes/No type) in my new table. I have tried an update query, but I keep
getting an error ("not enough temporary disk space...").

I've given up on that approach, after MUCH troubleshooting.

My next idea was to just do it by hand- after first creating a query that
returns all the records in the new table that correspond to the records in
the backup table where there is a YES in the field. This probably
wouldn't
be so difficult if the original brilliant designer had assigned a PK, but
that never happened.

Now, when I try to add autonumber PKs to both tables, they both get
arranged
differently, even if I sort them the same way first. Why?

Do I have any other options to fix the original mistake?

 

[Jeff Boyce]

Jolene

If you have a three-field combination in each table that guarantees unique
records, why not join on those three fields? You could join on all three
and do whatever update query(s) you needed.

You wouldn't need to invent a Primary Key, autonumber or otherwise.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Here are some more details: The tables I am working with are called
tblArtifact
tblArtifactBackup


I accidentally deleted an entire field ("mends"- Yes/No) that I still need
in tblArtifact, but I need to retain the changes in the other records,
while
replacing the deleted field's data with that from tblArtifactBackup.

The two tables started out as identical, now some records in tblArtifact
have been updated, but none have been added or deleted. Neither one has a
primary key (a combination of 3 fields makes each record unique). Because
of
this fact, I'm having a hard time joining the tables.

Can I create my own sequential numbers to apply after I sort (so the
records
in both tables will be in the same order)? I have no idea where to begin
with the code.

The end product that I need is a query that shows every record in
tblArtifact that has data (YES) in tblArtifactBackup.mends. I also need
to
display tblArtifact.mends (which is currently empty) so I can check all
the
boxes to YES.

Does this make any sense? It's just some cockamamy scheme I came up with
because I'm having so much trouble with the original update query.


--
Jolene Updike
Virginia Department of Historic Resources

Jolene

Without more description of what data elements are in each table and the
relationship between the tables, it's a bit tough to offer specifics.

Are you saying you expect two tables to have identical PKs, based on
Autonumber datatypes? If so, it won't happen! Autonumbers happen
independently in each table, and are NOT related between tables.

Are you saying that one table doesn't have a PK, and the second table
would
use the first table's PK as a foreign key? What is the relationship
between
the tables?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I am trying to join 2 tables [in a very poorly designed database] in
order
to
restore the entire contents of one field in a backup table to that same
field
(Yes/No type) in my new table. I have tried an update query, but I
keep
getting an error ("not enough temporary disk space...").

I've given up on that approach, after MUCH troubleshooting.

My next idea was to just do it by hand- after first creating a query
that
returns all the records in the new table that correspond to the records
in
the backup table where there is a YES in the field. This probably
wouldn't
be so difficult if the original brilliant designer had assigned a PK,
but
that never happened.

Now, when I try to add autonumber PKs to both tables, they both get
arranged
differently, even if I sort them the same way first. Why?

Do I have any other options to fix the original mistake?

 

[Jolene L.U. Smith]

Duh! I forgot that joining on multiple fields was a possibility. Now I have
the correct results, but I can't edit records within the query. Any idea
why, or how I can?

THANK YOU


--
Jolene Updike
Virginia Department of Historic Resources

Jolene

If you have a three-field combination in each table that guarantees unique
records, why not join on those three fields? You could join on all three
and do whatever update query(s) you needed.

You wouldn't need to invent a Primary Key, autonumber or otherwise.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Here are some more details: The tables I am working with are called
tblArtifact
tblArtifactBackup


I accidentally deleted an entire field ("mends"- Yes/No) that I still need
in tblArtifact, but I need to retain the changes in the other records,
while
replacing the deleted field's data with that from tblArtifactBackup.

The two tables started out as identical, now some records in tblArtifact
have been updated, but none have been added or deleted. Neither one has a
primary key (a combination of 3 fields makes each record unique). Because
of
this fact, I'm having a hard time joining the tables.

Can I create my own sequential numbers to apply after I sort (so the
records
in both tables will be in the same order)? I have no idea where to begin
with the code.

The end product that I need is a query that shows every record in
tblArtifact that has data (YES) in tblArtifactBackup.mends. I also need
to
display tblArtifact.mends (which is currently empty) so I can check all
the
boxes to YES.

Does this make any sense? It's just some cockamamy scheme I came up with
because I'm having so much trouble with the original update query.


--
Jolene Updike
Virginia Department of Historic Resources

Jolene

Without more description of what data elements are in each table and the
relationship between the tables, it's a bit tough to offer specifics.

Are you saying you expect two tables to have identical PKs, based on
Autonumber datatypes? If so, it won't happen! Autonumbers happen
independently in each table, and are NOT related between tables.

Are you saying that one table doesn't have a PK, and the second table
would
use the first table's PK as a foreign key? What is the relationship
between
the tables?

Regards

Jeff Boyce
Microsoft Office/Access MVP


message I am trying to join 2 tables [in a very poorly designed database] in
order
to
restore the entire contents of one field in a backup table to that same
field
(Yes/No type) in my new table. I have tried an update query, but I
keep
getting an error ("not enough temporary disk space...").

I've given up on that approach, after MUCH troubleshooting.

My next idea was to just do it by hand- after first creating a query
that
returns all the records in the new table that correspond to the records
in
the backup table where there is a YES in the field. This probably
wouldn't
be so difficult if the original brilliant designer had assigned a PK,
but
that never happened.

Now, when I try to add autonumber PKs to both tables, they both get
arranged
differently, even if I sort them the same way first. Why?

Do I have any other options to fix the original mistake?

 

[John Vinson]

If you have a three-field combination in each table that guarantees unique
records, why not join on those three fields? You could join on all three
and do whatever update query(s) you needed.

You wouldn't need to invent a Primary Key, autonumber or otherwise.

Or, if the three fields are in fact jointly unique, you could
ctrl-click all three fields in table design view and click the Key
icon, to create a three-field primary key. A Query joining the two
tables on those three fields will then be sure to be updateable.

John W. Vinson[MVP]

 

[david epsom dot com dot au]

Making sure that the order of the three fields was the same
in each primary key.

A Query joining the two tables on those three fields will
then be sure to be updateable.

(david)

 

[david epsom dot com dot au]

'sorting' them doesn't change the table, just the display.

If you want to change the order of the items in the table,
you need to make the sort order columns the 'primary key',
then compact the database.

Compacting the database will put the data into the order
of the primary key, and adding an autonumber column will
then autonumber in that order.

(david)