Vertical records to horizontal records 1

[emiTgiB]

I have a table laid out like this

ID Tag File Description
1 1234 1234i1.doc File 1
2 1234 1234i2.doc File 2
3 4321 4321i1.doc File 1
4 4321 4321i2.doc File 2
5 4321 4321i3.doc File 3

I want it to look like this

Tag File1 Desc1 File2 Desc2 File3 Desc3
1234 1234i1.doc File1 1234i2.doc File2
4321 4321i1.doc File1 4321i2.doc File2 4321i3.doc File 3

Is there anyway to do this in MS Access useing a SQL statement or anything like that?

 

[cass60660]

Create first a view for each file (file1, ...fileN) and then create an select from all views.

Something like this:
(table1 is the same tbale with table1_1)
SELECT Table1.*, Table1_1.*, Table1.id, Table1_1.id
FROM Table1, Table1 AS Table1_1
WHERE (((Table1.id)=1) AND ((Table1_1.id)=2));

 

[emiTgiB]

Could you explain that in a diffrent way. I don't under stand what you mean by a view for each file.

 

[cass60660]

Lets say Table1 is your table:

SELECT Table1.*, Table1_1.*
FROM Table1, Table1 AS Table1_1
WHERE (((Table1.Description)='File 1') AND ((Table1_1.Description)='File 2'));

the result is going to look like this

1234 1234i1.doc File1 1234i2.doc File2
4321 4321i1.doc File1 4321i2.doc File2

 

[emiTgiB]

Yes I understand that part and have gotten that far but what about the third, fouth or fifth file that I potentionaly have? 4321 should have a file3 (4321i3.doc File 3)

 

[fneily]

I made a table with two fields - partno, desc. Desc can span more than one record, eg.
partno desc
abcd this is one desc
abcd this is two desc
xyz another desc
xyz more desc
etc. etc.

I want to concatenate the desc's into one field.

create a query that looks like this:
SELECT [partno], fConcatFld("parttable","partno","desc","string",[partno]) AS descs
FROM parttable
GROUP BY [partno];

Do ALT+F11, insert a new module and put the following code for the fConcatFld function:

Function fConcatFld(stTable As String, _
stForFld As String, _
stFldToConcat As String, _
stForFldType As String, _
vForFldVal As Variant) _
As String

'Usage Examples:
' ?fConcatFld(("Customers","ContactTitle","CustomerID", _
' "string","Owner&quot
'Where Customers = The parent Table
' ContactTitle = The field whose values to use for lookups
' CustomerID = Field name to concatenate
' string = DataType of ContactTitle field
' Owner = Value on which to return concatenated CustomerID
'
Dim lodb As Database, lors As DAO.Recordset
Dim lovConcat As Variant, loCriteria As String
Dim loSQL As String

On Error GoTo Err_fConcatFld

lovConcat = Null
Set lodb = CurrentDb


loSQL = "SELECT [" & stFldToConcat & "] FROM [" & stTable & "] WHERE [" & stForFld & "] = '" & vForFldVal & "' ;" 'place this line on one line in the VBA window.

Set lors = lodb.OpenRecordset(loSQL, dbOpenSnapshot)

'Are we sure that duplicates exist in stFldToConcat
With lors
If .RecordCount <> 0 Then
'start concatenating records
Do While Not .EOF
lovConcat = lovConcat & lors(stFldToConcat) & &quot; &quot;
.MoveNext
Loop
Else
GoTo Exit_fConcatFld
End If
End With

'Just Trim the trailing ;
fConcatFld = Left(lovConcat, Len(lovConcat) - 1)


Exit_fConcatFld:
Set lors = Nothing: Set lodb = Nothing
Exit Function

Err_fConcatFld:
MsgBox &quot;Error#: &quot; & Err.Number & vbCrLf & Err.description
Resume Exit_fConcatFld
End Function

Run the query, which calls the function. Besure in your VBA window, click on Tools , the References and make sure the DAO library is referenced first.

You would substitute tag for partno, etc.

Neil