Originally Posted by
isladogs
I'll drop back in briefly. Change Expression to First in the Total row for the Note field.
Also remove the direct link between the Employee and Teams table. Its superfluous and having two sets of links may cause problems.
Thank you, get a step closer. Date format error.
Now I have a question, since TeamName is dynamic, depends on front end combo box value. Should I just replace (Teams.TeamName)="team1" with (Teams.TeamName)=comboBoxTeam.Text ? Then run the TRANSFORM statement in visual studio?
I can actually delete crosstab query from Access(after copying the code to Visual Studio project) ? Let us say, Access query is called queryCrosstab, I cannot call queryCrosstab directly, since queryCrosstab does not know which team is selected by user.
Code:
TRANSFORM First(WorkNote.Note) AS FirstOfNote
SELECT Teams.Username, Employee.FirstName, Employee.LastName
FROM Teams INNER JOIN (Employee INNER JOIN WorkNote ON Employee.Username = WorkNote.Username) ON Teams.Username = WorkNote.Username
WHERE (((Teams.TeamName)="team1") AND ((WorkNote.WorkDate)<Now()) AND ((WorkNote.WorkDate)>=Now()-7))
GROUP BY Teams.Username, Employee.FirstName, Employee.LastName
ORDER BY Teams.Username
PIVOT WorkNote.WorkDate;