Relationship with multiple columns?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dustin R - 18 Jul 2007 23:04 GMT

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.

Reply to this Message

Jeff Boyce - 19 Jul 2007 00:32 GMT

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

> Hello,
> This is my current situation. I have a staff roster (table) with
[quoted text clipped - 5 lines]
> for a match to the same "key". Hopefully this is something simple
> someone can help with.

Reply to this Message

Dustin R - 19 Jul 2007 17:07 GMT

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.

Reply to this Message

pietlinden@hotmail.com - 20 Jul 2007 00:20 GMT

> 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;

Reply to this Message