O
Ohio5
See Subject:Combine Data~Still Need Help from 4/13/04'
I really need help on this
I really need help on this
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
S
Steven
do you have a specific question to a particular question or is it still the
entire issue?
entire issue?
S
Steven
Run this query to update existing
UPDATE Final INNER JOIN [From] ON [Final].[ID] = [From].
[ID] SET Final.Journal = [Final].[Comment]+": " & [From].
[JournalDate] &"- "& [From].[Comment]
WHERE (((From.Comment) Is Not Null));
Run this query after you have run alberts update query to remove dups:
delete from [From] where [From].[ID] in (select distinct [Final].[ID] from
[Final])
You can then do an insert statement into final on the remainder and then
another delete from [From] to remove all the rest.
This assumes that while you are doing this, [from] isn't getting updated.
If it is, this won't work
UPDATE Final INNER JOIN [From] ON [Final].[ID] = [From].
[ID] SET Final.Journal = [Final].[Comment]+": " & [From].
[JournalDate] &"- "& [From].[Comment]
WHERE (((From.Comment) Is Not Null));
Run this query after you have run alberts update query to remove dups:
delete from [From] where [From].[ID] in (select distinct [Final].[ID] from
[Final])
You can then do an insert statement into final on the remainder and then
another delete from [From] to remove all the rest.
This assumes that while you are doing this, [from] isn't getting updated.
If it is, this won't work
O
Ohio5
Here is the original messages:
Subject: Combine Data~Still Need Help
From: "Ohio5" <[email protected]> Sent:
4/13/2004 6:03:51 PM
Below is the entire string from my first message and the
response from Albert, I am having such a hard time with
this and I don't know why. Any help would be appreciated.
Subject: Re: Combine data
From: "Ohio5" <[email protected]> Sent:
4/12/2004 11:13:31 AM
Few questions, I am trying to follow what you are doing
and not sure that I am following completed. The
following SQL works good for the update. Is there a way
that once the data has been updated to delete from the
first table to eliminate duplication. The code that you
referred to that goes in a module and if I understand you
are calling that the coolquery?
SQL CODE for UPDATE:
UPDATE Final INNER JOIN [From] ON [Final].[ID] = [From].
[ID] SET Final.Journal = [Final].[Comment]+": " & [From].
[JournalDate] &"- "& [From].[Comment]
WHERE (((From.Comment) Is Not Null));
Subject: Combine Data~Still Need Help
From: "Ohio5" <[email protected]> Sent:
4/13/2004 6:03:51 PM
Below is the entire string from my first message and the
response from Albert, I am having such a hard time with
this and I don't know why. Any help would be appreciated.
Subject: Re: Combine data
From: "Ohio5" <[email protected]> Sent:
4/12/2004 11:13:31 AM
Few questions, I am trying to follow what you are doing
and not sure that I am following completed. The
following SQL works good for the update. Is there a way
that once the data has been updated to delete from the
first table to eliminate duplication. The code that you
referred to that goes in a module and if I understand you
are calling that the coolquery?
SQL CODE for UPDATE:
UPDATE Final INNER JOIN [From] ON [Final].[ID] = [From].
[ID] SET Final.Journal = [Final].[Comment]+": " & [From].
[JournalDate] &"- "& [From].[Comment]
WHERE (((From.Comment) Is Not Null));
O
OHIO5
You are the bomb that works, now I am working on the
append query. It should be an append query not a select
query correct?
append query. It should be an append query not a select
query correct?
-----Original Message-----
Run this query to update existing
UPDATE Final INNER JOIN [From] ON [Final].[ID] = [From].
[ID] SET Final.Journal = [Final].[Comment]+": " & [From].
[JournalDate] &"- "& [From].[Comment]
WHERE (((From.Comment) Is Not Null));
Run this query after you have run alberts update query to remove dups:
delete from [From] where [From].[ID] in (select distinct [Final].[ID] from
[Final])
You can then do an insert statement into final on the remainder and then
another delete from [From] to remove all the rest.
This assumes that while you are doing this, [from] isn't getting updated.
If it is, this won't work
Ohio5 said:See Subject:Combine Data~Still Need Help from 4/13/04'
I really need help on this
.
O
OHIO5
Have an issue. All the IDs that existed I concatenated
the journals and then deleted them, but now in the from
table I have one id multiple times but it does not exist
in the Final table so I can not append because it
violates the primary key.
Still need help~
the journals and then deleted them, but now in the from
table I have one id multiple times but it does not exist
in the Final table so I can not append because it
violates the primary key.
Still need help~
-----Original Message-----
You are the bomb that works, now I am working on the
append query. It should be an append query not a select
query correct?
to-----Original Message-----
Run this query to update existing
UPDATE Final INNER JOIN [From] ON [Final].[ID] = [From].
[ID] SET Final.Journal = [Final].[Comment]+": " & [From].
[JournalDate] &"- "& [From].[Comment]
WHERE (((From.Comment) Is Not Null));
Run this query after you have run alberts update query
remove dups:distinctdelete from [From] where [From].[ID] in (select
[Final].[ID] fromisn't[Final])
You can then do an insert statement into final on the remainder and then
another delete from [From] to remove all the rest.
This assumes that while you are doing this, [from]
getting updated..If it is, this won't work
.
S
Steven
If you put the ID record without the journal & comment into the Final table
and then re-run the update query, the final table should be updated with
each comment from the From table
and then re-run the update query, the final table should be updated with
each comment from the From table
OHIO5 said:Have an issue. All the IDs that existed I concatenated
the journals and then deleted them, but now in the from
table I have one id multiple times but it does not exist
in the Final table so I can not append because it
violates the primary key.
Still need help~-----Original Message-----
You are the bomb that works, now I am working on the
append query. It should be an append query not a select
query correct?
to-----Original Message-----
Run this query to update existing
UPDATE Final INNER JOIN [From] ON [Final].[ID] = [From].
[ID] SET Final.Journal = [Final].[Comment]+": " & [From].
[JournalDate] &"- "& [From].[Comment]
WHERE (((From.Comment) Is Not Null));
Run this query after you have run alberts update query
remove dups:distinctdelete from [From] where [From].[ID] in (select
[Final].[ID] fromisn't[Final])
You can then do an insert statement into final on the remainder and then
another delete from [From] to remove all the rest.
This assumes that while you are doing this, [from]
getting updated..If it is, this won't work
See Subject:Combine Data~Still Need Help from 4/13/04'
I really need help on this
.
O
Ohio5
UPDATE Final RIGHT JOIN [From] ON Final.ID = From.ID SET
[From].ID = [Final]![ID];
The above is what I was trying to run and it says that my
records will update but when I open the table the records
were not added.
[From].ID = [Final]![ID];
The above is what I was trying to run and it says that my
records will update but when I open the table the records
were not added.
-----Original Message-----
If you put the ID record without the journal & comment into the Final table
and then re-run the update query, the final table should be updated with
each comment from the From table
OHIO5 said:Have an issue. All the IDs that existed I concatenated
the journals and then deleted them, but now in the from
table I have one id multiple times but it does not exist
in the Final table so I can not append because it
violates the primary key.
Still need help~-----Original Message-----
You are the bomb that works, now I am working on the
append query. It should be an append query not a select
query correct?
-----Original Message-----
Run this query to update existing
UPDATE Final INNER JOIN [From] ON [Final].[ID] = [From].
[ID] SET Final.Journal = [Final].[Comment]+": " & [From].
[JournalDate] &"- "& [From].[Comment]
WHERE (((From.Comment) Is Not Null));
Run this query after you have run alberts update query to
remove dups:
delete from [From] where [From].[ID] in (select distinct
[Final].[ID] from
[Final])
You can then do an insert statement into final on the
remainder and then
another delete from [From] to remove all the rest.
This assumes that while you are doing this, [from] isn't
getting updated.
If it is, this won't work
See Subject:Combine Data~Still Need Help from 4/13/04'
I really need help on this
.
.
.
J
John Vinson
UPDATE Final RIGHT JOIN [From] ON Final.ID = From.ID SET
[From].ID = [Final]![ID];
The above is what I was trying to run and it says that my
records will update but when I open the table the records
were not added.
From is a reserved word (for the FROM clause of a SQL query) - does
changing the tablename make any difference?
J
John Spencer (MVP)
UPDATE Final RIGHT JOIN [From]
ON Final.ID = [From].ID
SET [From].ID = [Final].[ID];
I'm not sure that you can do this. You are joining on the same field you are
attempting to update. Also, I would be consistent in referencing fields. Your
example code is not - sometimes brackets, sometimes . vs !
As I said in the first place, I'm not sure this will work because you use the
field you are trying to update in the join clause.
Right now, I don't have time to test that belief, but I thought I would at least
raise the issue.
ON Final.ID = [From].ID
SET [From].ID = [Final].[ID];
I'm not sure that you can do this. You are joining on the same field you are
attempting to update. Also, I would be consistent in referencing fields. Your
example code is not - sometimes brackets, sometimes . vs !
As I said in the first place, I'm not sure this will work because you use the
field you are trying to update in the join clause.
Right now, I don't have time to test that belief, but I thought I would at least
raise the issue.
UPDATE Final RIGHT JOIN [From] ON Final.ID = From.ID SET
[From].ID = [Final]![ID];
The above is what I was trying to run and it says that my
records will update but when I open the table the records
were not added.
-----Original Message-----
If you put the ID record without the journal & comment into the Final table
and then re-run the update query, the final table should be updated with
each comment from the From table
OHIO5 said:Have an issue. All the IDs that existed I concatenated
the journals and then deleted them, but now in the from
table I have one id multiple times but it does not exist
in the Final table so I can not append because it
violates the primary key.
Still need help~
-----Original Message-----
You are the bomb that works, now I am working on the
append query. It should be an append query not a select
query correct?
-----Original Message-----
Run this query to update existing
UPDATE Final INNER JOIN [From] ON [Final].[ID] = [From].
[ID] SET Final.Journal = [Final].[Comment]+": " &
[From].
[JournalDate] &"- "& [From].[Comment]
WHERE (((From.Comment) Is Not Null));
Run this query after you have run alberts update query
to
remove dups:
delete from [From] where [From].[ID] in (select
distinct
[Final].[ID] from
[Final])
You can then do an insert statement into final on the
remainder and then
another delete from [From] to remove all the rest.
This assumes that while you are doing this, [from]
isn't
getting updated.
If it is, this won't work
message
See Subject:Combine Data~Still Need Help from 4/13/04'
I really need help on this
.
.
.
O
OHIO5
THANK YOU VERY MUCH, THE ONLY THING I HAD TO CHANGE WAS
THE ORDER OF THE SET. This works perfectly for what I
need now, because by just putting the IDs in the table I
can then go back and run my update and I get no
duplication it is absolutely perfect. THANK YOU VERY MUCH
THE ORDER OF THE SET. This works perfectly for what I
need now, because by just putting the IDs in the table I
can then go back and run my update and I get no
duplication it is absolutely perfect. THANK YOU VERY MUCH
-----Original Message-----
UPDATE Final RIGHT JOIN [From]
ON Final.ID = [From].ID
SET [From].ID = [Final].[ID];
I'm not sure that you can do this. You are joining on the same field you are
attempting to update. Also, I would be consistent in referencing fields. Your
example code is not - sometimes brackets, sometimes . vs !
As I said in the first place, I'm not sure this will work because you use the
field you are trying to update in the join clause.
Right now, I don't have time to test that belief, but I thought I would at least
raise the issue.
.UPDATE Final RIGHT JOIN [From] ON Final.ID = From.ID SET
[From].ID = [Final]![ID];
The above is what I was trying to run and it says that my
records will update but when I open the table the records
were not added.
should-----Original Message-----
If you put the ID record without the journal & comment into the Final table
and then re-run the update query, the final table
be updated witheach comment from the From table
Have an issue. All the IDs that existed I concatenated
the journals and then deleted them, but now in the from
table I have one id multiple times but it does not exist
in the Final table so I can not append because it
violates the primary key.
Still need help~
-----Original Message-----
You are the bomb that works, now I am working on the
append query. It should be an append query not a select
query correct?
-----Original Message-----
Run this query to update existing
UPDATE Final INNER JOIN [From] ON [Final].[ID] = [From].
[ID] SET Final.Journal = [Final].[Comment]+": " &
[From].
[JournalDate] &"- "& [From].[Comment]
WHERE (((From.Comment) Is Not Null));
Run this query after you have run alberts update query
to
remove dups:
delete from [From] where [From].[ID] in (select
distinct
[Final].[ID] from
[Final])
You can then do an insert statement into final on the
remainder and then
another delete from [From] to remove all the rest.
This assumes that while you are doing this, [from]
isn't
getting updated.
If it is, this won't work
message
See Subject:Combine Data~Still Need Help from 4/13/04'
I really need help on this
.
.
.