Hello,
Not sure if this is all to be solved in Access or can be solved in Excel, but since it has to end up on a Worksheet figure this is the forum to post.
Trying to transform data I get out of Access data with 4 fields RegID, SeminarNo, AttendTime, EvalComplete into an Excel like pivot where the rows are the RegID's and the Columns are the actual SeminarNo's, the data values would be an AttendTime and Eval Complete, plus I need two extra columns (CE Value, CE Credit) per seminar that are blank for the user to type in values on the Excel Sheet based on evaluation replies. This means each seminar would have 4 columns and I have 200 seminars so the Excel sheet would be 800 columns wide and with 1000 registrants as the rows.
I try to give example of the Access output and Excel desired display below, but can't seem to do it in any way with a pivot table or condensed Access query. The only way I see is to build 200 subqueries on for each seminar to get all the columns to show.
I will show a subset of the data which actually is made up of (1000 registrants and 200 seminars a cross join of 20,000, and those are left joined to the Attendance and EvalComplete tables to give the following.
Datafile from Access...
RegID Seminar AttendTime EvalComplete
1 100 9am Y
2 100 10am
3 100
4 100 10am Y
1 105 11am Y
2 105
3 105 11 am Y
4 105 11am
etc...
Desird Output in Excel
RegID Sem100 Sem100 Eval CeVal CeCredit Se105 Sem105Eval Ce...
1 9am Y 11am Y
2 10am
3 11am Y
4 10am Y 11am
......
That Excel Output keeps repeating to the right with increasing seminar numbers, the blanks are because every registrant for every seminar has to be displayed whether or not they attended or returned an eval. I am forcibly bound to delivering this output in this way. How I get there is my choice, but the data comes from normalized tables in Access, one with list of reg's, one with list of seminars, one table for seminar attendance and if evaluation per seminar. I can't see any query delivering the output and I can't see any pivot table or consolidation either. Open to either Access or Excel VBA code, but even there don't see an easy solution.
Any ideas most appreciated.
Not sure if this is all to be solved in Access or can be solved in Excel, but since it has to end up on a Worksheet figure this is the forum to post.
Trying to transform data I get out of Access data with 4 fields RegID, SeminarNo, AttendTime, EvalComplete into an Excel like pivot where the rows are the RegID's and the Columns are the actual SeminarNo's, the data values would be an AttendTime and Eval Complete, plus I need two extra columns (CE Value, CE Credit) per seminar that are blank for the user to type in values on the Excel Sheet based on evaluation replies. This means each seminar would have 4 columns and I have 200 seminars so the Excel sheet would be 800 columns wide and with 1000 registrants as the rows.
I try to give example of the Access output and Excel desired display below, but can't seem to do it in any way with a pivot table or condensed Access query. The only way I see is to build 200 subqueries on for each seminar to get all the columns to show.
I will show a subset of the data which actually is made up of (1000 registrants and 200 seminars a cross join of 20,000, and those are left joined to the Attendance and EvalComplete tables to give the following.
Datafile from Access...
RegID Seminar AttendTime EvalComplete
1 100 9am Y
2 100 10am
3 100
4 100 10am Y
1 105 11am Y
2 105
3 105 11 am Y
4 105 11am
etc...
Desird Output in Excel
RegID Sem100 Sem100 Eval CeVal CeCredit Se105 Sem105Eval Ce...
1 9am Y 11am Y
2 10am
3 11am Y
4 10am Y 11am
......
That Excel Output keeps repeating to the right with increasing seminar numbers, the blanks are because every registrant for every seminar has to be displayed whether or not they attended or returned an eval. I am forcibly bound to delivering this output in this way. How I get there is my choice, but the data comes from normalized tables in Access, one with list of reg's, one with list of seminars, one table for seminar attendance and if evaluation per seminar. I can't see any query delivering the output and I can't see any pivot table or consolidation either. Open to either Access or Excel VBA code, but even there don't see an easy solution.
Any ideas most appreciated.
