Partial transpose access table

[VAer]

Then I suggest you make time. You cannot expect any of us unpaid volunteers to give up our time giving you a solution when you aren't prepared to make the effort yourself.
Good luck with your problem. I will drop out now

No, that is not what I mean. At this point, I am asking for general idea about if it works in Access. Just like asking for someone to point me to right direction. Just making sure I am in the right direction. For sure, I will explore more. So far, this thread's question is more about: I want to do something(output table), is it possible to do it in Access crosstab or somewhere? I am not asking for detailed code. For example, if someone has experience with Access crosstab, he/she may tell me if it is possible to make certain date range in crosstab, at least I have a general idea of what can be expected. It is not like I am not thinking, I have spent time on thinking about dummy username, based on the comments. I was posting the "dummy username" idea during midnight (US Eastern time). If the idea may work, I will explore more; if someone tells me it is not possible, then I can give up and look into other alternative. This is not the only issue I have, at the meanwhile, I spend most of time on front end app features.

At least, from this thread, I learn the term "crosstab", and so far I know it is crosstab related issue. Otherwise, I don't even know where to begin searching. Before it, I used the term "partial transpose" to describe my issue.

But thank you. I will explore more before asking more questions.

[June7]

We have given you 'general idea' with the referenced links.

Displaying date with day name is a format setting. Forcing it to display in CROSSTAB column headers requires using Format() function. CROSSTAB column headers display in alphabetical order unless otherwise specified with IN() clause. And to better understand, review tutorials.

[VAer]

Many online examples displaying crosstab value by math calculation, I don't think it applies to my case, not sure which one to choose, it always returns error. For Crosstab Value, it is actually WorkNote.Note, which is string (short text). I have changed numeric field Hours to string field Note.

Need to watch some youtube demo videos.

[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.

[VAer]

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.

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;

[VAer]

Issue Solved.

[VAer]

Issue Solved. Posted deleted.

[VAer]

Solved. Thanks