By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,486 Members | 2,169 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,486 IT Pros & Developers. It's quick & easy.

Format scattered information in Access table

P: 48
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
Apr 28 '08 #1
Share this Question
Share on Google+
6 Replies


rsmccli
P: 52
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.
May 1 '08 #2

P: 48
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
May 23 '08 #3

rsmccli
P: 52
I would definitely try this on a backup of the table. You may need to do some fiddling.
Expand|Select|Wrap|Line Numbers
  1. Public Function FixTable()
  2. Dim db As DAO.Database
  3. Dim rs As DAO.Recordset
  4. Dim strName As String
  5. Dim strTel As String  'or possibly Long
  6.  
  7. Set db = CurrentDb
  8. Set rs = db.OpenRecordset("YourTableName")
  9.  
  10. With rs
  11.   If Not .BOF And Not .EOF Then
  12.     .MoveFirst
  13.     Do Until .EOF
  14.       If Len(.Fields("Naam") & "") > 0 Then
  15.         strName = .Fields("Naam")
  16.         strTel = .Fields("Tel")
  17.         .MoveNext
  18.         Do Until Len(.Fields("Naam") & "") > 0
  19.           .Edit
  20.           .Fields("Naam") = strName
  21.           .Fields("Tel") = strTel
  22.           .Update
  23.           .MoveNext
  24.           If .EOF Then
  25.             Exit Do
  26.           End If
  27.         Loop
  28.       Else
  29.         .MoveNext
  30.       End If
  31.     Loop
  32.   End If
  33.  
  34.   .MoveFirst
  35.   Do Until .EOF
  36.     If Not Len(.Fields("Activity") & "") > 0 Then
  37.       .Delete
  38.       .MoveNext
  39.     Else
  40.       .MoveNext
  41.     End If
  42.   Loop
  43. End With
  44.  
  45. End Function
  46.  
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.
May 28 '08 #4

Expert Mod 2.5K+
P: 2,545
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
May 28 '08 #5

rsmccli
P: 52
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_Info
ID_Personal_Info(AutoNumber) Last_Name First_Name Tel
1 Doe John 1111
2 Liu Lucy 2222

tblActivities
ID_Activities(AutoNumber) Activity
1 Lunchbreak
2 Education
3 Call_Related
4 Non_Call_Related

tblSheduled_Activities
ID_Sched_Act (AutoNumber) ID_Personal_Info 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
May 28 '08 #6

P: 48
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
Jun 18 '08 #7

Post your reply

Sign in to post your reply or Sign up for a free account.