Re: Unmatch query?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

There has to be SOME basis for identifying unique records if you're going to
compare tables. Otherwise, no comparison is possible. In my sample, I
chose the simplest case, i.e. a single primary key field. If you don't have
a primary key established, you can still use any single field as long as it
uniquely identifies a record. (BTW, if you have one, you should at least
create a unique index on it if not a primary key).

If you have a *combination* of fields that uniquely identify the records,
then things get more complicated. If you had two fields (say LastName and
Firstname, for example), you could modify the routine to accept parameters
like this:

Sub CompareTables(BaseTable As String, KeyField1 As String, _
KeyField2 As String, BaseTableQuery As String, _
VaryingTableQuery As String)

Then everywhere you would use

rstBase(PrimaryKeyField)

for comparison, you would replace it with:

rstBase(KeyField1) & rstBase(KeyField2)

which concatenates the two values and will allow the comparison.

Unfortunately, this would require separate routines for tables with 2 or 3
or 4 fields in the combination. Off hand, I can't think of a nice, general
solution.

HTH.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

"zz12" <IDontLikeSpam@xxxxxxxxxxx> wrote in message
news:%23g7Aqps3HHA.5880@xxxxxxxxxxxxxxxxxxxxxxx
Just encountered something else in would you happen to have any samples or
recommendations for tables that don't have a primary key field? I notice
some of the other tables in our .mdb file have tables that don't have a
primary key ID fields so was wondering how would one go about comparing
the tables with this table structure? Thanks Roger.


"zz12" <IDontLikeSpam@xxxxxxxxxxx> wrote in message
news:Oa0asNs3HHA.5776@xxxxxxxxxxxxxxxxxxxxxxx
Wow, this looks pretty cool. Will look into it further. Thanks a bunch
Roger for your helpful reply :)


"Roger Carlson" <RogerCarlson@xxxxxxxxxxxxxxx> wrote in message
news:Obzxbpn3HHA.4676@xxxxxxxxxxxxxxxxxxxxxxx
On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "CompareTwoTables.mdb" which illustrates how to do this.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


"zz12" <IDontLikeSpam@xxxxxxxxxxx> wrote in message
news:exZnHHg3HHA.4048@xxxxxxxxxxxxxxxxxxxxxxx
Hello. Would anyone know of an easy method in trying to find
unmatching records in 2 different tables that have the same table
structure? A user had been updating a different copy of the same .mdb
file and now have to try to find which records and how to merge the
data in keeping the integrity since I notice the data have same ID
fields but different text. For example an Employee table that have
EmployeeID and EmployeeName as fields in where table A could have
EmployeeID=100 and EmployeeName=Albert and table B will have
EmployeeID=100 and EmployeeName=Bob.

I was thinking of concatenating all of the fields in a query as 1 large
column and doing a unmatching query based on this 1 super column but
this doesn't seem doable if the table contains numerous fields. Would
anyone know the best way to approach this in merging the 2 tables in
keeping the data integrity?

Thanks in advance.









.

Relevant Pages

  • Re: Gui Question
    ... employee they wish to create and specify the data concerning that ... private String firstName; ... public class CommissionEmployee extends Employee ... public class BasePlusCommissionEmployee extends CommissionEmployee ...
    (comp.lang.java.help)
  • Re: New to Access db
    ... The Foreign Key fields in the Subform which arentt linked to the main form ... Add EmpID and Employee to the query. ... AttEventID - Primary key Auto Number ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Code is failing on empyt recordset
    ... Set rst = CurrentDb.OpenRecordset ... primary key field for TST_FR_CASE_OTHERS. ... > fieldnames (even a query alias), ... >> Function RecordsInTable(Tablename As String, ...
    (microsoft.public.access.forms)
  • Re: inheritance question
    ... the constructor of the Employee class should accept all parameters ... public Employee (DateTime DOB, string Address, string MaritalStatus, ... > private string mMaritalStatus; ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Understanding Relationships
    ... have pages of things they might track about an employee. ... and for which a history is to be kept MUST be in a related (child) ... primary key of this employee's record in tblEmployee. ... Table 2 [SalaryScale related to Table 3 SalaryScale] ...
    (microsoft.public.access.gettingstarted)