Combine Data~Still Need Help

[Ohio5]

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));

-----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 address removed)
http://www.attcanada.net/~kallal.msn


.

..