|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
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 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
"Jolene L.U. Smith" <JoleneLUSmith[ at ]discussions.microsoft.com> wrote in
message news:ED91AB03-716B-4073-883D-6AE1A8C453D2[ at ]microsoft.com...
[Quoted Text] >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 Updike
> Virginia Department of Historic Resources
|
|
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
"Jeff Boyce" wrote:
[Quoted Text] > 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" <JoleneLUSmith[ at ]discussions.microsoft.com> wrote in
> message news:ED91AB03-716B-4073-883D-6AE1A8C453D2[ at ]microsoft.com...
> >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 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
"Jolene L.U. Smith" <JoleneLUSmith[ at ]discussions.microsoft.com> wrote in
message news:9849F79E-43E5-419C-B0B2-E990557890B8[ at ]microsoft.com...
[Quoted Text] > 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
>
>
> "Jeff Boyce" wrote:
>
>> 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" <JoleneLUSmith[ at ]discussions.microsoft.com> wrote in
>> message news:ED91AB03-716B-4073-883D-6AE1A8C453D2[ at ]microsoft.com...
>> >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 Updike
>> > Virginia Department of Historic Resources
>>
>>
>>
|
|
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
"Jeff Boyce" wrote:
[Quoted Text] > 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
>
> "Jolene L.U. Smith" <JoleneLUSmith[ at ]discussions.microsoft.com> wrote in
> message news:9849F79E-43E5-419C-B0B2-E990557890B8[ at ]microsoft.com...
> > 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
> >
> >
> > "Jeff Boyce" wrote:
> >
> >> 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" <JoleneLUSmith[ at ]discussions.microsoft.com> wrote in
> >> message news:ED91AB03-716B-4073-883D-6AE1A8C453D2[ at ]microsoft.com...
> >> >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 Updike
> >> > Virginia Department of Historic Resources
> >>
> >>
> >>
>
>
>
|
|
On Tue, 1 Aug 2006 11:22:13 -0700, "Jeff Boyce"
<nonsense[ at ]nonsense.com> wrote:
[Quoted Text] >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]
|
|
Making sure that the order of the three fields was the same
in each primary key.
[Quoted Text] > A Query joining the two tables on those three fields will
> then be sure to be updateable.
(david)
"John Vinson" <jvinson[ at ]STOP_SPAM.WysardOfInfo.com> wrote in message
news:be8vc2lt5f7mthvdvlra9md9kv58ftvtjt[ at ]4ax.com...
> On Tue, 1 Aug 2006 11:22:13 -0700, "Jeff Boyce"
> <nonsense[ at ]nonsense.com> wrote:
>
>>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]
|
|
'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)
"Jolene L.U. Smith" <JoleneLUSmith[ at ]discussions.microsoft.com> wrote in
message news:ED91AB03-716B-4073-883D-6AE1A8C453D2[ at ]microsoft.com...
[Quoted Text] >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 Updike
> Virginia Department of Historic Resources
|
|
|