convert multiple rows in a single query

[karimufeed]

I have an MS Access 2000 database It has an Applications table and an Applicants table.

One Application ID may have many applicants attached. When creating a query by Application ID, one row is returned for each applicant attached to the application e.g.
Application ID Applicant ID FirstName LastName
100 1 Jay kumar
100 2 Jai singh

What I want is a query that returns Application ID once and all the applicants attached to it in the one row.

thanks

[ajalwaysus]

Ok I'm sorry but I have to ask...Why?!

-AJ

[Stewart Ross]

I'd ask Why just as AJ has...

In any event this is not directly possible using any form of standard SQL query. The nearest Access could come to doing what you ask (which is in effect pivoting the list of applicants from rows to columns of your query) is via a Crosstab query, but this will not work for what you want to do as it would pivot all applicants for all posts into the column list, not just the applicants for the current post only.

It can be done in code by processing a recordset in a loop and generating a list of applicants for each post, but you'd need to have good VBA skills to do so.

-Stewart

[TheSmileyCoder]

If you use grouping in a report, you can achieve a result looking like you want, just group on Application ID, and make a Application Header.