selecting row data and displaying horizontally

[AndrewOfAzotus]

I have a table like this:

Group Attendee Date
club1 person1 Jan-3
club1 person2 Jan-3
club1 person1 Jan-10
club1 person1 Jan-17
club1 person2 Jan-17 etc.

I would like to display thus:
club1
Dates Jan-3 Jan-10 Jan-17
person1 Y Y Y
person2 Y N Y

Is there a 'simple' way, or do I need to write some 'clever' code and build
a dynamic display on a form?

Many Thanks,
Andrew/

 

[Stefan Hoffmann]

hi Andrew,

club1
Dates Jan-3 Jan-10 Jan-17
person1 Y Y Y
person2 Y N Y

Is there a 'simple' way?

Use a pivot query:

TRANSFORM IIf(Nz(Count([Group]),0)>0,"Y","N")
SELECT Attendee
FROM yourTable
WHERE [Group] = "club1"
GROUP BY Attendee
PIVOT [Date];


mfG
--> stefan <--

 

[AndrewOfAzotus]

hi Andrew,

club1
Dates Jan-3 Jan-10 Jan-17
person1 Y Y Y
person2 Y N Y

Is there a 'simple' way?

Use a pivot query:

TRANSFORM IIf(Nz(Count([Group]),0)>0,"Y","N")
SELECT Attendee
FROM yourTable
WHERE [Group] = "club1"
GROUP BY Attendee
PIVOT [Date];


mfG
--> stefan <--

Stunning

Thank you stefan, it is much appreciated. Not sure I understand how it
works, mind you I never could get my mind around pivot tables. I did try
using the access wizard but couldn't get anywhere.

Thanks again.

Andrew/

 

[AndrewOfAzotus]

hi Andrew,

club1
Dates Jan-3 Jan-10 Jan-17
person1 Y Y Y
person2 Y N Y

Is there a 'simple' way?

Use a pivot query:

TRANSFORM IIf(Nz(Count([Group]),0)>0,"Y","N")
SELECT Attendee
FROM yourTable
WHERE [Group] = "club1"
GROUP BY Attendee
PIVOT [Date];


mfG
--> stefan <--

Stunning

Thank you stefan, it is much appreciated. Not sure I understand how it
works, mind you I never could get my mind around pivot tables. I did try
using the access wizard but couldn't get anywhere.

Thanks again.

Andrew/

Uh, one more question. I need to run this from VB varying the club.

Thanks,
Andrew/

 

[Stefan Hoffmann]

hi Andrew,

AndrewOfAzotus write:

Use a pivot query:
TRANSFORM IIf(Nz(Count([Group]),0)>0,"Y","N")
SELECT Attendee
FROM yourTable
WHERE [Group] = "club1"
GROUP BY Attendee
PIVOT [Date];

Uh, one more question. I need to run this from VB varying the club.

Private Sub ChangeQuery(AFilter As String)

Const QUERY As String = _
"TRANSFORM IIf(Nz(Count([Group]),0)>0,"Y","N") " & _
"SELECT Attendee " & _
"FROM yourTable " & _
"WHERE [Group] = % " & _
"GROUP BY Attendee " & _
"PIVOT [Date];"

Dim db as DAO.Database

Set db = CurrentDb
db.QueryDefs.Item("yourQuery").SQL = _
Replace(QUERY, "%", SQLQuote(AFilter))
Set db = Nothing

End Sub

Public Function SQLQuote(AString As String, _
Optional ADelimiter As String = "'") As String

SQLQuote = ADelimiter & _
Replace(AString, ADelimiter, ADelimiter & ADelimiter) & _
ADelimiter

End Function


mfG
--> stefan <--

 

[AndrewOfAzotus]

hi Andrew,

AndrewOfAzotus write:

Use a pivot query:
TRANSFORM IIf(Nz(Count([Group]),0)>0,"Y","N")
SELECT Attendee
FROM yourTable
WHERE [Group] = "club1"
GROUP BY Attendee
PIVOT [Date];

Uh, one more question. I need to run this from VB varying the club.

Private Sub ChangeQuery(AFilter As String)

Const QUERY As String = _
"TRANSFORM IIf(Nz(Count([Group]),0)>0,"Y","N") " & _
"SELECT Attendee " & _
"FROM yourTable " & _
"WHERE [Group] = % " & _
"GROUP BY Attendee " & _
"PIVOT [Date];"

Dim db as DAO.Database

Set db = CurrentDb
db.QueryDefs.Item("yourQuery").SQL = _
Replace(QUERY, "%", SQLQuote(AFilter))
Set db = Nothing

End Sub

Public Function SQLQuote(AString As String, _
Optional ADelimiter As String = "'") As String

SQLQuote = ADelimiter & _
Replace(AString, ADelimiter, ADelimiter & ADelimiter) & _
ADelimiter

End Function


mfG
--> stefan <--

No wonder I couldn't work it out, that's a long way round to run a simple
query.

Once again, Many Thanks.

Andrew/