Append Query ??? 1

[tstowe]

I have two separate dbases that encompass all of my personnel. All of the data that I want to track is coming from this source, but I don't need the amount of information that the original is tracking. So, I created a dbase with like field names and done a simple Append query to pull in the personnel and the fields that I personally need to track.

My question: Personnel come and go on a regular basis and I am wanting access to import the only the new personnel. How do I create an Append Query to ONLY bring in the new personnel into my dbase??

Thanks ahead of time.

Tony

 

[zishan619]

Hi:
Well if the new list only provides new people just do append query to the existing table from that list. If not does the List have anything on it to distinguish between new and old person?
Cool
Z

 

[hwkranger]

You'll need to have either 1: a unique key that you use to pull new people based on maybe Julian date? or you'll need a field that shows when the person joined your team.

if you have either you can do it simply. Perhaps if you're employee numbers are incremented you could also do it.

Otherwise, if you have a database with OLD people, Current people, and NEW people and your database your working on only wants NEW people it'll be impossible without distinguishing when they joined.

Randall Vollen
National City Bank Corp.

 

[tstowe]

No distinguishing fields to indicate that an individual was since <date>. That would be nice though, but someone far above is dictating the original dbase. The download that I get lists all personnel - with any new personnel added. I tried several things and ended up with my table having the same individual listed six times or so. haha I had to erase all of it and Append the two originals again.

 

[hwkranger]

Download the info (download table), check for duplicate between your down load table and your current table (Personel table). Then do a <NOT> those people in your query.

2nd query will be append, append from first query, all those that are NOT duplicates are appended to your Personal Database

Randall Vollen
National City Bank Corp.

 

[tstowe]

Although what you are saying makes sense, I don't know HOW to make the query check for <NOT> those people. Can you elaborate for me. thanks

 

[hwkranger]

Maybe I wasn't clear:

Have 2 tables. Download and Employee tables

1)Intersection of Download and Employee = Duplicate Query. (Select Query)
2)Then change your Duplicate query to saying NOT those people.(Same as saying Not Employee Table)
3)Append results to your Employee table

In the End Employee Table = Employee Table + (Download Table - Employee Table)

Randall Vollen
National City Bank Corp.

 

[tstowe]

I have also noticed that the data coming in has the persons full name in one field. I would prefer to have the name separate such as :
LNAME
FNAME
MI


Is it also possible to have the original field [sidstrName_Ind] to split into [Lname] [Fname] [MI]. At the moment I am looking at retyping some 200 names. :*(

 

[hwkranger]

Sure is..

use VBA. I would also suggest having an EMPLOYEE NUMBER if you don't already since You can have two Bob Smiths.

For VBA you can use Instr(), Left(), and Right() to break your name into First, Last, Middle Name.

For this you would need to add NEW Fields to your OLD table. Run a VBA function breaking down the name and putting it into the corresponding fields..

eg.

'FirstName = Field name
'LastName = Field Name
'Middle = Field Name

dim mynames as string
mynames = &quot;Randall L Vollen&quot;

dim first as string
dim middle as string
dim last as string
dim spot as string

dim db as database
dim myrecord as recordset

set db = currentdb()
set myrecord = db.openrecordset(&quot;Your Table Name&quot

spot = instr(Mynames,&quot; &quot
First = left(Mynames, spot)
mynames = right(mynames, len(mynames-spot)

With myrecords
If .RecordCount Then
.MoveFirst
Do Until myrecordset.EOF

spot = instr(mynames, &quot; &quot
.edit
if spot > 0 then
middle = left(mynames, spot)
!Mi = middle
last = right(myname, len(myname) - spot)
!LastName = Last
else
Last = mynames
!lastName = last
end if

!FirstName = First
.update


end with
end if

Randall Vollen
National City Bank Corp.

 

[hwkranger]

Correction to that Eg.
'FirstName = Field name
'LastName = Field Name
'Middle = Field Name

dim mynames as string

dim first as string
dim middle as string
dim last as string
dim spot as string

dim db as database
dim myrecord as recordset

set db = currentdb()
set myrecord = db.openrecordset(&quot;Your Table Name&quot

spot = instr(Mynames,&quot; &quot
First = left(Mynames, spot)
mynames = right(mynames, len(mynames-spot)

With myrecords
If .RecordCount Then
.MoveFirst
Do Until myrecordset.EOF
mynames = !sidstrName_Ind
spot = instr(mynames, &quot; &quot
.edit
if spot > 0 then
middle = left(mynames, spot)
!Mi = middle
last = right(myname, len(myname) - spot)
!LName = Last
else
Last = mynames
!LName = last
end if

!FName = First
.update


end with
end if

Randall Vollen
National City Bank Corp.

 

[hwkranger]

LoL one more correction, I'm an idiot.

move:
spot = instr(Mynames,&quot; &quot
First = left(Mynames, spot)
mynames = right(mynames, len(mynames-spot)

to below the following line:

mynames = !sidstrName_Ind


Randall Vollen
National City Bank Corp.

 

[tstowe]

I have only been a member of this forum for a short period of time and you guys never cease to amaze me. Thanks.

Tony

 

[tstowe]

That is awesome. One last question, Where do I enter this code? I ask because I have yet to input this type of coding. Thanks

 

[hwkranger]

module or sub.

you can make a form, make a button, on the ONCLICK event put the code.

Make sure you CHANGE to your table names.
set myrecord = db.openrecordset(&quot;Your Table Name&quot <--





Randall Vollen
National City Bank Corp.

 

[tstowe]

Ok, thanks. I copy/pasted your code into a Module and found that the line &quot;mynames = right(mynames, len(mynames-spot)&quot; came up in red.

Also, will this code work with Access97?

 

[tstowe]

HWKRANGER,

I attempted to use your code to split the incoming data and I continue to have errors. I simply do not understand coding enough to file in the correct data. Can I ask that you idiot proof this for me a little more to streamline the work? This is a good learning curve for me but my continued failures is taking the fire out of me.

Thanks

Tony

 

[hwkranger]

Private Sub Command0_Click()
On Error GoTo Err_Command0_Click
'FirstName = Field name
'LastName = Field Name
'Middle = Field Name

Dim mynames As String

Dim First As String
Dim middle As String
Dim Last As String
Dim spot As String

Dim db As Database
Dim myrecord As Recordset

Set db = CurrentDb()
Set myrecord = db.OpenRecordset(&quot;Znames&quot


With myrecord
If .RecordCount Then
.MoveFirst
Do Until myrecord.EOF
mynames = !sidstrName_Ind

spot = InStr(mynames, &quot; &quot
First = Left(mynames, spot)
mynames = Right(mynames, Len(mynames) - spot)

spot = InStr(mynames, &quot; &quot
.Edit
If spot > 0 Then
middle = Left(mynames, spot)
!Mi = middle
Last = Right(mynames, Len(mynames) - spot)
!LName = Last
Else
Last = mynames
!LName = Last
End If

!FName = First
.Update
.MoveNext
Loop


End If
End With
Screen.PreviousControl.SetFocus
DoCmd.FindNext

Exit_Command0_Click:
Exit Sub

Err_Command0_Click:
MsgBox Err.DESCRIPTION
Resume Exit_Command0_Click

End Sub

Randall Vollen
National City Bank Corp.

 

[hwkranger]

I ran this code - it works. I had forgotten a couple things when I did it on the fly. SOrry about that. Would have gotten bakc to you sooner but I've been VERY VERY Sick

Randall Vollen
National City Bank Corp.

 

[hwkranger]

btw you still have ot replace the word Znames with your table name.

Randall Vollen
National City Bank Corp.

 

[tstowe]

I certainly appreciate your time and I hope that you are feeling much better today. Thanks.

I will try to make this work this evening and I will let you know tomorrow. Again, Thanks.

Tony.