Combine data

  • Thread starter Thread starter Ohio5
  • Start date Start date
O

Ohio5

I have two tables that are identical with regard to the
fields and format. The first table is data that I bring
in from another database although there are multiple rows
of the same ID since the Comments field has multiple
comments and they can not be pulled any other way with the
way the database with the data is setup.

So the idea is to either append or update to the other
table based on the ID#. So basically in one module I want
to do an if then I guess, If the ID# exists then
concatenate the Comments field, if the ID# does not exist
then append the entire record to the table.

Can someone help me get started with this. I know what I
want to do in theory but putting it to work is a different
story.

Thanks!!!!!
 
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...
 
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));
 
Back
Top