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
Tidak ada komentar:
Posting Komentar