Splitting Out a listing tables

[JezLisle via AccessMonster.com]

I am trying to work out how I can take a table of data that lists AddressID,
LetterNumber & LetterDate in that form to this way

AddressID Letter1 Letter1Date AddressID Letter2 Letter2Date AddressID
Letter3 Letter3Date

Thanks

 

[Duane Hookom]

Do you always have 3 sets of records? Is this being published in a report? Do
you want separate columns for each field?

 

[JezLisle via AccessMonster.com]

Yes theres only a maximum of 3 records. I need to show them in this way as
the report I'm building has to show all the data for that Address by Columns.

So I want to take the listing of Address and Letter Dates and split out into
columns

Do you always have 3 sets of records? Is this being published in a report? Do
you want separate columns for each field?

I am trying to work out how I can take a table of data that lists AddressID,
LetterNumber & LetterDate in that form to this way

[quoted text clipped - 3 lines]

Thanks

 

[Duane Hookom]

I would create a main report that has a record source of each unique
AddressID. Then create a 3 column subreport to place in the detail section of
the main report. Set the Link Master/Child properties of the subreport
control to AddressID.
--
Duane Hookom
Microsoft Access MVP

Yes theres only a maximum of 3 records. I need to show them in this way as
the report I'm building has to show all the data for that Address by Columns.

So I want to take the listing of Address and Letter Dates and split out into
columns

Do you always have 3 sets of records? Is this being published in a report? Do
you want separate columns for each field?

I am trying to work out how I can take a table of data that lists AddressID,
LetterNumber & LetterDate in that form to this way

[quoted text clipped - 3 lines]

Thanks

 

[JezLisle via AccessMonster.com]

Is there not a way it can be done in a query so that I can use it other forms
like exporting to excel?

I would create a main report that has a record source of each unique
AddressID. Then create a 3 column subreport to place in the detail section of
the main report. Set the Link Master/Child properties of the subreport
control to AddressID.

Yes theres only a maximum of 3 records. I need to show them in this way as
the report I'm building has to show all the data for that Address by Columns.

[quoted text clipped - 9 lines]

 

[Duane Hookom]

I thought you asked only for a report ;-). You first need to sequence your
records in a query. The following is a query [qselRankTitle] of the Employees
in Northwind that creates a sequence number within a [Title] (similar to
AddressID).

SELECT Employees.Title, Employees.LastName, Count(Employees_1.EmployeeID) AS
RankOrder
FROM Employees LEFT JOIN Employees AS Employees_1 ON Employees.Title =
Employees_1.Title
WHERE (((Employees.EmployeeID)<=[Employees_1].[EmployeeID]))
GROUP BY Employees.Title, Employees.LastName
ORDER BY Employees.Title;

Then you can create a crosstab query that changes the vertical display to
horizontal by Title.
TRANSFORM First(qselRankTitle.LastName) AS FirstOfLastName
SELECT qselRankTitle.Title
FROM qselRankTitle
GROUP BY qselRankTitle.Title
PIVOT qselRankTitle.RankOrder;

You can create a multivalue crosstab as described at
http://www.tek-tips.com/faqs.cfm?fid=4524.

--
Duane Hookom
Microsoft Access MVP

Is there not a way it can be done in a query so that I can use it other forms
like exporting to excel?

I would create a main report that has a record source of each unique
AddressID. Then create a 3 column subreport to place in the detail section of
the main report. Set the Link Master/Child properties of the subreport
control to AddressID.

Yes theres only a maximum of 3 records. I need to show them in this way as
the report I'm building has to show all the data for that Address by Columns.

[quoted text clipped - 9 lines]

Thanks