Subquery in Access

Question

I have 2 tables in Access with these fields

Student:
ID(PK)   Name   Family Tel

Lesson:
ID StudentRef(FK(Student))  Name    Score

Imagine we have these records

Student :
1     Tom      Allen      09370045230
2     Jim      leman      09378031380

Lesson:

1     1       Math           18
2     1       Geography      20
3     2       Economic       15 
4     2       Math            12

How can I write a query that result will be this (2 fields)?

Tom      Math : 18    , Geography 20
Jim      Economic :15 ,  Math :12

This may help: http://stackoverflow.com/questions/8601643/access-sql-query-to-concatenate-rows/8603245#8603245 — Fionnuala, Dec 25 '11 at 20:14
Proper [sample code](http://sscce.org/) (here, SQL statements) is more useful than any ad hoc schema and sample data format. Please use `CREATE TABLE` and `INSERT ... VALUES` for samples. Desired results don't need to be presented as sample code, as results are the output of code and not code themselves. — outis, Mar 26 '12 at 00:41

score 2 · Answer 1 · answered Dec 25 '11 at 14:55

2

SELECT s.Name, l.Name, l.Score
  INNER JOIN tbl_lessons as l ON s.student_id = l.student_id
FROM tbl_students as s

That won't give you your formatting, but it'll get you the data.

answered Dec 25 '11 at 14:55

Corith Malin

1,505
10
18

no anybody can help me ? i can solve that in my application but this is easy for me – Salah Sanjabian Dec 25 '11 at 19:09

score 0 · Answer 2 · answered Dec 25 '11 at 22:34

0

The most tricky part of your problem is how to aggregate strings in your sub-query. MS Access does not have any aggregation function that is applicable to strings (except for Count()) and there is no way to define your own function. This means you can't just get the desired "subject:score , subject:score" concanetation. As long as you can go without you can easily take the solution provided in the answer by Corith Malin.

answered Dec 25 '11 at 22:34

Alexander Galkin

12,086
12
63
115

thank Alexander Galkin and Corith Malin. i 'll solve it in my Application with DataTable . – Salah Sanjabian Dec 26 '11 at 07:46

Subquery in Access

2 Answers2