Structure question to get a report output

[Janis Rough]

tblRouter:routerName
tblUtilizationHistory:routerName, weekending date, utilization%
The match key is the routerName

What I want to do is create a flatfile output of the fields: routerName &
columns of the last 6 weeks utilization percentages for each router.They
want this report output to Excel like this.

RouterName, 8/25, 9/1, 9/7
Router ubrA... 50%, 80%, 81%
Router ubrB... 70%, 75%, 76%
Etc.


If I relate them with a right outer join then I get this right?

Routername, Date, utilization %
UbrA 8/25, 50%
UbrA 9/1, 80%
UbrA 9/7, 81%
UbrB 8/25, 70%
UbrB 9/1, 75%
UbrB 9/7, 76%

How could I output this to a flat file with rows and columns like the first
example? Do I need a query with a subquery or do I need a different
structure?

Tia,

 

[Guest]

Hi Janis,

You should be able to do this using a crosstab query, as long as the
utilization% field is numeric. Create a new query. Dismiss the Add Tables
dialog without adding any tables. In query design view, click on View > SQL
View. You should see the word SELECT highlighted. Copy the following SQL
statement (Ctrl C) and paste it into the SQL view (Ctrl V), replacing the
SELECT keyword:

TRANSFORM Sum(tblUtilizationHistory.[utilization%]) AS [SumOfutilization%]
SELECT tblUtilizationHistory.routerName
FROM tblUtilizationHistory
GROUP BY tblUtilizationHistory.routerName
ORDER BY tblUtilizationHistory.routerName, tblUtilizationHistory.[weekending
date]
PIVOT tblUtilizationHistory.[weekending date];


If you wish, you can create an unbound form with a command button on it for
exporting your query to Excel.

Option Compare Database
Option Explicit

Private Sub cmdOutputToExcel_Click()
On Error GoTo ProcError

Dim strPath As String
strPath = CurrentProject.Path

DoCmd.OutputTo acOutputQuery, "qryCrosstab", acFormatXLS, _
strPath & "\Percent Utilization.xls" ', AutoStart:=-1

MsgBox "The router usage has been exported to the file" & vbCrLf _
& """Percent Utilization.xls"" in the folder:" & vbCrLf _
& strPath, vbInformation, "Export Complete..."

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cmdOutputToExcel_Click..."
Resume ExitProc
End Sub


For more information on Crosstab Queries, please see this link:

Crosstab Queries
http://www.access.qbuilt.com/html/crosstab_queries.html


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________