INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Need help Combining records

Need help Combining records

(OP)
I am having a difficult time trying to figure out how to do this and could use some help...

I have a table that contains a member's last name, first name, and date of birth.

This table gets populated by another source that I have no control over and sometimes a couple of
the records need to be combined. For example:

Last		First	DOB
Smith		John	12/1/63
Williams	
		Kyle	4/21/66
Jones		Bob	5/6/65
Johnson		
		Steve	7/11/66
 

In this case I would look through the table for records with Last Names without First names and combine this
record with the next record found without a last name into one record.

I would find the last name Williams (which is missing the first name) and then the next record missing the
first name which is Kyle and combine those into one record:

Williams Kyle 4/21/66

and then off to find the next occurrence to combine:

Johnson Steve 7/11/66

So the end result table would look like this:
Last		First	DOB
Smith		John	12/1/63
Williams	Kyle	4/21/66
Jones		Bob	5/6/65
Johnson		Steve	7/11/66
 


Thanks!

RE: Need help Combining records

Do you have an autonumber or other sequence type field that uniquely identifies the order of the records?

Duane
Hook'D on Access
MS Access MVP

RE: Need help Combining records

(OP)
No, there isn't an autonumber or other sequence type field that uniquely identifies the order of the records...

Because this table gets populated by another source that I have no control over, is there a way for me to add one?

thanks

RE: Need help Combining records

I don't know how it is populated and why you can't add an autonumber field.

Duane
Hook'D on Access
MS Access MVP

RE: Need help Combining records

(OP)
The table is created from an excel spreadsheet using a DoCmd.TransferSpreadsheet command
and I do not know how to add an autonumber field.

The excel spreadsheet can at times be missing the first name or the last name fields
and could look like this:

#	Last		First	DOB
1	Smith		John	12/1/63
2	Williams	
3			Kyle	4/21/66
4	Jones		Bob	5/6/65
5	Johnson		
6			Steve	7/11/66
 
I am looking for a way to combine records missing the first name with the next record
missing the last name.

What is the most direct way to do this within Access?

thanks again

RE: Need help Combining records

Reason Duane is asking about a sequence number is because in databases there is no guarantee of order unless you have a way to force it. The fact the spreadsheet looks like that does not guarantee the records will go in and you could get those records back out in that order. Any process to fix this will need to ensure it is in the same order as the spreadsheet. I would put the order number in the spreadsheet before importing. You could manually add an autonumber field to the table after import, or you could import into an existing table with an autonumber field.
Once you have a way to ensure the ordering you will likely have to do this in code. You could do it through a sequence of queries, but it would be challenging. If the records are exactly as you describe (no other patterns), I would simply make a recordset order by my sequence and read the records down, storing the last name in a variable. If the last name is blank I would then input the previous last name into the current last name field. Once I was done copying the last name down for all records, I would run a delete query on the table to delete all records where first name is empty.

This is untested code but something like

CODE

Dim rs as dao.recordset
Dim tempName as string
dim strSql as string
strSql = "Select * from yourTableName order by YourSequenceField"
set rs = currentdb.openrecordset (strSql,dbopendynaset)
Do while not rs.eof
  if rs!lastname & "" = "" then
    rs.edit
    rs!lastname = tempname
    rs.update
  else
    TempName = rs!LastName
  end if
  rs.movenext
loop
strSql = "delete * from yourTable where FirstName is null"
currentDb.execute strSql 

RE: Need help Combining records

Thanks MajP

I added:

rs.close
Set rs = Nothing

to the end of your code and imported the # field from the spreadsheet but received a
run-time error '3131' Syntax error in FROM clause.

When I looked at the table I realized that there were a couple duplicate records which I am guessing caused this error to occur.
I am looking for a way to remove one of each duplicate from this table before trying it again...

RE: Need help Combining records

My table name did have a space in it's name, so I changed it and now when I run this
code it wipes out the whole table:

The table is created with the following command:

DoCmd.TransferSpreadsheet acImport, 8, "members_tbl", "C:\MemberList\Newlist.xlsx", True

CODE -->

Private Sub update_Click()  ' update members_tbl
Dim rs As DAO.Recordset
Dim tempName As String
Dim strSql As String

strSql = "Select * from members_tbl order by Seq"

Set rs = CurrentDb.OpenRecordset(strSql, dbOpenDynaset)


Do While Not rs.EOF
  If rs!Last & "" = "" Then
    rs.Edit
    rs!Last = tempName
    rs.update
  Else
    tempName = rs!Last
  End If
  rs.MoveNext
Loop
strSql = "delete * from members_tbl where First is null"
CurrentDb.Execute strSql

rs.Close
Set rs = Nothing
 
End Sub 

Here is a small section of the table:

Seq	Last	First	DOB
1	Smith		John
2	Williams		
3			Kyle
4	Jones		Bob
5	Johnson		
6			Steve
1	Smith		John
2	Williams		
3			Kyle 

Thanks!

RE: Need help Combining records

can you comment out the following line
'currentDb.execute strSql
and rerun the code? See what the table looks like.

RE: Need help Combining records

RE: Need help Combining records

(OP)
I noticed that I had a copy/paste error on my previous post
(DOB Column was omitted) and the section of the table before the
process actually looks like this:
Seq	Last	First	DOB
1	Smith	John	12/1/63
2	Williams		
3		Kyle	4/21/66
4	Jones	Bob	5/6/65
5	Johnson		
6		Steve	7/11/66
1	Smith	John	12/1/63
2	Williams		
3		Kyle	4/21/66 

Quote:


can you comment out the following line
'currentDb.execute strSql
and rerun the code? See what the table looks like.

I comment out the line 'currentDb.execute strSql
and run the code, then the table looks like this:
	
Seq	Last	First	DOB
1	Smith	John	12/1/63
2	Williams		
3	Williams	Kyle	4/21/66
4	Jones	Bob	5/6/65
5	Johnson		
6	Johnson	Steve	7/11/66
1	Smith	John	12/1/63
2	Williams		
3	Williams	Kyle	4/21/66 

It looks like it is combining the records without a first name with the next
field that is missing a last name (which is what I want) and creating a new
additional record but it is not removing the initial records missing the first name.

It is getting close but I also still have those duplicates in the table...

Thanks MajP

RE: Need help Combining records

So it is doing what it is supposed to do. The last step is to delete those records without a first name and had you comment it out so it would not run.
strSql = "delete * from members_tbl where First is null"
CurrentDb.Execute strSql

However you are saying that instead of deleting the ones with empty first names it is deleting all records. That should not be possible. Can you verify this again. I tested this and it works without problem. If you cannot get it to work you can simply build a delete query to delete all the records with no first name, but it should work.


Just to be clear there is no creating of a new record it is simply moving the previous last name down to the next record.

Quote:

It looks like it is combining the records without a first name with the next
field that is missing a last name (which is what I want) and creating a new
additional record

RE: Need help Combining records

(OP)
It was deleting all the records in the table until I commented
the line out as you suggested.

Now it is leaving the table like this:

Seq	Last	First	DOB
1	Smith	John	12/1/63
2	Williams		
3	Williams	Kyle	4/21/66
4	Jones	Bob	5/6/65
5	Johnson		
6	Johnson	Steve	7/11/66
1	Smith	John	12/1/63
2	Williams		
3	Williams	Kyle	4/21/66
 


It is working correctly where all records have a first and last name
except for the records that are missing the first name and it is leaving
duplicates in the table.

I can run a post process delete query on the records that are missing the first name but
how do I get rid of the duplicate records?

thanks MajP

RE: Need help Combining records

I am suggesting that you comment back in the line of code. I do not believe it is deleting all the records, I think you are mistaken. BTW it also did not create duplicates, that has to be an issue in the table imported. If you are referring to the last three records.

Recommend you do a clean import and try again, I think what you think is happening is not really happening.

RE: Need help Combining records

(OP)
I guess I was mistaken...
I commented the line back in and it is working.

Here is what the table looks like now:
Seq	Last	First	DOB
1	Smith	John	12/1/63
3	Williams	Kyle	4/21/66
4	Jones	Bob	5/6/65
6	Johnson	Steve	7/11/66
1	Smith	John	12/1/63
3	Williams	Kyle	4/21/66
 
Unfortunately, the duplicate records are a pre-existing issue with the
spreadsheet and it might be best for me to remove these first but when I tried
this with a query I ended up deleting both sets of records...

Thanks again

RE: Need help Combining records

I would also recommend bracketing First since it is a SQL reserved word:

CODE --> vba

strSql = "delete * from members_tbl where [First] is null" 

Duane
Hook'D on Access
MS Access MVP

RE: Need help Combining records

You may want to google this, there are several examples of how to identify and delete duplicates.

RE: Need help Combining records

(OP)
I have been googling how to do this but most of the examples
that I have found rely on the table having a unique key field...

The field that I want to test duplicates against would be the Seq field
but this is not unique...

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close