Relationships with multiple columns?

[Dustin R]

Hello,
Is it possible to build a relationship that looks at more than one
column for a single match? For example I have a table with staff
information that has multiple logins (4) columns and another table
with activity information that only has one login. I want to be able
to query/report with information from both where login = login 1 or
login2 or login3 or login4 but can only build a relationship that
looks at one column then stops. Is there a way to make access do this?

 

[John W. Vinson]

Hello,
Is it possible to build a relationship that looks at more than one
column for a single match? For example I have a table with staff
information that has multiple logins (4) columns and another table
with activity information that only has one login. I want to be able
to query/report with information from both where login = login 1 or
login2 or login3 or login4 but can only build a relationship that
looks at one column then stops. Is there a way to make access do this?

Your first table's structure IS WRONG.

Someday you'll need a *fifth* logon, and you'll be stuck!

If you have a One (staff member) to Many (logins) relationship, the proper
structure is to have a logins table related one to many to the staff table.

That said... you can generate your report query. What you'll need to do is add
the first table, and then add the activities table to the query *four times* -
join one instance to Login1, the second to Login2, and so on. Select all four
join lines and change the join type from the default inner join (which
returns only records where there is a value in both tables) to Option 2 ("show
all records in Staff and matching records in Activities"). This will show the
record even if (say) there's data in Login1 but nothing in the other three
fields.

John W. Vinson [MVP]

 

[pietlinden]

Hello,
Is it possible to build a relationship that looks at more than one
column for a single match? For example I have a table with staff
information that has multiple logins (4) columns and another table
with activity information that only has one login. I want to be able
to query/report with information from both where login = login 1 or
login2 or login3 or login4 but can only build a relationship that
looks at one column then stops. Is there a way to make access do this?

Sounds like your problem is stemming from the denormalized structure
of your table. login(#) is the sign of a denormalized data structure.
Move those records with the primary key to another table. If you
can't change the structure of your tables, you can create a union
query to create a normalized view of the data.

SELECT PrimaryKey, Login1 AS Login FROM MyTable WHERE Login1 IS NOT
NULL
UNION
SELECT PrimaryKey, Login2 AS Login FROM MyTable WHERE Login2 IS NOT
NULL
ORDER BY PrimaryKey, Login;