putting three records into one record

[BrightIdeas]

hi, I want to take three consequetive records in a table and create a
table with three records on one row. All records would connect by one
field.

I can set up a table with all the fields in it but do not know how to
put the records on the same row

 

[kingston via AccessMonster.com]

Refer to this recent post for more details:

http://www.accessmonster.com/Uwe/Fo...-values-into-a-single-record#688a5501f31ccuwe


Basically, this can be done via a temporary table, a delete query, an append
query, and an update query. The update query is the key since it adds data
from consecutive records to the concatenated field. In your case, you'll
have to create a select query, possibly using TOP 3, to feed the update query.

 

[Duane Hookom]

There is a generic concatenate function with sample usage at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

 

[John Nurick]

If the "consecutive" records are identified by consecutive ID numbers,
you can join the table to itself three times and return every third
record. Here's an example that works in the Northwind sample database:

SELECT A.OrderID, B.OrderID, C.OrderID
FROM (
(SELECT OrderID As RowNum, OrderID FROM Orders) AS A
INNER JOIN
(SELECT OrderID - 1 AS RowNum, OrderID FROM Orders) AS B
ON A.RowNum = B.RowNum
) INNER JOIN
(SELECT OrderID - 2 AS RowNum, OrderID FROM Orders) AS C
ON A.RowNum = C.RowNum
WHERE A.OrderID MOD 3 = 1
ORDER BY A.RowNum
;

Without the consecutive numbers things get more complicated.

 

[BrightIdeas]

Thanks, I am trying this method. It works for each id field but I
cannot get the other fields on to the same row. My record only has
three fields now.


I can create an empty table with all the fields . Can I fill one record
from three records?

 

[BrightIdeas]

I have found I can join the original table three times to the row
number result query using the different ids to get all the fields from
each set of three records on one row. thanks