By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,504 Members | 1,212 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,504 IT Pros & Developers. It's quick & easy.

convert multiple rows in a single query

P: 4
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
Jan 26 '10 #1
Share this Question
Share on Google+
3 Replies


Expert 100+
P: 266
Ok I'm sorry but I have to ask...Why?!

-AJ
Jan 26 '10 #2

Expert Mod 2.5K+
P: 2,545
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
Jan 27 '10 #3

TheSmileyCoder
Expert Mod 100+
P: 2,321
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.