Hi all,

This one is complicated for me to explain but I am trying to see how to complete this task:

I have an imported spreadsheet that is placed into a table. I need to pull certain records from it.

1. Imported Fields: [Name], [A], [B], [C]

2. When data is imported, the majority of records have One [Name], and items in [A], [B], and [C].

3. SOME imported records have a [name], but mulitple lines in [A], [B], and [C]. The multiple lines however, leave the [name] field blank. (e.g. 3 rows of data, but the name is only listed once in row 1. Rows 2 and 3 do not have a name listed, but they do have data in [A], [B], and [C]

When I do a query on name, I will only get the row where the name exists, but not the rows under.

4. Between each name, there are specific characters (dashes) to show where the next record begins.

I need to find an easy way to pull the data. I'm thinking along the lines of copying the persons name (which are unique) to each row below until it hits the end of record characters (dashes) so at least if I query by name, all the rows of data will come up?

I attached a photo to give an example.

This post has been edited by off1c3r: Mar 9 2012, 07:47 PM

You need to modify your import so that you add an autonumber field to each record. (It's part of the wizard).
Otherwise you have no guarantee that Access will retain the original sequence when you use the records elsewhere.

The update for the names will require you to run a vba procedure - it cannot realistically be done any other way.

Change the table name where indicated in the procedure below.

Sub setname()
Dim db As Database
Dim rs As DAO.Recordset
Dim LastName As String

LastName = "Unknown"
Set db = CurrentDb
Set rs = db.OpenRecordset("Select * from xltable order by ID") ' set table name here
If rs.RecordCount = 0 Then GoTo exitsub ' no records
rs.MoveFirst
Do Until rs.EOF

If Trim(Nz(rs![Name], "")) = "" Then ' name is empty
rs.Edit
rs![Name] = LastName
rs.Update

ElseIf Left(Nz(rs![Name], ""), 1) = "/" Then
' do nothing for ///

Else ' must be name so store it
LastName = rs![Name]

End If

rs.MoveNext
Loop

exitsub:

rs.Close
Set rs = Nothing
Set db = Nothing

End Sub