Update query question ??

[steve]

I have been trying and trying to I update a database. It just doesnt
seem to work the way I think it is suppose to.

Im not using any sql code just the tools etc that come with access
2000.

What I want to do is update an old table with new data.

I want the changed data to overwrite the old data and any new records
to be appended to the update. I dont know where to begin with my
questions becasue I think I would have to type for so long to try to be
detailed. I have tryed to follow the instructions from MS but i just
cant get it right.

But let me ask a simple question. If you update an old table with a new
table, does it add new records also or does it just change the old
records to the new ones. and any new records it does not add. Maybe
what I have to do is an update and then append New records.

Can anyone confirm if this is the bahvour.

Thanks.

 

[Larry Daugherty]

You're on the right track. Assuming that you actually want to keep
the existing records but to modify data that meets some criteria then
Update. If you don't want any of the old data then run a Delete
query. To Append new records then ... You get it.

HTH

 

[steve]

You're on the right track. Assuming that you actually want to keep
the existing records but to modify data that meets some criteria then
Update. If you don't want any of the old data then run a Delete
query. To Append new records then ... You get it.

HTH

mmm now thats an interesting appoach. I really dont need any of the old
data. I could just delete it all. Of course off of of the "top" table
there are sub data tables that I dont want to change. If I key on one
field that is unique can I delete the top table and keep all the sub
tables linked to each record?

Basically the top table data changes but the sub table data does need
to be related to the same records each time.

Regards

 

[Larry Daugherty]

No! I have to assume that you have relationships between the tables
and if you delete the original records on the "one" side of a
one-to-many relationship you'll have some consequences to address.
Only if you're sure that there are no relationships should you do the
deletes.

What ever you do, make a backup of your complete application and then
take a copy off to a sandbox area to play with it.

Now, if there are no relationships between your "top" tables and the
others then your schema probably needs to be revisited.

Access a Relational Database Management System (RDBMS. Actually that
is more Jet, the system underlying Access applications by default
happens to be. The power realized by using such a system is that you
can relate entities in your application in meaningful ways.

If you post back please include the names of all of your tables and
the names and datatypes of each field. A separate paragraph for each
table, please. That would give us a chance to see what you really
have to deal with.

On the other hand, that's far afield from your original issue ...

HTH

 

[steve]

One of the things I don't understand is the seemingly opposite way
that the update query works.

Here is what I mean.

I have a table called tblLoginfo . This is the main volatile database
that I want to change regularly.It has many fields "Date" "Time"
"UserName" "ComputerName" etc. Beneath it are sub-tables such as
tblServiceRecords these I don't want to update. We have a program
that logs our computer hardware and puts it into a file called
loginfo.log . So what I'm trying to do is have service records that
related to each record in the tblLoginfo database. The Key is set on
the drive name. Each system has drive name. The drive name remains the
same but other info, eg increase memory, disk space left all changes.
So we just want to update this info. I think I have figured out how to
do this. Partially with your assistance. The problem is that it
doesn't work the way I think it should. Obviously it's some block I
have about how it should work.

I tell you all the previous info to give some understanding of what
I'm doing. But here is the REAL QUESTION.

I have made an update query. I have put in the query two tables. The
"tblLoginfo" and "New-Loginfo". This is the latest updates from
loginfo.log I have linked one field that never changes.

I have the following in the design fields.
Field: Date
Table: tblLoginfo
Update To: [New-Loginfo].date

When I run this update query it updates the "tblLoginfo".
Shouldn't it update the table "New-Loginfo" ??

I have tested this many times and it seems to always update the
tblLogInfo. This is actually what I want but it doesn't seem to make
sense since it says:
"Update To: [New-loginfo].date , the Other table.

Thanks.

 

[Larry Daugherty]

Maybe understand it as
"Update (your target table) to: [What you find over here]".

HTH

 

[steve]

Thanks Larry, For the clarification.

I'm Glad I got it right. I was thinking I was doing something odd.I
will try to reverse my thinking.

Regards