Relationship with multiple columns?

[Dustin R]

Hello,
This is my current situation. I have a staff roster (table) with
employee information including 4 columns of work logins. Some will
only have one login but some have multiple logins and I need to be
able to pull information from the staff table into a query/report with
data from a table that only contains a login for identification. I
cannot see how to make a relationship that looks at multiple columns
for a match to the same "key". Hopefully this is something simple
someone can help with.

 

[Jeff Boyce]

Dustin

"Simple", yes... but not if your data is organized like a spreadsheet (and
it is!). Access is a relational database, and will require a new way of
thinking if you want to get the best use of Access' features and functions.

Access works best with "well-normalized" data, not, as your description
suggestions, "repeating fields" (i.e., logon1, logon2, ...).

Is it possible to step through all the columns that might have logon data,
looking for a particular value? OK, is it possible to drive nails with a
chain saw? Yes, of course, but it can be painful!

Before you go any further, re-consider your data structure.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Dustin R]

Thank you for your input, I am getting this staff information from a
SharePoint list which is why the data is structured like that, but I
can change the way that list is arranged to easily solve my problem
but it will make administration more difficult.

 

[pietlinden]

Thank you for your input, I am getting this staff information from a
SharePoint list which is why the data is structured like that, but I
can change the way that list is arranged to easily solve my problem
but it will make administration more difficult.

If you have to you could query it together...

SELECT.tblA.PrimaryKey, tblA.Field1 AS FieldA
FROM tblA
WHERE tblA.Field1 IS NOT NULL
UNION
SELECT tblA.PrimaryKey, tblA.Field2 AS FieldA
FROM tblA
WHERE tblA.Field2 IS NOT NULL
....
ORDER BY tblA.FieldA;