SQL Query to transform/group data by Date

[Lucas_london via AccessMonster.com]

Hi,

I have a large Access table with data organised as follows:

Field1: Code
Field2: Date
Field3: Value1
Field4: Value2

There are seveal different codes and therefore duplicate dates. I'd like to
run a query to bring back each code grouped by date and so put the codes
along the top as feilds. For example the query below brings back the
following data for two codes.

SELECT field2, field1, field3
FROM Data
WHERE field1 In ('LLOY','RSA');

21/01/05, LLOY, 12454
22/01/05, LLOY, 31541
21/01/05, RSA, 21241
22/01/05, RSA, 12414

Instead I want the data to look like this:

Date LLOY RSA
21/01/05 12454 21241
22/01/05 31541 12414

Would this be easy to do?

Thanks,

Lucas

 

[Guest]

A crosstab query will do it.

 

[Lucas_london via AccessMonster.com]

Thanks,

I did the crosstab query and it works fine. A couple of questions.

Do you know if I can use the same syntax below to create the same result in
other applications e.g SQL Server, oracle etc? I've never come accross the
Transform function or the pivot table function. Is this unique to access?

TRANSFORM Avg(Field3) AS AvgOfField3
SELECT Field2
FROM Data
GROUP BY Field2
PIVOT Field1;

The reason why I ask is, I have a much bigger dataset that I need to do this
for, but Access has a limit in terms of the number of columns it can display
but I have 600. Or to get around this, does anyone know if it is possible to
write the output from a crosstab query in access directly to a text file?
Then I could just import the file into another database to work on.

Thanks

Lucas

 

[John Spencer]

As far as I know the Cross tab query is unique to Access. MS SQL may have
something like it in the later versions.

Yes is possible to export the results of query that returns records.
However, you are limited to 255 columns, so you would have to find a way to
split the number of columns that is returned into multiple groups of 255 or
less. In addition, you would have to have some method of tying the records
from query one to the records in query two (a primary key that is passed in
each group of records or a set of unique values).

If I can find it I will see if I can dig out some old code I used to do
something similar for getting data out of Access into a format that SAS
could use.


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[John Spencer]

Here is a VBA routine I used to export data with more than 255 columns.
Note that I had two recordsets based on queries. The queries had the same
number of records and the records were ordered in a unique order by same set
of fields (or the primary key). The first query had the unique order fields
as the first fields in the query. The second query did not return those
fields (not in the select list).

Hope you can expand this to cover your situation.

Public Function funExportToTabDelimited()
Dim db As database
Dim rs1 As Recordset, rs2 As Recordset
Dim strHolder As String, strTemp As String
Dim intCounter As Integer
Dim strFldDelimiter As String

strFldDelimiter = vbTab 'or Comma "," or Other value

Set db = CurrentDb
Set rs1 = db.OpenRecordset("tblExportValues", dbOpenDynaset)
Set rs2 = db.OpenRecordset("tblExportValues_Section2", dbOpenDynaset)

strHolder = db.Name
strHolder = Left(strHolder, Len(strHolder) - Len(Dir(strHolder)))

Open strHolder & "DataFlatFile" & Format(Date, "_mm-dd-yy") & ".txt" For
Output As #1

With rs1
strHolder = ""
'Get Field Names
For intCounter = 0 To .Fields.Count - 1
If intCounter <> 1 Then
strHolder = strHolder & Left$(.Fields(intCounter).Name, 8) &
strFldDelimiter
End If
Next intCounter
For intCounter = 0 To rs2.Fields.Count - 1
If intCounter > 1 Then
strHolder = strHolder & Left$(rs2.Fields(intCounter).Name, 8) &
strFldDelimiter
End If
Next intCounter

'Write Field Names to file
strHolder = Left(strHolder, Len(strHolder) - Len(strFldDelimiter))
Print #1, strHolder

'Get field contents for each record and write to file
Do While Not .EOF

'get matching line of data in secondary file
strHolder = "SurveyDocId ='" & .Fields("SurveyDocID") & "'"
rs2.FindFirst strHolder
strHolder = vbNullString

For intCounter = 0 To .Fields.Count - 1
If intCounter <> 1 Then
strTemp = .Fields(intCounter) & ""
strTemp = ReplaceString(strTemp, vbTab, " ") 'Strip Tabs
strTemp = ReplaceString(strTemp, vbCrLf, " ") 'Strip C/R

'----Surround with Quotes if appropriate
If strFldDelimiter <> "|" Then
If InStr(1, strTemp, ",") > 0 Then
strTemp = Chr(34) & strTemp & Chr(34)
End If
End If

strHolder = strHolder & strTemp & strFldDelimiter
End If
Next intCounter

For intCounter = 0 To rs2.Fields.Count - 1
If intCounter > 1 Then
strTemp = rs2.Fields(intCounter) & ""
strTemp = ReplaceString(strTemp, vbTab, " ")
strTemp = ReplaceString(strTemp, vbCrLf, " ")

If InStr(1, strTemp, ",") > 0 Then
strTemp = Chr(34) & strTemp & Chr(34)
End If
strHolder = strHolder & strTemp & strFldDelimiter
End If
Next intCounter

strHolder = Left$(strHolder, Len(strHolder) - Len(strFldDelimiter))
Print #1, strHolder

.MoveNext
Loop

End With 'rs1

Beep


funExportToTabDelimited_Exit:
Close #1
rs1.Close
rs2.Close
db.Close
Set rs1 = Nothing
Set rs2 = Nothing
Set db = Nothing
Exit Function

funExportToTabDelimited_Error:


End Function




--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

As far as I know the Cross tab query is unique to Access. MS SQL may have
something like it in the later versions.

Yes is possible to export the results of query that returns records.
However, you are limited to 255 columns, so you would have to find a way
to split the number of columns that is returned into multiple groups of
255 or less. In addition, you would have to have some method of tying the
records from query one to the records in query two (a primary key that is
passed in each group of records or a set of unique values).

If I can find it I will see if I can dig out some old code I used to do
something similar for getting data out of Access into a format that SAS
could use.


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

 

[Lucas_london via AccessMonster.com]

Hi John,

Thanks for the code. Have to admit I'm not really a VBA whizz so will take me
a while to understand the code. In the meantime, I'll try to get hold of the
latest version of Access (Access 12) has I think it accomadates thousands of
columns!

Thanks,

Lucas

Here is a VBA routine I used to export data with more than 255 columns.
Note that I had two recordsets based on queries. The queries had the same
number of records and the records were ordered in a unique order by same set
of fields (or the primary key). The first query had the unique order fields
as the first fields in the query. The second query did not return those
fields (not in the select list).

Hope you can expand this to cover your situation.

Public Function funExportToTabDelimited()
Dim db As database
Dim rs1 As Recordset, rs2 As Recordset
Dim strHolder As String, strTemp As String
Dim intCounter As Integer
Dim strFldDelimiter As String

strFldDelimiter = vbTab 'or Comma "," or Other value

Set db = CurrentDb
Set rs1 = db.OpenRecordset("tblExportValues", dbOpenDynaset)
Set rs2 = db.OpenRecordset("tblExportValues_Section2", dbOpenDynaset)

strHolder = db.Name
strHolder = Left(strHolder, Len(strHolder) - Len(Dir(strHolder)))

Open strHolder & "DataFlatFile" & Format(Date, "_mm-dd-yy") & ".txt" For
Output As #1

With rs1
strHolder = ""
'Get Field Names
For intCounter = 0 To .Fields.Count - 1
If intCounter <> 1 Then
strHolder = strHolder & Left$(.Fields(intCounter).Name, 8) &
strFldDelimiter
End If
Next intCounter
For intCounter = 0 To rs2.Fields.Count - 1
If intCounter > 1 Then
strHolder = strHolder & Left$(rs2.Fields(intCounter).Name, 8) &
strFldDelimiter
End If
Next intCounter

'Write Field Names to file
strHolder = Left(strHolder, Len(strHolder) - Len(strFldDelimiter))
Print #1, strHolder

'Get field contents for each record and write to file
Do While Not .EOF

'get matching line of data in secondary file
strHolder = "SurveyDocId ='" & .Fields("SurveyDocID") & "'"
rs2.FindFirst strHolder
strHolder = vbNullString

For intCounter = 0 To .Fields.Count - 1
If intCounter <> 1 Then
strTemp = .Fields(intCounter) & ""
strTemp = ReplaceString(strTemp, vbTab, " ") 'Strip Tabs
strTemp = ReplaceString(strTemp, vbCrLf, " ") 'Strip C/R

'----Surround with Quotes if appropriate
If strFldDelimiter <> "|" Then
If InStr(1, strTemp, ",") > 0 Then
strTemp = Chr(34) & strTemp & Chr(34)
End If
End If

strHolder = strHolder & strTemp & strFldDelimiter
End If
Next intCounter

For intCounter = 0 To rs2.Fields.Count - 1
If intCounter > 1 Then
strTemp = rs2.Fields(intCounter) & ""
strTemp = ReplaceString(strTemp, vbTab, " ")
strTemp = ReplaceString(strTemp, vbCrLf, " ")

If InStr(1, strTemp, ",") > 0 Then
strTemp = Chr(34) & strTemp & Chr(34)
End If
strHolder = strHolder & strTemp & strFldDelimiter
End If
Next intCounter

strHolder = Left$(strHolder, Len(strHolder) - Len(strFldDelimiter))
Print #1, strHolder

.MoveNext
Loop

End With 'rs1

Beep

funExportToTabDelimited_Exit:
Close #1
rs1.Close
rs2.Close
db.Close
Set rs1 = Nothing
Set rs2 = Nothing
Set db = Nothing
Exit Function

funExportToTabDelimited_Error:

End Function

As far as I know the Cross tab query is unique to Access. MS SQL may have
something like it in the later versions.

[quoted text clipped - 74 lines]

 

[John Spencer]

No, Access is limited to 255 columns in a table or in a query. There is
no change in the limits.

Excel 2007 will support over a million rows and I don't remember how
many thousand columns.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Hi John,

Thanks for the code. Have to admit I'm not really a VBA whizz so will take me
a while to understand the code. In the meantime, I'll try to get hold of the
latest version of Access (Access 12) has I think it accomadates thousands of
columns!

Thanks,

Lucas

Here is a VBA routine I used to export data with more than 255 columns.
Note that I had two recordsets based on queries. The queries had the same
number of records and the records were ordered in a unique order by same set
of fields (or the primary key). The first query had the unique order fields
as the first fields in the query. The second query did not return those
fields (not in the select list).

Hope you can expand this to cover your situation.

Public Function funExportToTabDelimited()
Dim db As database
Dim rs1 As Recordset, rs2 As Recordset
Dim strHolder As String, strTemp As String
Dim intCounter As Integer
Dim strFldDelimiter As String

strFldDelimiter = vbTab 'or Comma "," or Other value

Set db = CurrentDb
Set rs1 = db.OpenRecordset("tblExportValues", dbOpenDynaset)
Set rs2 = db.OpenRecordset("tblExportValues_Section2", dbOpenDynaset)

strHolder = db.Name
strHolder = Left(strHolder, Len(strHolder) - Len(Dir(strHolder)))

Open strHolder & "DataFlatFile" & Format(Date, "_mm-dd-yy") & ".txt" For
Output As #1

With rs1
strHolder = ""
'Get Field Names
For intCounter = 0 To .Fields.Count - 1
If intCounter <> 1 Then
strHolder = strHolder & Left$(.Fields(intCounter).Name, 8) &
strFldDelimiter
End If
Next intCounter
For intCounter = 0 To rs2.Fields.Count - 1
If intCounter > 1 Then
strHolder = strHolder & Left$(rs2.Fields(intCounter).Name, 8) &
strFldDelimiter
End If
Next intCounter

'Write Field Names to file
strHolder = Left(strHolder, Len(strHolder) - Len(strFldDelimiter))
Print #1, strHolder

'Get field contents for each record and write to file
Do While Not .EOF

'get matching line of data in secondary file
strHolder = "SurveyDocId ='" & .Fields("SurveyDocID") & "'"
rs2.FindFirst strHolder
strHolder = vbNullString

For intCounter = 0 To .Fields.Count - 1
If intCounter <> 1 Then
strTemp = .Fields(intCounter) & ""
strTemp = ReplaceString(strTemp, vbTab, " ") 'Strip Tabs
strTemp = ReplaceString(strTemp, vbCrLf, " ") 'Strip C/R

'----Surround with Quotes if appropriate
If strFldDelimiter <> "|" Then
If InStr(1, strTemp, ",") > 0 Then
strTemp = Chr(34) & strTemp & Chr(34)
End If
End If

strHolder = strHolder & strTemp & strFldDelimiter
End If
Next intCounter

For intCounter = 0 To rs2.Fields.Count - 1
If intCounter > 1 Then
strTemp = rs2.Fields(intCounter) & ""
strTemp = ReplaceString(strTemp, vbTab, " ")
strTemp = ReplaceString(strTemp, vbCrLf, " ")

If InStr(1, strTemp, ",") > 0 Then
strTemp = Chr(34) & strTemp & Chr(34)
End If
strHolder = strHolder & strTemp & strFldDelimiter
End If
Next intCounter

strHolder = Left$(strHolder, Len(strHolder) - Len(strFldDelimiter))
Print #1, strHolder

.MoveNext
Loop

End With 'rs1

Beep

funExportToTabDelimited_Exit:
Close #1
rs1.Close
rs2.Close
db.Close
Set rs1 = Nothing
Set rs2 = Nothing
Set db = Nothing
Exit Function

funExportToTabDelimited_Error:

End Function

As far as I know the Cross tab query is unique to Access. MS SQL may have
something like it in the later versions.

[quoted text clipped - 74 lines]

Lucas