Select Query To Pull Several Rows Of Data, Office 2010
Mar 9 2012, 07:42 PM
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
Untitled.png ( 6.69K ) Number of downloads: 18
Mar 10 2012, 05:44 AM
From: Oadby Leics, UK
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.
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
Do Until rs.EOF
If Trim(Nz(rs![Name], "")) = "" Then ' name is empty
rs![Name] = LastName
ElseIf Left(Nz(rs![Name], ""), 1) = "/" Then
' do nothing for ///
Else ' must be name so store it
LastName = rs![Name]
Set rs = Nothing
Set db = Nothing
|Go to Top · Lo-Fi Version||Time is now: 19th June 2013 - 03:11 PM|