Comparing fields from 2 similar tables + generating new table

[Mimo]

Hi,

I have imported 2 tables with similar structure, and want to generate a new
table containing data from both tables, as well new fields that compare the
data from the two tables.

Eg. Table 1 = Users (Name, Surname, DOB)
Table 2 = Persons(F_Name, S_Name, Birthday).

I am trying to create Table 3 to have the fields in the following order:

Table 3 = Compare(Users.Name, Persons.F_Name, Compare1, Users.Surname,
Persons.S_Name, Compare2, Users.DOB, Persons.Birthday, Compare3)

The fields compare1, compare2, and compare 3 need to return TRUE if the 2
fields before them contain identical data.

Using the example above compare1 would return TRUE if Users.Name, and
Persons.F_Name are the same.

I've been trying to write a query that does this. Please help! PS: Using
Access 2003

Thanks

 

[carlo]

Hi,

I have imported 2 tables with similar structure, and want to generate a new
table containing data from both tables, as well new fields that compare the
data from the two tables.

Eg. Table 1 = Users (Name, Surname, DOB)
Table 2  = Persons(F_Name, S_Name, Birthday).

I am trying to create Table 3 to have the fields in the following order:

Table 3 = Compare(Users.Name, Persons.F_Name, Compare1, Users.Surname,
Persons.S_Name, Compare2, Users.DOB, Persons.Birthday, Compare3)

The fields compare1, compare2, and compare 3 need to return TRUE if the 2
fields before them contain identical data.

Using the example above compare1 would return TRUE if Users.Name, and
Persons.F_Name are the same.

I've been trying to write a query that does this. Please help! PS: Using
Access 2003

Thanks

In the query design view, add both tables, then connect all 3 fields
of the first table with the other table.
for example, click and hold Users.Name and drag it over to
Persons.F_Name, then grab Users.Surname and drag it over to
Persons.S_Name, then grab Users.DOB and drag it over to
Persons.Birthday.
That way access returns you only those fields that actually contain
the same data.

If you really want to have the compare fields you could do following:
compare1: iif([Users].[Name]=[Persons].[F_Name],TRUE,FALSE)

hth
Carlo

 

[Mimo]

Thanks Carlo.
I have to include the compare as the clients are requesting the data in this
format.
Tidious as I have more than 50 fields, but thankyou!

Mimo

Hi,

I have imported 2 tables with similar structure, and want to generate a new
table containing data from both tables, as well new fields that compare the
data from the two tables.

Eg. Table 1 = Users (Name, Surname, DOB)
Table 2 = Persons(F_Name, S_Name, Birthday).

I am trying to create Table 3 to have the fields in the following order:

Table 3 = Compare(Users.Name, Persons.F_Name, Compare1, Users.Surname,
Persons.S_Name, Compare2, Users.DOB, Persons.Birthday, Compare3)

The fields compare1, compare2, and compare 3 need to return TRUE if the 2
fields before them contain identical data.

Using the example above compare1 would return TRUE if Users.Name, and
Persons.F_Name are the same.

I've been trying to write a query that does this. Please help! PS: Using
Access 2003

Thanks

In the query design view, add both tables, then connect all 3 fields
of the first table with the other table.
for example, click and hold Users.Name and drag it over to
Persons.F_Name, then grab Users.Surname and drag it over to
Persons.S_Name, then grab Users.DOB and drag it over to
Persons.Birthday.
That way access returns you only those fields that actually contain
the same data.

If you really want to have the compare fields you could do following:
compare1: iif([Users].[Name]=[Persons].[F_Name],TRUE,FALSE)

hth
Carlo