Jumat, 02 September 2011

RE: [MS_AccessPros] Compare, match and append rows in two tables

 

hi Sally,

adding on...

add a Long Integer field to Table2 (blank default value) with the same name as SomeID in tablename1

then, run an update to see if there are records in Table2 that are not in Table1

UPDATE [Tablename2] AS t2
INNER JOIN [Tablename1] as t1
ON t2.Field1 = t1.Field2
SET t2.SomeID = t1.SomeID

The records in table2 where SomeID Is NULL need to be appended...

Warm Regards,
Crystal

*
(: have an awesome day :)
*

--- On Fri, 9/2/11, Crystal <strive4peace> wrote:

> Hi Sally,
>
> welcome :)  Happy to have you join us
>
> adding on to what Bill said...
>
> Before you do anything, add these 2 tracking fields to
> Table1:
>
> - dtmAdd, date/time, DefaultValue --> =Now(), when
> record was created
> - dtmEdit, date/time, DefaultValue --> =Now(), when
> record was last edited
>
> while you are in there, add an AutoNumber field to your
> table for future relationships:
>
> - someID, AutoNumber
>
> WHERE
> 'some' is some descriptive word (unlike the other
> fieldnames) ... ie: ImportID if the table has no meaning
> other than to store imported data before it is put somewhere
> more permanent
>
> dtmEdit will have to be maintained in the database design
> or it won't have valid information.
>
> ~~~
> One thing you will have to be careful about is leading or
> trailing spaces.  It would be a good idea to trim them
> from your key field in each table using an UPDATE query
> BEFORE you link them ... just in case ...
>
> UPDATE [Tablename1] SET [Field4] = Trim([Field4])
> UPDATE [Tablename2] SET [Field2] = Trim([Field2])
>
> ~~~
> If you want to compare field by field, the only way to do
> that is one at a time. Therefore, you will need to run
> update queries a field at a time. 
>
> your SQL for the first field would look something like
> this:
>
> UPDATE [Tablename2] AS t2
> INNER JOIN [Tablename1] as t1
> ON t2.Field1 = t1.Field2
> SET t1.Field4 = trim(t1.[Field4])  & ("~"+
> trim(t2.[Field2]))
> , t1.dtmEdit = Now()
> WHERE nz(t1.[Field4]) <> nz(t2.[Field2])
>
> WHERE
> ~ is used to separate the values -- this can be changed of
> course
>
> ~~~
> on text fields, it would also be good to trim the
> comparison.  For example:
>
> WHERE trim(nz(t1.[Field4],"")) <>
> trim(nz(t2.[Field2],""))
>
> ~~~
>
> How will you handle fields that are different?  Append
> them?  This example just appends them.  If this is
> really what you think you want, look at fields that are
> modified before you get too far ...
>
> Maybe Flag them?  Write changes to another
> table? 
>
> ~~~
> Is this a one-time operation?  or will you do this on
> a regular basis?
>
> if one-time, it will be quickest to query differences for
> each field when the first word matches one at a time, even
> though you have so many. 
>
> If this is to be done enough times to make it worthwhile to
> write code that loops through all the fields, then you will
> need a mapping table to store the the fieldnames in each
> table that should match.
>
> ie:
>
> Mappings
> - MapID, autonumber
> - FldName1, text, 64 -- fieldname in table1
> - FldName2, text, 64 -- fieldname in table2
>
> then, in code, open both recordsets.  You will need to
> learn VBA to customize any starter code we give you. 
> Here is a good place to start:
>
> www.AccessMVP.com/strive4peace/VBA
>
> Warm Regards,
> Crystal
>
> Access Basics by Crystal (Bill Mosca's site)
> http://thatlldoit.com
> Free 100-page book that covers essentials in Access
>
> *
>    (: have an awesome day :)
> *
>
>
> --- On Fri, 9/2/11, Bill Mosca  wrote:
>
>
> > Hi Sally
> >
> > 
> >
> > Another Bear in Space! Say hi to Liz for me.
> >
> > 
> >
> > You can join the two tables on the matching field.
> Create
> > an append query with
> > the two tables joined and match up the fields putting
> > source fields in the grid
> > and the target fields in the "Append To" line under
> the
> > appropriate source
> > field.
> >
> > 
> >
> > Regards,
> > Bill Mosca,
> > Founder, MS_Access_Professionals
> > That'll do IT  <http://thatlldoit.com> http://thatlldoit.com
> > MS Access MVP
> >  <http://mvp.support.microsoft.com/profile/Bill.Mosca>
> > http://mvp.support.microsoft.com/profile/Bill.Mosca
> >
> > 
> >
> > From:Sally Vega
>
> >
> > Hi,
> >
> > I'm new to VBA and new to this group. My co-worker
> Liz
> > Ravenwood told me I'd get
> > great results if I posted my issue to this group!
> >
> > This is what I'm trying to do. I have two tables in
> > Access.
> >
> > Table one is in this order:
> >
> > Field2 Field4 Field5 Field7... up to 42 columns and
> over
> > 1000 rows.
> >
> > Table two is in this order:
> >
> > Field1 Field2 Field3 Field4... etc.
> >
> > In each table, th first row of each column has the
> word
> > that needs to match. All
> > the column data that matches in table two needs to be
> > appended to the matching
> > field in table one.
> >
> > Can this be done in Access or is it more of an Excel
> > issue?
> >
> > Thank you.
> >
> > Sally
>
>

__._,_.___
Recent Activity:
MARKETPLACE
A bad score is 579. A good idea is checking yours at freecreditscore.com.

Stay on top of your group activity without leaving the page you're on - Get the Yahoo! Toolbar now.

.

__,_._,___

Tidak ada komentar:

Posting Komentar