CODEWITHSUNDEEP
sqldatabasems-accessms-access-2010
mdames
mdames

Reputation: 37

How to display a one to many relationship as one to one

I'm working with a paging system. For some of my co-workers I need to create something of a directory. Each person in the directory has a PAGER_ID and a MESSAGING_ID. The PAGER_ID is unique to a given paging device, while a MESSAGING_ID is unique to a person. A group might work in shifts and pass a single device from shift to shift resulting in several people having an identical PAGER_ID and different MESSAGING_ID. This is fine and by design.

For our directory the users want the following criteria met:

  1. Each PAGER_ID to show up once and only once (I know I could do this by SELECT TOP 1 to the PAGER_ID)
  2. A ten digit MESSAGING_ID is preferred when a PAGER_ID has a MESSAGING_ID that is ten digits and one or more other MESSAGING_ID with a different number of digits.
  3. If there is no 10 digit MESSAGING_ID a MESSAGING_ID with any other number of digits will do.

Succinctly: They want to see only one record for a PAGER_ID / MESSAGING_ID. They don't care if every individual person is listed in the directory. They don't really care which MESSAGING_ID as long as each PAGER_ID shows in the directory with only one MESSAGING_ID and as long as a 10 digit MESSAGING_ID is given preference over those not 10 digits long.

I've tried combinations of TOP and IIF queries and have been unable to get them to all play nice together in the manner needed.

My basic select query is:

SELECT
    tbl_Amcom_Prod.NAME,
    tbl_Amcom_Prod.PAGER_ID,
    tbl_Amcom_Prod.MESSAGING_ID
FROM tbl_Amcom_Prod
WHERE
    (((tbl_Amcom_Prod.PAGER_ID) Like "241662"
    Or (tbl_Amcom_Prod.PAGER_ID) Like "18888"))
ORDER BY tbl_Amcom_Prod.PAGER_ID;

and results in this:

|     NAME       | PAGER_ID | MESSAGING_ID |
--------------------------------------------
| TESTER 1       |    18888 | 18888        |
--------------------------------------------
| SMITH, MARK    |    18888 | 5735551262   |
--------------------------------------------
| SUPERVISOR     |   241662 | 102621       |
--------------------------------------------
| JOHN, JONES    |   241662 | 101436       |
--------------------------------------------
| SEEGER, ROBERT |   241662 | 101409       |
--------------------------------------------

They want to see this:

|     NAME       | PAGER_ID | MESSAGING_ID |
--------------------------------------------
| SMITH, MARK    | 18888    | 5735551262   |
--------------------------------------------
| SUPERVISOR     | 241662   | 102621       |
--------------------------------------------

Any ideas?

Upvotes: 1

Views: 117

Answers (3)

HansUp
HansUp

Reputation: 97101

If PAGER_ID and MESSAGING_ID are both text data type, this query returns the results you requested.

SELECT
    t.NAME,
    t.PAGER_ID,
    t.MESSAGING_ID
FROM
    (
        SELECT
            sub1.PAGER_ID,
            DLookUp(
                "MESSAGING_ID",
                "tbl_Amcom_Prod",
                "PAGER_ID = '" & sub1.PAGER_ID &
                    "' AND Len(MESSAGING_ID) = " &
                    sub1.MESSAGING_ID_max_length
            ) AS MESSAGING_ID
        FROM
            (
                SELECT
                    PAGER_ID,
                    Max(Len(MESSAGING_ID))
                        AS MESSAGING_ID_max_length
                FROM tbl_Amcom_Prod
                GROUP BY PAGER_ID
            ) AS sub1
    ) AS sub2
    INNER JOIN tbl_Amcom_Prod AS t
    ON sub2.MESSAGING_ID = t.MESSAGING_ID
WHERE t.PAGER_ID In ("241662","18888")
ORDER BY t.PAGER_ID;

If PAGER_ID is Long Integer instead of text, use ...

            DLookUp(
                "MESSAGING_ID",
                "tbl_Amcom_Prod",
                "PAGER_ID = " & sub1.PAGER_ID &
                    " AND Len(MESSAGING_ID) = " &
                    sub1.MESSAGING_ID_max_length
            ) AS MESSAGING_ID

and ...

WHERE t.PAGER_ID In (241662,18888)

If MESSAGING_ID is Long Integer instead of text, change Max(Len(MESSAGING_ID)) to Max(Len(CStr(MESSAGING_ID))) and Len(MESSAGING_ID) to Len(Cstr(MESSAGING_ID))

Upvotes: 1

Bulat
Bulat

Reputation: 6969

Ok lets say your table is MyTable, then all you need is to group:

SELECT FIRST(Name), PAGER_ID,  MAX(MESSAGING_ID)
FROM MyTable
GROUP BY PAGER_ID

Upvotes: 1

Fionnuala
Fionnuala

Reputation: 91316

Perhaps:

SELECT tt.NAME, tt.PAGER_ID, tt.MESSAGING_ID
FROM tt
WHERE tt.PAGER_ID In (
    SELECT [PAGER_ID] 
    FROM tt a 
    WHERE  a.MESSAGING_ID IN (
        SELECT TOP 1 MESSAGING_ID 
        FROM tt 
        ORDER BY Len(MESSAGING_ID) DESC,PAGER_ID))

Where tt is your table.

Upvotes: 1

Related Questions