Originally Posted by
ebrommhead
The sales rep names (SANAME) are in this query and I only want to get the reps that are in the query.
There are several ways to do this.... this is my idea.
You have a saved query "SalesInfoforCurrYrPlan". In code, I opened a query to get unique "sanames".
The SQL is
Code:
sSQL = "SELECT DISTINCT sifcpct.saname"
sSQL = sSQL & " FROM salesinfoforcurryrplan_crosstab AS sifcpct"
sSQL = sSQL & " ORDER BY sifcpct.saname;"
Then you fill an array that will be used to filter the query "SalesInfoforCurrYrPlan", which will then be written to individual worksheets.
I don't have your dB, so I faked it until I made it. (I created a table using the fields in the query)
Now that there is a "list" of unique sanames, create a loop that:
1) Creates a new worksheet
2) Rename the worksheet to the "saname"
3) open the recordset with only one saname (see below)
3) write the data to the worksheet
The SQL of the crosstab query will be in VBA with one additional line - a WHERE statement.
The SQL would be
Code:
TRANSFORM Sum(SalesInfoforCurrYrPlan.SumOfIHDAR_FCAMT1) AS SumOfSumOfIHDAR_FCAMT1
SELECT SalesInfoforCurrYrPlan.saname, SalesInfoforCurrYrPlan.plant, SalesInfoforCurrYrPlan.CSNAME, SalesInfoforCurrYrPlan.PLYear, Sum(SalesInfoforCurrYrPlan.SumOfIHDAR_FCAMT1) AS [Total Of SumOfIHDAR_FCAMT1]
FROM SalesInfoforCurrYrPlan
WHERE SalesInfoforCurrYrPlan.saname ='" & sa_Array(i) & "'"
GROUP BY SalesInfoforCurrYrPlan.saname, SalesInfoforCurrYrPlan.plant, SalesInfoforCurrYrPlan.CSNAME, SalesInfoforCurrYrPlan.PLYear
ORDER BY SalesInfoforCurrYrPlan.saname, SalesInfoforCurrYrPlan.plant, SalesInfoforCurrYrPlan.CSNAME, SalesInfoforCurrYrPlan.PLYear
PIVOT SalesInfoforCurrYrPlan.PLMth;
This is the Test code I used :
Code:
Public Sub TestArray()
Dim d As DAO.Database
Dim r As DAO.Recordset
Dim rc As Integer '<<-- record count
Dim sSQL As String
Dim sa_Array() '<<-- array if unique sanames
Dim i As Integer '<<-- just a counter
Set d = CurrentDb()
'query to get the unique saNames
sSQL = "SELECT DISTINCT sifcpct.saname"
sSQL = sSQL & " FROM salesinfoforcurryrplan_crosstab AS sifcpct"
sSQL = sSQL & " ORDER BY sifcpct.saname;"
Set r = d.OpenRecordset(sSQL)
If Not r.BOF And Not r.EOF Then
r.MoveLast '<<-- fill the recordset
rc = r.RecordCount ' <<-- number of unique sanames
r.MoveFirst
' now we know the number of exements needed in the array
ReDim sa_Array(rc)
'fill array
For i = 1 To rc
sa_Array(i) = r.Fields("saname")
r.MoveNext
Next
End If
'clean up
r.Close
Set r = Nothing
Set d = Nothing
' as a check that the names were added to the array, print to immediate window
For i = 1 To rc
Debug.Print sa_Array(i)
Next
End Sub
At the 30,000 foot level, the idea is to:
Pseudo-code
Code:
Fill an array with unique sanames
Create a new workbook
Loop through the sanames:
Create new worksheet
Rename worksheet
Fill worksheet
Save workbook
What do you think????