line listing records into one record (1 Viewer)

[gsrai31]

Table
ChipNo Location(L) 1st Reg(1st) Last Reg(Last)
511 A 8/12 8/13
511 B 8/13 8/14
511 C 8/15 8/16
511 D 8/17
513 B 8/25 8/25
513 D 8/27 8/28

How can I list all the records from above table in one line where chip no is same? something like this,

Results ( what I want)

ChipNo | L | 1st | Last | L | 1st | Last| L | 1st | Last | L |1st | Last |
511 A 8/12 8/13 B 8/13 8/14 C 8/15 8/16 D 8/17
513 B 8/25 8/25 D 8/27 8/28

thanks in advance
gsrai

 

[Jon K]

The attached DB contains a table "Location" and two queries:

qryOne:-
SELECT a.ChipNo AS ChipNo, a.Location & ", First " & nz(a.First_Reg,'nil') & ", Last " & nz(a.Last_Reg,'nil') AS Location,
"Location" & DCount("*","Location","ChipNo=" & [ChipNo] & " and Location <='" & [Location] & "'") AS LocationNo
FROM Location AS a;

qryTwo:-
TRANSFORM Last([Location]) AS LastOfLocation
SELECT [ChipNo]
FROM qryOne
GROUP BY [ChipNo]
PIVOT [LocationNo];

When the second query "qryTwo" is run, it will give:-

ChipNo -- Location1 --------------- Location2
511 ----- A, First 8/12, Last 8/13 -- B, First 8/13, Last 8/14  .....   .....
513 ----- B, First 8/25, Last 8/25 -- D, First 8/27, Last 8/28

Hope it helps.

The DB was saved in Access 97 format from Access 2000.

 

[gsrai31]

Jon Thank you for you reply. this is great and will help me in something else I am trying to do. I have attached an excel workbook to simplify, what I was trying to achieve with this.
There are four sheets, first sheet describes other worksheets and what I am want to acheive. Many thanks.

 

[Jon K]

I don't think you can do it with queries. You will need to write some VBA code to achieve the format you required.