Home

MS Access SQL SELECTs With SELECTs From Another Table

SELECT *, 
(SELECT MAX(MyDate) FROM table2 WHERE table2.id = table1.id) AS MyDate
FROM table1 WHERE MyBool = true AND MyCount > 3

So we are selecting everything from table1 as well as table2's date field, but just the latest (MAX) date field. In this case I'm basically selecting the latest order made my each customer. 

We can complicate this with inner joins too

SELECT *, 
(SELECT MAX(MyDate) FROM table2 WHERE table2.id = table1.id) AS MyDate
FROM
(table1 INNER JOIN table3 ON table1.id = table3.thisorder)
INNER JOIN table4 on table1.id = table4.thisfile
WHERE MyBool = true AND MyCount > 3

For me this skipped all the long loops through a repeater.

Reader's Comments

Post Your Comment Posts/Links Rules

Name

---

Comment

---

Add a RELEVANT link (not required)

---

Upload an image (not required)
Uploading...

---

---

Home