Display multiple query records in one row

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Carl - 29 Mar 2007 18:48 GMT

I am trying to write a query that will return one row of data even though
there are multiple records.

For Example. If I want to search for all accounts created in last month.

Table

Name DOB AC Create Account type Account Number
Joe Bloggs 12.06.83 15.02.07 Current 12345678
Joe Bloggs 12.06.83 01.03.07 Saving 87654321
Joe Bloggs 12.06.58 10.03.07 Deposit 13572468
Jane Doe 16.02.58 17.02.07 Current 13578642
Jane Doe 16.02.58 17.02.07 Saving 86421357

I would like to create a simple query to get the results as

Name DOB Current No Saving No Deposit No
Joe Bloggs 12.06.83 12345678 87654321 13572468
Jane Doe 16.02.58 13578642 86421357

Reply to this Message

Heather - 29 Mar 2007 23:10 GMT

I'd suggest using a crosstab query. There is a wizard in access that will
guide you through it. Assuming you don't have more than one checking or
savings or account type...

In design view
field Total Crosstab
customer Group By row heading
account type Group by column heading
account number First Value

You'd have to make sure that there aren't any customers with more that one
of each type of account though--this will omit those as it's only choosing
the first of each type..

> I am trying to write a query that will return one row of data even though
> there are multiple records.
[quoted text clipped - 15 lines]
> Joe Bloggs 12.06.83 12345678 87654321 13572468
> Jane Doe 16.02.58 13578642 86421357

Reply to this Message