See Subject:Combine Data~Still Need Help from 4/13/04'

I really need help on this

do you have a specific question to a particular question or is it still the
entire issue?

"Ohio5" <(E-Mail Removed)> wrote in message
news:1831601c4228b$72b660d0$(E-Mail Removed)...
> See Subject:Combine Data~Still Need Help from 4/13/04'
>
> I really need help on this

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" <(E-Mail Removed)> wrote in message
news:1831601c4228b$72b660d0$(E-Mail Removed)...
> See Subject:Combine Data~Still Need Help from 4/13/04'
>
> I really need help on this

Here is the original messages:

Subject: Combine Data~Still Need Help
From: "Ohio5" <(E-Mail Removed)> 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" <(E-Mail Removed)> 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));

>-----Original Message-----
>I would break down what you want into two steps:
>
>step 1)
>
>Update data for records that EXIST in the table.
>
>step 2)
>Add all new records that do NOT exist in the table.
>
>By breaking the above task into two steps...it is easier
to write the code.
>
>We are going from table1...to our resulting
table2..right?
>
>' to code this..we build a query with the id of table2
(drop in table2 to
>the query builder, and drag ID to the grid). Now, drop
in our table1, and
>then draw a join line from table2 to table1 (which way
you draw this line is
>critical). Set the join up so that a value must exist in
each table. Now,
>drag in the comments field from this table1. Also, now
set the order of the
>query by id (table2). Lets save this a qryImportComments.
>
>At this point, we get a one to many data set. Each
table2 ID will now show
>all of the comments from table1. Run the query..make
sure it looks
>ok..good..once you get the query working...close it.
>
>It is a simply matter to write some code that process
this information
>
>dim rstTable2Results as dao.recordset
>dim rstImport as dao.recordset
>
>dim lngCurrentId as long
>dim lngLastId as long
>
>dim strComments as string
>
>' open our results table (main table were target data
goes)
>
>set rstTable2Result = currentdb.OpenRecordSet("Table2")
>
>' now process our cool query we just made...
>set rstImport = currentdb.OpenRecordSet
("qryImportComments")
>
>do while rstImport.Eof = false
> if rstImport!id <> lngCurrentId then
> if strComments <> "" then
> rstTable2Result.FindFirst "id = " & lngCurrentID
> rstTAble2Result.Edit
> rstTable2Result!Comments = strComments
> end if
> lngCurrentId = rstImport!ID
> strComments = rstImport!Comments
> else
> if strComments <> "" then
> strComments = strComments & ","
> endif
> strComments = strComments & rstImport!Comments
> endif
> rstImport.MoveNext
>loop
>
>' the above code does the first step.
>
>The 2nd step does not need looking code...but can be
done with sql.....
>
>Simple make a query that joins from table1 (import) to
table 2. Do left join
>on the id field, and put in a condition for table2 id of:
>
>is null
>
>Now, in the query builder..change the query to a append
query..and set all
>the fields you want. Now...just run the query...
>
>currentdb.execute "yourcoolquery"
>
>The above code is as I type this (air code)..so it is a
bit rought...but
>quite close to what you need...
>
>--
>Albert D. Kallal (MVP)
>Edmonton, Alberta Canada
>(E-Mail Removed)
>http://www.attcanada.net/~kallal.msn
>
>
>.
>
...


..


>-----Original Message-----
>do you have a specific question to a particular question
or is it still the
>entire issue?
>
>"Ohio5" <(E-Mail Removed)> wrote in message
>news:1831601c4228b$72b660d0$(E-Mail Removed)...
>> See Subject:Combine Data~Still Need Help from 4/13/04'
>>
>> I really need help on this
>
>
>.
>

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
>
>
>
>"Ohio5" <(E-Mail Removed)> wrote in message
>news:1831601c4228b$72b660d0$(E-Mail Removed)...
>> See Subject:Combine Data~Still Need Help from 4/13/04'
>>
>> I really need help on this
>
>
>.
>

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
>>
>>
>>
>>"Ohio5" <(E-Mail Removed)> wrote in
message
>>news:1831601c4228b$72b660d0$(E-Mail Removed)...
>>> See Subject:Combine Data~Still Need Help from 4/13/04'
>>>
>>> I really need help on this
>>
>>
>>.
>>
>.
>

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" <(E-Mail Removed)> wrote in message
news:18f4201c423b6$e6e23160$(E-Mail Removed)...
> 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
> >>
> >>
> >>
> >>"Ohio5" <(E-Mail Removed)> wrote in
> message
> >>news:1831601c4228b$72b660d0$(E-Mail Removed)...
> >>> See Subject:Combine Data~Still Need Help from 4/13/04'
> >>>
> >>> I really need help on this
> >>
> >>
> >>.
> >>
> >.
> >

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" <(E-Mail Removed)> wrote in message
>news:18f4201c423b6$e6e23160$(E-Mail Removed)...
>> 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
>> >>
>> >>
>> >>
>> >>"Ohio5" <(E-Mail Removed)> wrote in
>> message
>> >>news:1831601c4228b$72b660d0$(E-Mail Removed)...
>> >>> See Subject:Combine Data~Still Need Help from
4/13/04'
>> >>>
>> >>> I really need help on this
>> >>
>> >>
>> >>.
>> >>
>> >.
>> >
>
>
>.
>

On Sat, 17 Apr 2004 05:33:56 -0700, "Ohio5"
<(E-Mail Removed)> wrote:

>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?

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public

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.

Ohio5 wrote:
>
> 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" <(E-Mail Removed)> wrote in message
> >news:18f4201c423b6$e6e23160$(E-Mail Removed)...
> >> 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
> >> >>
> >> >>
> >> >>
> >> >>"Ohio5" <(E-Mail Removed)> wrote in
> >> message
> >> >>news:1831601c4228b$72b660d0$(E-Mail Removed)...
> >> >>> See Subject:Combine Data~Still Need Help from
> 4/13/04'
> >> >>>
> >> >>> I really need help on this
> >> >>
> >> >>
> >> >>.
> >> >>
> >> >.
> >> >
> >
> >
> >.
> >