Transpose data in MS Access

[JBLoafer]

My task is to allocate Tasks to People for any/every day of a single week. There are up to 100 different tasks and about 1000 people to choose from but a typical allocation would involve perhaps 200 people. I have successfully used a form that presents a persons Name at the top of the form, and shows, for each Day: whether they are available, a combo box to select Tasks from a predefined list and a Time when they should start the Task. A report easily prints this by Name, without any transposition, and it is great for each person to see Task they have to do on which day and the time they should turn up.
However, when it comes to supervision on each day, the manager wants to see a layout with column headings Task, Time1, Day1, Time2, Day2, . . . Time7,Day7 with a list of Tasks with assiciated Names for each of the days. One of the threads presents a solution that uses Dlookup to fetch the data but I believe that worked because the size of the data was determinate.


A solution to this would be welcome as I have had to create a cludge using a subform/table to store the data as Task, Day1Name, Day1Time etc, using some VB routines, but the user has to take great care, for instance when a person is allocated a different task, the user has first to select a different line on this second table. It is even worse when the allotted task has to change or is deleted. The info is bound to be inconsistent.
To sum up, there are only 7 days, not a perpetual calendar, but tasks and people are many and varied.

[June7]

Review this recent thread https://www.accessforums.net/showthr...lias-Variables

[JBLoafer]

Thanks June7 for your suggestion. However I believe after looking at your suggestion, that my problem is a different type of transposition. In an Excel Transpose the row and column headings are transposed and the data shuffled accordingly. In my case, the Data becomes the New Row headings and the old Row Heading becomes the new data. Column headings stay the same.
I don't know whether you can help with this or whether I should be in a different forum. Further advice obviously most welcome.
JBLoafer

[June7]

It can be done. Can you provide project for analysis? Make copy, remove confidential data but leave enough records for testing. Or data examples in Excel. Show source data structure and the output structure you want.

[JBLoafer]

Thanks again June7.
I have put together an Excel sheet that shows sample data. It is difficult to match the Before and After for you, but the intent is clearly shown. However I don't know how to let you have a copy of the XL file.
As it happens I have managed the job with a series of queries, but it is not very flexible regarding the fixed days. Some events ar 2 days, some 5.
The trick I employed to get the required format was to concatenate the Helper ID with the Task. That made the tasks unique to a person and a Xtab properly provides multiples of the same task in the Row Heading column. By using multiples copies of the HelperID,Name, their Name can be substituted for the ID on its own. I then strip out the Helper ID into another column and use that to link in the Time fields to produce the final output. 1 query for the each of the 3 days + 4 others to do the linking and a macro to make it all happen.
If you let me know how to insert a file I'll post it so you can see the result required.
Thanks again

[June7]

Attach to post. Attachment manager is below the Advanced post editor. Your solution is probably what I would have come up with. Otherwise, VBA is the alternative.

[JBLoafer]

Attach to post. Attachment manager is below the Advanced post editor. Your solution is probably what I would have come up with. Otherwise, VBA is the alternative.

Thanks once again June7 for keeping an eye on me. Attached is a .accb file that has the two example tables for your interest. If you have a solution that accepts any and all days of the week, as each event uses different days of the week and a different number of days, that would be brilliant. At this time, I have decided that a common solution to all of them would be too complex and have kept the base data in another database with the custom manipulations for each event in separate linked databases, to suit each different event. There's a lot of changes to queries, tables and reports so that there's a lot of work which is not designer friendly to customise, nevermind the possibility of the user bein able to do it!
The helper address list etc may be kept up to date from anywhere.

[June7]

Would be nice to have the queries you already built and all the tables involved.

What is the Helper ID, the only ID in these tables is Address ID. How does Address ID relate?

So each record can have more than one task and task will not always have a time?

ManagerData is the desired output? There are tasks in Original not in Manager - why is that?

BTW, advise no spaces in names, as in Address ID, better would be AddressID or Address_ID.

[JBLoafer]

Hi June7. I can see you are puzzled by a number of things in my previous attached data. Sorry about that. The latest version has been completely changed to include real data with a macro that sequences the various queries needed to do the transform.
The "helpers Contacts" table is the master name and address table and I have stripped off the rest of the info.
The starting table is Deployment, which is generated by a query based on the "Helpers Contacts" and a linked table in to which helpers have signified their availability. It has extra fields for the planner to input info and also eliminates those helpers that are not available. The table Deployment is presented on a form that shows by using conditional formatting which of the days they are available, and the planner of the event uses the combos, (one per day) that show the complete list of tasks from which they pick a suitable task for that "helper". The planner also includes a time when they should be present only if it differs from the standard arrival time.
All these picks and times can be seen in the final Deployment table in the attached database.
This format is useful for each helper to see what tasks they have been allocated, typically done by email.

The event manager on the day needs to quickly determine who is doing what, hence the need to present the info listed by Task, ie the data has to be transformed, and the queries in the sample database achieve this. The Task Names table is used to provide a sort for the event manager's report so it is in a consistent sequence.

As I remarked before, these events can be anywhere between 1 and 5 days of the 7 day week. The form I have for picking copes with all 7 days, that's easy, but the remaining manipulations as you can see are rather specific for day and number of days, particularly for column headings and so on. I have to admit I haven't tried but I've been trying to solve the transform for about a month, and exploring other ways too, so I haven't tried expanding the capability yet!
Let me know what you think of the transform sequence if you can spare the time, and help with the 7 day version would be appreciated.
Thanks again for your interest.
JBLoafer

[June7]

Can't help if I don't have the tables and some data to work with. Seeing what you have done with the queries will help me better understand what you need and evaluate the approach. I really don't want to even try to replicate what you have done.

[JBLoafer]

Sorry June7. I forgot to add the sample database! Now added as a .zip as the original was too big. I hope it gives you a good idea of the project and the queries required to achieve the result for one specific event.
Regards
JBLoafer

[June7]

I think the following sequence achieves the same results:

Query1:
SELECT ID, Surname & ", " & [First Name] As Helper, [FriTask] As Tasks, 5 As Day, FriTime As Times FROM Deployment WHERE Not FriTask Is Null
UNION SELECT ID, Surname & ", " & [First Name], [SatTask], 6 As Day, SatTime FROM Deployment WHERE Not SatTask Is Null
UNION SELECT ID, Surname & ", " & [First Name], [SunTask], 7 As Day, SunTime FROM Deployment WHERE Not SunTask Is Null;

There is no query designer or wizard for UNION, must type in the SQL View editor. UNION queries have a limit of 50 lines and VBA doesn't like to work with them.

Query2:
TRANSFORM First(TasksByDayUNION.ID) AS [FirstOfAddress ID]
SELECT TasksByDayUNION.ID, TasksByDayUNION.Helper, TasksByDayUNION.Tasks
FROM TasksByDayUNION
GROUP BY TasksByDayUNION.ID, TasksByDayUNION.Helper, TasksByDayUNION.Tasks
PIVOT TasksByDayUNION.Day;

Query3:
SELECT TaskNames.Seq, TasksByDayUNION_Crosstab.Tasks, Deployment.FriTime, IIf(Not IsNull([5]),[Helper]) AS HelperFri, Deployment.SatTime, IIf(Not IsNull([6]),[Helper]) AS HelperSat, Deployment.SunTime, IIf(Not IsNull([7]),[Helper]) AS HelperSun
FROM Deployment RIGHT JOIN (TaskNames RIGHT JOIN TasksByDayUNION_Crosstab ON TaskNames.[Task Name] = TasksByDayUNION_Crosstab.Tasks) ON Deployment.ID = TasksByDayUNION_Crosstab.ID
ORDER BY TaskNames.Seq;

No need to make and append tables. Also no need for the macro. Just use the last query as RecordSource for a report.

Don't know why couldn't work for all 7 days of week. Would have to know more about data.

[JBLoafer]

Thanks June7 - very interesting and I will certainly try that out first until I get the hang of it and then give it a trial to add a Monday, as that is the next event, then if all goes well extend to 7 days. I have to tread warily as the events won't happen if it doesn't work. At least I will have my cludge method and a lot of query changes if I get lost in the SQL. Its strange in that many many years ago, in the days of really big and floppy disks, the first relational databases required SQL query definitions and I used to design them regularly. Time to brush up I think.
Thanks for your help and support. I guess the thread should really be shut down as the original problem has been sorted. I'll leave it open for a few days just in case though.
Many thanks again
JBLoafer