Converting rows into columns under certain conditions (agenda like)

[TVV]

Hi everbody,

As a rookie to Access I browsed many forums in search for an answer to my problem. I've got a relationship based ageda/calender for multiple people (100+). The information of those people is ordened in one table and their appointments in another. The appointments are already ordened on Peson-Id and the appointment (which consist of the "dd-mm-yyyy':' uu:mm to uu:mm 'on' [location]", i.e. "01-01-2011: 09:00 to 10:00 on London square").

Table one:
Person-Id | Name | Address | Postal code | City | Phone number
1 | Hank | Street 1 | xxxxx | City 1 | 0000-00000
2 | Thomas | Street 2 | xxxxx | City 2 | 0000-00001
3 | Mary | Straat 3 | xxxxx | City 3 | 0000-00002
4 | Clara | Straat 4 | xxxxx | City 4 | 0000-00003

Table two:
Person-Id | Appointment
1 | "01-05-2010: 08:00 to 11:00 on London square"
1 | "01-08-2010: 09:00 to 10:00 on London square"
1 | "01-01-2011: 09:00 to 10:00 on London square"
3 | "01-06-2010: 09:00 to 10:00 on London square"
3 | "01-07-2010: 09:00 to 10:00 on London square"

From these two tables I am trying to created a query that shows the person-id, name, phonenumber and there next 5 appointments. I did not put all the data I've got in the the tables above, but you can imagen that every person in the database has more then 5 appointments.

Desired output:
Person-Id | Phone number | Appointment_1 | Appointment_2 | Appointment_3 | Appointment_4 | Appointment_5
1 | 0000-00000 | "01-05-2010: 08:00 to 11:00 on London square" | "01-08-2010: 09:00 to 10:00 on London square" | "01-01-2011: 09:00 to 10:00 on London square" | "" | ""
3 | 0000_00002 | "01-06-2010: 09:00 to 10:00 on London square" | "01-07-2010: 09:00 to 10:00 on London square" | "" | "" | ""

I already used a query to get only the desired data from the first table, now I am looking for a way to order the second table. Which means I need to switch the rows to columns ordened by Person-Id.

If that works, an inner join to combine the two sets of data fixes the rest.

Can someone help me with this problem?

Thanks in advance!

Thank you!

 

[DCrake]

What do you intend to do with this data when you have transposed it? Apart from anything else, the data is not sequential and fragmented.

 

[TVV]

I would like to print the overview of the data when it's in the desired order. It's mainly to have a quick view of the upcoming appointments.

You call the data not sequal and fragmented. Sorry for not understanding (I'm Dutch, so there is some of this 'lost in translation'). The Person-Id provides the relation between the two tables. The appointments are ordered by dated and Person-Id. I can not combine the two tables, because there's more data in other areas of the database. Those other areas I did not shared here, because they are not relavent to my problem. My biggest problem is in the second table, to get a different query table which contains the next 5 appointments grouped by 'Person-Id' with the appointments as columns:

Person-Id | Appointment_1 | Appointment_2 | Appointment_3 | Appointment_4 | Appointment_5

Thanks!

 

[DCrake]

Lets work on the assumption that one user has at least 5 appointments. along with the persons name all on the horizontal plane; is it going to fit on one page even in landscape? According to your examples I think not.

People usually fal into these traps. Another example is have 50+ characters for a business name. Then using address lables or window envolopes that cannot cope with the maiximum length of the name. Why allow 50+ chars when only 40 fit on the address label?

 

[TVV]

I see what you mean. I would like to clarify my approach. Not all the people in the database are used in the query for the overview. It will only be used for a limited and selected number of people. Also the appointments are limited to certain appointments. Not all the appointments are put in the database. This is only for the most important appointments. Therefore not every person will be on the overview and some won't even have 5 appointments. That's why I am confident that I will fit on a overview to print this information.

 

[TVV]

I made a temporary table for the second table, TempAppointments:

Person-Id | Appointment_1 | Appointment_2 | Appointment_3 | Appointment_4 | Appointment_5


And by using a VBA function, the table will be emptied and after filled with the new information:

Public Function order appointments()
Dim db As DAO.Database
Dim rs1 As DAO.Recordset, rs2 As DAO.Recordset
Dim sSQL1 As String, sSQL2 As String, sSQL3 As String
Dim iI As Integer
DoCmd.SetWarnings False
sSQL1 = "DELETE * FROM TempAppointments"
DoCmd.RunSQL (sSQL1)
Set db = CurrentDb()
Set rs1 = db.OpenRecordset("select [Person-Id] As Id FROM [People] WHERE [Important]=True")
Do While Not rs1.EOF
sSQL2 = "INSERT INTO TempAppointments ([Person-Id]) VALUES ('" & rs1!Id & "')"
DoCmd.RunSQL (sSQL2)
Set rs2 = db.OpenRecordset("SELECT [Appointment] FROM [Appointments] WHERE [Person-Id]=" & rs1!Id & " ORDER BY [Appointment]")
iI = 1

Do While Not rs2.EOF
If iI < 6 Then
sSQL3 = "UPDATE TempAppointments SET [Appointment_" & iI & "] ='" & rs2!Appointment & "' WHERE [Person-Id]=" & rs1!Id
DoCmd.RunSQL (sSQL3)
End If
rs2.MoveNext
iI = iI + 1
Loop

rs1.MoveNext
Loop
DoCmd.SetWarnings True

End Function

This gave me a table which I ca join with the other data for the desired output.