Format scattered information in Access table

[sanniep]

Hi guys and girls,

I am trying to format a table with scattered information to the right format. The table looks like
this.

As you can see their are a, per user, different number of rows containing activities and time. Between the username and activities is an empty row. I would like to fill the Name & Tel in the empty cells in the activity & time rows. It should look like this.

How do I delete the empty rows and fill the empty Name & Tel cells with the Name and Tel from the cells above?

I am thinking of a VB script or SQL query that adds a rownumber and counts the row in which it encounters a Name. Because theirs is always 1 empty row you I would think something like:
- When encountered a value in column Name
- take rownumber +1
- remember the rownumbers untill you encounter a new value in column Name
- enter the Name and Tel in the corresponding columns untill you reach the rownumber with a new value in Name
- and then some sort of loop

I hope someone can help me out!

Thanks, Sander

[rsmccli]

Hi guys and girls,

I am trying to format a table with scattered information to the right format. The table looks like
this.

As you can see their are a, per user, different number of rows containing activities and time. Between the username and activities is an empty row. I would like to fill the Name & Tel in the empty cells in the activity & time rows. It should look like this.

How do I delete the empty rows and fill the empty Name & Tel cells with the Name and Tel from the cells above?

I am thinking of a VB script or SQL query that adds a rownumber and counts the row in which it encounters a Name. Because theirs is always 1 empty row you I would think something like:
- When encountered a value in column Name
- take rownumber +1
- remember the rownumbers untill you encounter a new value in column Name
- enter the Name and Tel in the corresponding columns untill you reach the rownumber with a new value in Name
- and then some sort of loop

I hope someone can help me out!

Thanks, Sander

I think you should have two other separate tables: name/tel and other personal info in one, and another table for activity type.

To add a Primary Key (or "row number") the easiest way would be to insert an Autonumber field at the beginning of the table and assign it as the PK.

After that it would be pretty easy to loop through the table and fill in or delete what is needed. If you didn't change the table at all you could update the names and tel first, then delete any erroneous records.

[sanniep]

I think you should have two other separate tables: name/tel and other personal info in one, and another table for activity type.

To add a Primary Key (or "row number") the easiest way would be to insert an Autonumber field at the beginning of the table and assign it as the PK.

After that it would be pretty easy to loop through the table and fill in or delete what is needed. If you didn't change the table at all you could update the names and tel first, then delete any erroneous records.

Hey rsmccli,

Thanks for your reply but I still can't seem to figure this one out.... Sorry.. I don't understand how having 2 tables (personal info and activities) would help me solve this.

I can add a PK to the existing table but I would still need a script which does the looping and filling. Can you help me out with the VB script?

I'm such an VB-noob...

Hope you, or someone else, can help me out!

Sander
NL

[rsmccli]

I would definitely try this on a backup of the table. You may need to do some fiddling.

Public Function FixTable()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strName As String
Dim strTel As String  'or possibly Long

Set db = CurrentDb
Set rs = db.OpenRecordset("YourTableName")

With rs
  If Not .BOF And Not .EOF Then
    .MoveFirst
    Do Until .EOF
      If Len(.Fields("Naam") & "") > 0 Then
        strName = .Fields("Naam")
        strTel = .Fields("Tel")
        .MoveNext
        Do Until Len(.Fields("Naam") & "") > 0
          .Edit
          .Fields("Naam") = strName
          .Fields("Tel") = strTel
          .Update
          .MoveNext
          If .EOF Then
            Exit Do
          End If
        Loop
      Else
        .MoveNext
      End If
    Loop
  End If

  .MoveFirst
  Do Until .EOF
    If Not Len(.Fields("Activity") & "") > 0 Then
      .Delete
      .MoveNext
    Else
      .MoveNext
    End If
  Loop
End With

End Function

The first portion goes through and copies the name (Naam) and tel (Tel) to the records after it. The second portion deletes any records that dont have anything in the activity field.

[Stewart Ross]

Hi. rsmccli has kindly provided code which will help you get round your problem, which was also correctly identified by rsmccli in post 2 - it is the design of your table which is the problem. It looks to me to be Excel-like data imported into a single table, complete with blank rows. It is not data suited to a relational database application.

Even if you apply the code provided you will end up with an unnormalised table, which does need to be normalised into separate contact details and activity tables to be maintainable. To give just one instance, the repeated contact names will lead to potential update anomalies unless the contact details are removed to a separate table.

This HowTo article on Database Normalisation and Table Structures may help with what has to be done to sort out your design.

-Stewart

[rsmccli]

By splitting up the data into different tables, you would cut down on any redundant data. So instead of having "John Smith" copied down a hundred times in your tables, you would just have it once, in a "Personal Information" table, then you would use the PK from that table to refer to John Smith.

An example with the extremely simple data you have put forth:
[PHP]
tblPersonal_Inf o
ID_Personal_Inf o(AutoNumber) Last_Name First_Name Tel
1 Doe John 1111
2 Liu Lucy 2222

tblActivities
ID_Activities(A utoNumber) Activity
1 Lunchbreak
2 Education
3 Call_Related
4 Non_Call_Relate d

tblSheduled_Act ivities
ID_Sched_Act (AutoNumber) ID_Personal_Inf o ID_Activities Time
1 1 3 0600
2 1 2 0900
3 2 3 0800
4 2 1 1830[/PHP]

So your table data ends up looking something like the one on the bottom. It is simple to make Access show and store the correct data using queries and forms.

Anyway, hope this helped and good luck to you.

rsmccli

[sanniep]

Hey rsmccli,

Excuse me for responding this late, I was defragmenting my brain on a 2 week vacation... Anyway, thanks very much for your perfect explanation!! I am going to figure this one out, I'm sure!

Thanks to Stewart Ross Inverness too for adressing the normalisation issues! Although that's my latest concern, I will definitely take make sure to solve this the right way.

Regards and thanks again, Sander