Search code examples
databasems-accessms-access-2016

Find the newest entry of a crosstable per record?

I have three tables:

  1. My products with their IDs and their features.
  2. is a table with treatments of my products with a treatment-ID, a method, and a date. The treatments are done in batches of many products so there is a crosstable
  3. with the products IDs and the treatment IDs and a bool value for the success of the treatment.

Each product can undergo many different treatments so there is a many-to-many relation. I now want to add to the product table (1.) for every product a value that shows the method of its most recent successful treatment if there is any.

I made a query that groups the crosstable's entries by product-ID but I don't know how to show the method and date of it's last treatment.

table 1:
| productID | size | weight | height | ... |
|-----------|:----:|-------:|--------|-----|
| 1         |  13  |     16 | 9      | ... |
| 2         |  12  |     17 | 12     | ... |
| 3         |  11  |     15 | 15     | ... |
| ...       | ...  | ...    | ...    | ... |

table 2:
| treatmentID |  method  |       date |
|-------------|:--------:|-----------:|
| 1           | dye blue | 01.02.2016 |
| 2           |  dye red | 01.02.2017 |
| 3           | dye blue | 01.02.2018 |
| ...         | ...      | ...        |

table 3:
| productID | treatmentID | success |
|-----------|:-----------:|--------:|
| 1         | 1           | yes     |
| 1         | 2           | yes     |
| 1         | 3           | no      |
| ...       | ...         | ...     |

I need table 1 to be like:

table 1:
    | productID | size | weight | height | latest succesful method |
    |-----------|:----:|-------:|--------|-------------------------|
    | 1         |  13  |     16 | 9      |  dye red                |
    | 2         |  12  |     17 | 12     | ...                     |
    | 3         |  11  |     15 | 15     | ...                     |
    | ...       | ...  | ...    | ...    | ...                     |

My query:

 SELECT table3.productID, table2.method
 FROM table2 INNER JOIN table3 ON table2.treatmentID = table3.treatmentID
 GROUP BY table3.productID, table2.method
 HAVING (((table3.productID)=Max([table2].[date])))
 ORDER BY table3.productID DESC;

but this does NOT show only one (the most recent) entry but all of them.

Solution

  • Simplest solution here would be to write either a subquery within your sql, or create a new query to act as a subquery(it will look like a table) to help indicate(or elminate) the records you want to see.

    Using similar but potentially slightly different source data as you only gave one example.

    Table1
| ProductID | Size | Weight | Height |
|-----------|------|--------|--------|
| 1         | 13   | 16     | 9      |
| 2         | 12   | 17     | 12     |
| 3         | 11   | 15     | 15     |

Table2
| TreatmentID | Method     | Date     |
|-------------|------------|----------|
| 1           | dye blue   | 1/2/2016 |
| 2           | dye red    | 1/2/2017 |
| 3           | dye blue   | 1/2/2018 |
| 4           | dye yellow | 1/4/2017 |
| 5           | dye brown  | 1/5/2018 |

Table3
| ProductID | TreatmentID | Success |
|-----------|-------------|---------|
| 1         | 1           | yes     |
| 1         | 2           | yes     |
| 1         | 3           | no      |
| 2         | 4           | no      |
| 2         | 5           | yes     |

    First order of business is to get the max(dates) and productIds of successful treatments.

    We'll do this by aggregating the date along with the productIDs and "success".

    SELECT Table3.productid, Max(Table2.Date) AS MaxOfdate, Table3.success
FROM Table2 INNER JOIN Table3 ON Table2.treatmentid = Table3.treatmentid
GROUP BY Table3.productid, Table3.success;

    This should give us something along the lines of:

    | ProductID | MaxofDate | Success |
|-----------|-----------|---------|
| 1         | 1/2/2018  | No      |
| 1         | 1/2/2017  | Yes     |
| 2         | 1/4/2017  | No      |
| 2         | 1/8/2017  | Yes     |

    We'll save this query as a "regular" query. I named mine "max", you should probably use something more descriptive. You'll see "max" in this next query.

    Next we'll join tables1-3 together but in addition we will also use this "max" subquery to link tables 1 and 2 by the productID and MaxOfDate to TreatmentDate where success = "yes" to find the details of the most recent SUCCESSFUL treatment.

    SELECT table1.productid, table1.size, table1.weight, table1.height, Table2.method
FROM ((table1 INNER JOIN [max] ON table1.productid = max.productid) 
INNER JOIN Table2 ON max.MaxOfdate = Table2.date) INNER JOIN Table3 ON 
(Table2.treatmentid = Table3.treatmentid) AND (table1.productid = Table3.productid)
WHERE (((max.success)="yes"));

    The design will look something like this: Design

    (ps. you can add queries to your design query editor by clicking on the "Queries" tab when you are adding tables to your query design. They act just like tables, just be careful as very detailed queries tend to bog down Access)

    Running this query should give us our final results.

    | ProductID | Size | Weight | Height | Method    |
|-----------|------|--------|--------|-----------|
| 1         | 13   | 16     | 9      | dye red   |
| 2         | 12   | 17     | 12     | dye brown |