469,150 Members | 1,951 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,150 developers. It's quick & easy.

convert multiple rows in a single query

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.

Jan 26 '10 #1
3 1985
266 Expert 100+
Ok I'm sorry but I have to ask...Why?!

Jan 26 '10 #2
Stewart Ross
2,545 Expert Mod 2GB
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.

Jan 27 '10 #3
2,321 Expert Mod 2GB
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.
Jan 27 '10 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by gregory.sharrow | last post: by
3 posts views Thread by Greg Neef | last post: by
5 posts views Thread by Robert Brown | last post: by
1 post views Thread by CARIGAR | last post: by
1 post views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.