Using Dlookup to map data

[Mimo]

Hi,

I'm trying to create a query using 2 tables with similar data, and 1 table
that contains a data map.

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

I am trying to run a query that presents the fields in the following order:

Table 4 = Compare(Users.Name, Persons.F_Name, Compare1, Users.Surname,
Persons.S_Name, Compare2, Users.DOB, Persons.Birthday, Compare3,
Users.Status, Persons.Stat, Compare4)

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

For Table Users, Status is either M, S, D, W. and for Table Persons, Stat
has the values Married, Single, Divorced or Widowed. I am trying to make
compare4 return True if the status and Stat match.

Table 3 - (Map) maps the data e.g. Row 1: field1 = M, field2 = Married, Row
I2: field1 = S, field2 = single. etc. Ihave previously used iif statement to
check the 2 previous values before the compare field to return a result,
however I need to link compare4 to Table 3, to verify that the status does
indeed match.

I thought about using a Dlookup() function, but I'm not sure how to check
all 4 options for the status as I keep getting errors. Any ideas?

Thanks

Mimo

 

[Mimo]

I was just tired, and overcomplicated things, I just used the fields from the
Map tables in the iif() and made sure the tables were linked!