Need help Combining records

[irethedo]

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:

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

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:
[pre]
Last First DOB
Smith John 12/1/63
Williams Kyle 4/21/66
Jones Bob 5/6/65
Johnson Steve 7/11/66
[/pre]


Thanks!

 

[dhookom]

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

 

[irethedo]

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

 

[dhookom]

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

 

[irethedo]

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:

[pre]# 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
[/pre]
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

 

[MajP]

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

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

 

[robojeff]

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...

 

[dhookom]

Does your table name have a space in it? Can you share your actual code?

Duane
Hook'D on Access
MS Access MVP

 

[robojeff]

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

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:

[pre]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[/pre]

Thanks!

 

[dhookom]

Are you all good now or are there still outstanding issues?

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

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

 

[irethedo]

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:
[pre]
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[/pre]

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:
[pre]
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[/pre]

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

 

[MajP]

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.

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

 

[irethedo]

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:

[pre]
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
[/pre]

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

 

[MajP]

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.

 

[irethedo]

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

Here is what the table looks like now:
[pre]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
[/pre]
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

 

[dhookom]

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

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

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

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

 

[irethedo]

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...