CODEWITHSUNDEEP
sqldatabasems-accessvbams-access-2010
virrion
virrion

Reputation: 424

MS Access SQL query from 3 tables

I have 3 tables shown below in MS Access 2010:

Table: devices

   id | device_id | Company | Version | Revision |
   -----------------------------------------------
   1  | dev_a     | Almaras | 1.5.1   | 0.2A     |
   2  | dev_b     | Enigma  | 1.5.1   | 0.2A     |
   3  | dev_c     | Almaras | 1.5.1   | 0.2C     |
*Field: device_id is Primary Key Unique String
*Field ID is just an auto-number column

Table: activities

   id | act_id | act_date   | act_type | act_note |
   ------------------------------------------------
    1 | dev_a  | 07/22/2013 | usb_axc  | ok       |
    2 | dev_a  | 07/23/2013 | usb_axe  | ok       | (LAST ROW for dev_a)
    3 | dev_c  | 07/22/2013 | usb_axc  | ok       | (LAST ROW for dev_c)
    4 | dev_b  | 07/21/2013 | usb_axc  | ok       | (LAST ROW for dev_b)
*Field: act_id contains device_id; NOT UNIQUE
*Field ID is just an auto-number column

Table: matrix

  id | mat_id | tc    | ts | bat | cycles |
  -----------------------------------------
  1  | dev_a  | 2811  | 10 | 99  | 200    |
  2  | dev_a  | 2911  | 10 | 97  | 400    |
  3  | dev_a  | 3007  | 10 | 94  | 600    |
  4  | dev_a  | 3210  | 10 | 92  | 800    | (LAST ROW for dev_d)
  5  | dev_b  | 1100  | 5  | 98  | 100    |
  6  | dev_b  | 1300  | 8  | 93  | 200    |
  7  | dev_b  | 1411  | 11 | 90  | 300    | (LAST ROW for dev_b)
  8  | dev_c  | 4000  | 27 | 77  | 478    | (LAST ROW for dev_c)
*Field: mat_id contains device_id; NOT UNIQUE
*Field ID is just an auto-number column

Is there any way to query tables to get results as shown below (each device from devices and only last row added [see example output table] from each of the other two tables):

Query Results:

device_id | Company | act_date   | act_type | bat | cycles |
------------------------------------------------------------
device_a  | Almaras | 07/23/2013 | usb_axe  |  92 |   800  |
device_b  | Enigma  | 07/21/2013 | usb_axc  |  90 |   300  |
device_c  | Almaras | 07/22/2013 | usb_axc  |  77 |   478  |

Any ideas? Thank you in advance for reading and helping me out :)

Upvotes: 0

Views: 176

Answers (3)

Daniel E.
Daniel E.

Reputation: 2059

What do you consider the "last" row in Matrix?

You need to do something like WHERE act_date in (SELECT max(a.act_date) from activities a where a.mat_id=d.device_id GROUP BY a.mat_id)

and something similar for the join to matrix.

Upvotes: 0

John Woo
John Woo

Reputation: 263913

I think is what you want,

SELECT  a.device_id, a.Company,
        b.act_date, b.act_type,
        c.bat, c.cycles
FROM    ((((devices AS a
        INNER JOIN activities AS b
            ON a.device_id = b.act_id)
        INNER JOIN matrix AS c
            ON  a.device_id = c.mat_id)
        INNER JOIN
        (
            SELECT  act_id, MAX(act_date) AS max_date
            FROM    activities
            GROUP   BY act_id
        ) AS d  ON b.act_id = d.act_id AND b.act_date = d.max_date)
        INNER JOIN
        (
            SELECT  mat_id, MAX(tc) AS max_tc
            FROM    matrix
            GROUP   BY mat_id
        ) AS e  ON c.mat_id = e.mat_id AND c.tc = e.max_tc)

The subqueries: d and e separately gets the latest row for every act_id.

Upvotes: 4

NyseX
NyseX

Reputation: 24

Try

SELECT devices.device_id, devices.Company, activities.act_data, activities.act_type, matrix.bat, matrix.cycles
FROM devices
LEFT JOIN activities
ON devices.device_id = activities.act_id
LEFT JOIN matrix
ON devices.device_id = matrix.mat_id;

Upvotes: 0

Related Questions