I need to collapse multiple rows into one

[MrMikeL]

Hello,

Apologies if this is answered elsewhere. I have a database with potentially multiple records per person. My goal is to collapse this down to a single record per person. Here is a sample of the data:



I am trying calculate a new field at the name level for each year the person attended. The code I have for that is as follows:

=IIf(([Tbl_Attendance].[session 1] is not null or [Tbl_Attendance].[session 2] is not null) and [Tbl_Attendance].[year] = 1970,"X"," ") as A70,
=IIf(([Tbl_Attendance].[session 1] is not null or [Tbl_Attendance].[session 2] is not null) and [Tbl_Attendance].[year] = 1971,"X"," ") as A71,


=IIf(([Tbl_Attendance].[session 1] is not null or [Tbl_Attendance].[session 2] is not null) and [Tbl_Attendance].[year] = 1972,"X"," ") as A72,
=IIf(([Tbl_Attendance].[session 1] is not null or [Tbl_Attendance].[session 2] is not null) and [Tbl_Attendance].[year] = 1973,"X"," ") as A73,
=IIf(([Tbl_Attendance].[session 1] is not null or [Tbl_Attendance].[session 2] is not null) and [Tbl_Attendance].[year] = 1974,"X"," ") as A74,
=IIf(([Tbl_Attendance].[session 1] is not null or [Tbl_Attendance].[session 2] is not null) and [Tbl_Attendance].[year] = 1975,"X"," ") as A75,
=IIf(([Tbl_Attendance].[session 1] is not null or [Tbl_Attendance].[session 2] is not null) and [Tbl_Attendance].[year] = 1976,"X"," ") as A76,
=IIf(([Tbl_Attendance].[session 1] is not null or [Tbl_Attendance].[session 2] is not null) and [Tbl_Attendance].[year] = 1977,"X"," ") as A77,
=IIf(([Tbl_Attendance].[session 1] is not null or [Tbl_Attendance].[session 2] is not null) and [Tbl_Attendance].[year] = 1978,"X"," ") as A78,
=IIf(([Tbl_Attendance].[session 1] is not null or [Tbl_Attendance].[session 2] is not null) and [Tbl_Attendance].[year] = 1979,"X"," ") as A79,

I would like a result as follows (remember, one record per person):

LAST NAME FIRST NAME A70 A71 A71 A73 A74 A75 A76 A77 A78 A79
ABRAMS MARK X X X X X

I have tried a few things, but I cannot get it to collapse down to a single record. When I add a GROUP BY clause, it does not like that I am excluding some of the fields used in the calculation and it gives me a run-time error.

Thanks for your help!

[orange]

Mike,

I have approved your post in order that others might see it and respond.

You might consider providing some context regarding the environment and issue you are attempting to resolve. You mention 1 record per person, but have given no info regarding the "persons".

You might want to structure your data/tables to ease querying and reporting.

It sounds like attendees -->attendedEvents<---EventByYear??

I often suggest the 30,000 ft overview to describe the issue to potential responders.

[Welshgasman]

I know of two options. The first I have used, the second is a little simpler?

http://allenbrowne.com/func-concat.html

https://www.accessmvp.com/thedbguy/c...itle=simplecsv

[CJ_London]

Misposted.

[CJ_London]

What’s wrong with using a crosstab query?

columnheading=‘A’ & right(year,2)
value=‘X’

and possibly use criteria for the sessions

although year is not a good name for a field as it is a reserved word

[June7]

Why does each expression begin with equal sign? Where are these expressions?

Show your SQL?

I would not use a space as alternate value. I would use Null.

Want to provide sample data? You can build (Go Advanced post editor) or copy/paste a table in post or attach file (again, Go Advanced).

[davegri]

Have a look at this. Got a little help from CoPilot, without too many annoying bugs to work out.
MikeL-davegri-v01.zip

[CJ_London]

Using the data kindly provided by davegri. This is my crosstab suggestion

TRANSFORM First("X") AS V
SELECT Tbl_Attendance.Person_FK
FROM Tbl_Attendance
WHERE (((Tbl_Attendance.[session 1]) Is Not Null)) OR (((Tbl_Attendance.[session 2]) Is Not Null))
GROUP BY Tbl_Attendance.Person_FK
PIVOT "A" & Right([attYear],2);

which produces this result

there are some differences - for example Person 4 year A71 is marked on mine but not on Davegri's - although there is a value in session 1. So perhaps I am not understanding the logic?

Attend_PK	Person_FK	session 1	session 2	attYear
33	4	X		1971

[davegri]

CJ_London said:

So perhaps I am not understanding the logic?

But I was the one misreading the logic. My solution required both Session 1 and Session 2 to be present but OP's logic indicated either.
To fix my example, query qPersonYear SQL should be:

SELECT tblPerson.Person_PK, Tbl_Attendance.[session 1], Tbl_Attendance.[session 2], Tbl_Attendance.attYearFROM tblPerson INNER JOIN Tbl_Attendance ON tblPerson.Person_PK = Tbl_Attendance.Person_FK
WHERE ((Not (Tbl_Attendance.[session 1]) Is Null)) OR ((Not (Tbl_Attendance.[session 2]) Is Null));

changing the AND to OR

[CJ_London]

Have to wait for the OP to respond and clarify as my method does not create a record, only a view- although it could be converted to an append or update query, I would question the rationale since the number of years could extend into the 80’s, 90’s etc

[MrMikeL]

BINGO!! This was the solution for me. I never would have figured out the TRANSFORM function, so thank you!

[June7]

CROSSTAB is an option the query wizard can help with.
It's also a choice on the ribbon query design tab
Here's some more info http://allenbrowne.com/ser-67.html