CODEWITHSUNDEEP
sqlms-accessjoin
wenzel267
wenzel267

Reputation: 93

How can I get data from the same table in one query in ms access

I have a problem with my ms access sql query. I want to have every ReportPosition From a Report. In every ReportPosition There is a Pknr from a deceased its first and lastname and its type. As well as the pknr firstname and lastname of the matching pensioner

I have the following Tables:

ReportPosition

+-----------------------+---------------+----------+
| ReportPositionId(PK1) | ReportId(PK1) | Pknr(FK) |
+-----------------------+---------------+----------+
|                     1 |             1 |   123121 |
|                     2 |             1 |   456451 |
|                     3 |             1 |   789782 |
+-----------------------+---------------+----------+

Pensioner

+----------+-----------+------------+--------------+--------------+
| PKNR(PK) | LastName  | FirstName  | PKNRdeceased | deceasedType |
+----------+-----------+------------+--------------+--------------+
|   123121 | Lastname1 | Firstname1 |       123120 |            1 |
|   456451 | Lastname2 | Firstname2 |       456450 |            1 |
|   789782 | Lastname3 | Firstname3 |       789780 |            2 |
|   123120 | Lastname4 | Firstname4 |            0 |            0 |
|   456450 | Lastname5 | Firstname5 |            0 |            0 |
|   789780 | Lastname6 | Firstname6 |            0 |            0 |
+----------+-----------+------------+--------------+--------------+

deceasedType

+--------+-------------+
| TypeId |     Bez     |
+--------+-------------+
|      0 | Pensioner   |
|      1 | Widow       |
|      2 | first Child |
+--------+-------------+

I need the following output:

+----------------+----------+--------+-----------+------------+---
| ReportPosition | ReportId |  PKNR  | lastname  | firstname  |
+----------------+----------+--------+-----------+------------+---
|              1 |        1 | 123121 | lastname1 | firstname1 |
|              2 |        1 | 456451 | lastname2 | firstname2 |
|              3 |        1 | 789782 | lastname3 | firstname3 |
+----------------+----------+--------+-----------+------------+---
--+--------------+--------------+---------------------+--------------------+
  | deceasedtype | PKNRdeceased | lasttname deaceased | firstname deceased |
--+--------------+--------------+---------------------+--------------------+
  |            1 |       123120 | lastname4           | firstname4         |
  |            1 |       456450 | lastname5           | firstname5         |
  |            2 |       789780 | lastname6           | firstname6         |
--+--------------+--------------+---------------------+--------------------+

I tried this already but I don't get the desired result: I used the automatic query creation from access since writing the query manually was to hard for me.

    SELECT ReportPosition.ReportPositionId, ReportPosition.ReportId, ReportPosition.PKNR, deceased.lastname, deceased.firstname, PensionerType.Bez, Pensioner.PKNR, Pensioner.lastname, Pensioner.firstname
FROM (PensionerType INNER JOIN (ReportPosition INNER JOIN Pensioner AS deceased ON ReportPosition.PKNR = deceased.PKNR) ON PensionerType.TypeId = deceased.PensionerType) INNER JOIN Pensioner ON deceased.PKNR = Pensioner.pknrdeceased
WHERE ReportPosition.ReportId=1;

I need something like a selfjoin because i have to join pensioner with pensioner am I right?

At the moment I don't even get all columns i need. and also not every dataset I want.

Upvotes: 0

Views: 408

Answers (2)

Wolfgang Kais
Wolfgang Kais

Reputation: 4100

As long as you don't need the Bez of the deceased type, you just need to add the ReportPosition and Pensioner tables, where you add the Pensionertable twice:

enter image description here

Then, add the fields you want to return, where you think of Pensioner_1 as "the deceased". The SQL of the resulting query should look similar to this:

SELECT ReportPosition.ReportPositionId, ReportPosition.ReportId, 
  Pensioner.PKNR, Pensioner.LastName, Pensioner.FirstName, Pensioner.deceasedType, Pensioner.PKNRdeceased, 
  Pensioner_1.LastName AS [Lastname deceased], Pensioner_1.FirstName AS [Firstname deceased]
FROM Pensioner AS Pensioner_1 INNER JOIN 
    (Pensioner INNER JOIN ReportPosition ON Pensioner.PKNR = ReportPosition.Pknr) 
  ON Pensioner_1.PKNR = Pensioner.PKNRdeceased;

And of course you can change the alias from Pensioner_1 to deceased.

Added:

If you want to display the Bez field (instead if TypeId), you have to add the deceasedType table:

enter image description here

You can then exchange the deceasedType field by the Bez field from the newly added table. The SQL of the edited query will look similar to this:

SELECT ReportPosition.ReportPositionId, ReportPosition.ReportId,
  Pensioner.PKNR, Pensioner.LastName, Pensioner.FirstName, deceasedType.Bez, Pensioner.PKNRdeceased,
  Pensioner_1.LastName AS [Lastname deceased], Pensioner_1.FirstName AS [Firstname deceased]
FROM deceasedType INNER JOIN
  ((Pensioner AS Pensioner_1 INNER JOIN Pensioner
    ON Pensioner_1.PKNR = Pensioner.PKNRdeceased) INNER JOIN
    ReportPosition ON Pensioner.PKNR = ReportPosition.Pknr)
  ON deceasedType.TypeId = Pensioner.deceasedType;

Upvotes: 2

Erik A
Erik A

Reputation: 32682

I haven't tracked down your exact error, but the query builder in Access creates a very chaotic join notation that's hard to read for me.

As far as I can see, the following should work, though:

SELECT ReportPosition.ReportPositionId, ReportPosition.ReportId, ReportPosition.PKNR, deceased.lastname, deceased.firstname, PensionerType.Bez, Pensioner.PKNR, Pensioner.lastname, Pensioner.firstname
FROM (((Pensioner
        INNER JOIN Pensioner AS deceased ON deceased.PKNR = Pensioner.pknrdeceased)
    INNER JOIN PensionerType ON PensionerType.TypeId = deceased.PensionerType)
INNER JOIN ReportPosition ON ReportPosition.PKNR = deceased.PKNR)
WHERE ReportPosition.ReportId=1;

Upvotes: 0

Related Questions