(could be some sort of DLookup but I'm not to sure)

[lars7]

Hi There,
I have a staff table "tblstaff" where the staff have a code number "Code" and then 4 fields are updated,"JobFamily","SubJobFamily","Spine" and "Band", according to the code and the information is then sent to payroll for the staff to be regraded. But as this table is updated every week (Imported Excel Sheet) with more staff that can have the same code the user will have to go through the same process again and again. They have asked me is it possible for the information in these 4 fields to be automatically (could be some sort of DLookup but I'm not to sure) updated where the code number has already been regraded.
As always any help would be gratefully received.

 

[CautionMP]

Create a linked table to the source Excel sheet, then run an update query to change the codes in [tt]tblStaff[/tt] to match the most recent Excel sheet?

CMP

(GMT-07:00) Mountain Time (US & Canada)

 

[lars7]

Hi Caution,
As the excel sheet does not have the information yet in the "JobFamily","SubJobFamily","Spine", "Band" and I missed 2 "PostDescriptor" and "AFCCode" I think I will import the Excel sheet into table "Importedstaff" and then set up an update query and then an append query to put them in the staff table. Does this sound ok to you. But the update query, although working, is giving me 6157 records then there is only 38 records in "Importedstaff" Here is the SQL:

UPDATE Importedstaff LEFT JOIN tbltruststaff ON Importedstaff.Code=tbltruststaff.Code SET Importedstaff.JobFamily = tbltruststaff.JobFamily, Importedstaff.SubJobFamily = tbltruststaff.SubJobFamily, Importedstaff.PostDescriptor = tbltruststaff.PostDescriptor, Importedstaff.AFCCode = tbltruststaff.AFCCode, Importedstaff.Spine = tbltruststaff.Spine, Importedstaff.[Band] = tbltruststaff.[Band];

As I said it only updated the ones I wanted it to but it is duplicating the records big time. Will this cause problems.

 

[Zion7]

The join is not necessary and actually erroneous.
That's why you're dup/triplicating.

If i've understood correctly,
you don't need to update, just DROP TABLE Importedstaff,
then import. Do you have any use, of the former records?
If so, just append Importedstaff to tbltruststaff,
after importing?

 

[lars7]

Hi Zion,
I have never used a Drop Table before so don't know how that will take information from "tbltruststaff and update fields where the "code" is equal in "ImportedStaff". As for the records in "ImportedStaff", I was going to delete them but leave the table and therefore the queries intact after the append query.
Could you give me the idiots guide.

 

[Zion7]

No problem, I'm just not sure if I understand your objective.
Do you actually need to save any former records?
If ALL the fields, are coming in via the Excel sheet,
there's all the data you need.
All the updates are there?
Any info you wanted to keep from the former table,
is just being replaced again, and any updates are refreshed.

So, I was saying, before you do the Import,
Drop the existing table, "DELETE IT".
then import the new table under the same name,
so there's no "table already exists error"


Private Sub cmdTransfer_Click()

CurrentProject.Connection.Execute _
"DROP TABLE Importedstaff",,129

Docmd.TransferSpreadsheet....

End Sub

But, my logic may be wrong, if for any reason
you need to save certain data from the old table?
If that is the case then transfer first then "UPDATE".

DO NOT join the tables, they're identical!!!

Simply using an UNIQUE field, match them & update.

Private Sub cmdTransfer_Click()
Dim rec as New AdODB.Recordset

Docmd.TransferSpreadsheet....

rec.Open ImportedStaff, CurrentProje.....

Do until rec.EOF

CurrentProject.Connection.Execute _
"Update tbltruststaff SET " & _
"txtExchange =" & rec!txtExchange & "," & _
"txtCurrency =" & rec!txtCurrency & "," & _
"txtDate =#" & rec!txtDate & "#," & _
"txtName ='" & rec!txtName & "' " & _
"WHERE txtExDate =#" & rec!txtExDate & "#",,129
rec.MoveNext
Loop

rec.Close: set rec = Nothing

End Sub

 

[CautionMP]

lars7,
Take this with a grain of salt, I don't know your requirements, skills, preferences, but here is what I see:[ul]
[li]You have an Excel workbook with data in it.[/li]
[li]You import this data into a table [tt]Importedstaff[/tt].[/li]
[li]You then move the data to [tt]tbltruststaff[/tt].[/li][/ul]
So at any one time, you have the data in three different places, and two of the places are transient (temp)?

It seems to me that if the Excel workbook was linked, you could do the append and update (either with queries, or with code as suggested by [navy]Zion7[/navy]), simply by using the linked Excel table.

Can you tell that I'm not a big fan of temporary records? Since they are created and then deleted I don't see much value in using them. Let your source data be the temporary records.

Getting off my soapbox now,
CMP

(GMT-07:00) Mountain Time (US & Canada)

 

[lars7]

Hi Zion,
I need the rest of the "rec.Open ImportedStaff, CurrentProje..." code, this is my first time using "loop" so when i said "Idiots Guide" I meant it. I need the table to be intact as it has the additional fields to update the info. into, Unless I'm not understanding this at all. Here is what I have so far.

Private Sub btn4_Click()
On Error GoTo btn4_Err
Dim rec As New AdODB.Recordset

DoCmd.TransferSpreadsheet acImport, 8, "ImportedStaff", "C:\Documents and Settings\tmcmeekin1\My Documents\truststaffupdate.xls ", True, ""

rec.Open ImportedStaff, CurrentProje...


Do Until rec.EOF

CurrentProject.Connection.Execute _
"Update tbltruststaff SET " & _
"txtExchange =" & rec!txtExchange & "," & _
"txtCurrency =" & rec!txtCurrency & "," & _
"txtDate =#" & rec!txtDate & "#," & _
"txtName ='" & rec!txtName & "' " & _
"WHERE txtExDate =#" & rec!txtExDate & "#", , 129
rec.MoveNext
Loop

rec.Close: Set rec = Nothing

DoCmd.Requery "lst2"

btn4_Exit:
Exit Sub
btn4_Err:
MsgBox Error$
Resume btn4_Exit
End Sub

 

[Zion7]

CMP's suggestion was interesting/preferable.

If you insist on Updating using 2 tables, then,

rec.Open "ImportedStaff", _
CurrentProject.Connection, adOpenForwardOnly, adLockPessimistic


DO NOT USE THE FIELD NAMES, I gave you
for the update statement!
They were just examples!!!!!!!!!

 

[lars7]

Hi Zion,
I tried your code as I already had it set up and I have a syntax error message.

On Error GoTo btn4_Err
Dim strSQL As String
Dim rec As New AdODB.Recordset

DoCmd.TransferSpreadsheet acImport, 8, "ImportedStaff", "C:\Documents and Settings\Tom\My Documents\truststaffupdate.xls ", True, ""

rec.Open "ImportedStaff", _
CurrentProject.Connection, adOpenForwardOnly, adLockPessimistic


Do Until rec.EOF

CurrentProject.Connection.Execute _
"Update tbltruststaff SET " & _
"JobFamily ='" & rec!JobFamily & "' " & _
"SubJobFamily ='" & rec!SubJobFamily & "' " & _
"PostDescriptor ='" & rec!PostDescriptor & "' " & _
"spine ='" & rec!spine & "' " & _
"Band ='" & rec!band & "' " & _
"AFCCode ='" & rec!AFCCode & "' " & _
"WHERE code ='" & rec!code & "' ", , 129
rec.MoveNext
Loop

rec.Close: Set rec = Nothing

All the fields are text.

Caution,

I think I have finally understood what you meant. I linked the excel sheet and created a query but I still had to make the join with the trust table (didn't I ?), but I grouped the data and the count has come away down to just 4 more records than the "ImportStaff" table has. At the end of the old code I would delete the excel file so that the user could not import the same information again would you advise me still to do that.

INSERT INTO tbltruststaff ( Trust, PayNumber, EmployeeName, JobTitle, Code, Grade, DateProcessed, JobFamily, SubJobFamily, PostDescriptor, AFCCode, Spine, [Band] )
SELECT Importedstaff.Trust, Importedstaff.PayNumber, Importedstaff.EmployeeName, Importedstaff.JobTitle, Importedstaff.Code, Importedstaff.Grade, Importedstaff.DateProcessed, tbltruststaff.JobFamily, tbltruststaff.SubJobFamily, tbltruststaff.PostDescriptor, tbltruststaff.AFCCode, tbltruststaff.Spine, tbltruststaff.[Band]
FROM Importedstaff LEFT JOIN tbltruststaff ON Importedstaff.Code = tbltruststaff.Code
GROUP BY Importedstaff.Trust, Importedstaff.PayNumber, Importedstaff.EmployeeName, Importedstaff.JobTitle, Importedstaff.Code, Importedstaff.Grade, Importedstaff.DateProcessed, tbltruststaff.JobFamily, tbltruststaff.SubJobFamily, tbltruststaff.PostDescriptor, tbltruststaff.AFCCode, tbltruststaff.Spine, tbltruststaff.[Band];


Please let me know if this is not what you intended me to do.

 

[CautionMP]

lars7,
For the Append query, no, no joins (this query will only return the number of records in the Excel workbook).

For the Update query, yes, so only the records in [tt]tbltruststaff[/tt] that match the records in [tt]Imprtedstaff[/tt] get updated.

I need to back up for a minute. In [tt]tbltruststaff[/tt] do you have a Primary key?

CMP



(GMT-07:00) Mountain Time (US & Canada)

 

[lars7]

Hi Caution,
The append query I posted has put all the information I require in the table without an update query.

"(this query will only return the number of records in the Excel workbook)" this is what I wanted. With the left join the first 7 fields are from the excel sheet and the next 6 from truststaff. The query is filling in the last fields according to what the code is equal to in the "Importedstaff" Table and the whole record is getting appended into the "truststaff" table.

I tested it and it looks ok.%-)

 

[lars7]

Hi again Caution,
I can't get rid of the extra records the join is creating, 79 "Importedstaff", 84 "qryAppendtruststaff", therefore I can't append them straight into the table but I am not sure how I can update "Importedstaff" when the 6 fields that I want to update are not there. Could you talk me through this slowly.

 

[Zion7]

JUST FOR THE RECORD,
Lars, you need commas, between each field...

CurrentProject.Connection.Execute _
"Update tbltruststaff SET " & _
"JobFamily ='" & rec!JobFamily & "', " & _
"SubJobFamily ='" & rec!SubJobFamily & "', " & _
"PostDescriptor ='" & rec!PostDescriptor & "', " & _
"spine ='" & rec!spine & "', " & _
"Band ='" & rec!band & "', " & _
"AFCCode ='" & rec!AFCCode & "',, " & _
"WHERE code ='" & rec!code & "'", , 129
rec.MoveNext
Loop

 

[lars7]

Hi Zion,
I got a "syntax error in update query" with your code, with or without the 2 commas in the AFCCode bit.


Private Sub btn4_Click()
On Error GoTo btn4_Err
Dim strSQL As String
Dim rec As New AdODB.Recordset

DoCmd.TransferSpreadsheet acImport, 8, "ImportedStaff", "C:\Documents and Settings\tmcmeekin1\My Documents\truststaffupdate.xls ", True, ""

rec.Open "ImportedStaff", _
CurrentProject.Connection, adOpenForwardOnly, adLockPessimistic


Do Until rec.EOF

CurrentProject.Connection.Execute _
"Update tbltruststaff SET " & _
"JobFamily ='" & rec!Jobfamily & "', " & _
"SubJobFamily ='" & rec!Subjobfamily & "', " & _
"PostDescriptor ='" & rec!PostDescriptor & "', " & _
"spine ='" & rec!spine & "', " & _
"Band ='" & rec!band & "', " & _
"AFCCode ='" & rec!AFCCode & "',, " & _
"WHERE code ='" & rec!code & "'", , 129
rec.MoveNext
Loop

rec.Close: Set rec = Nothing

 

[CautionMP]

lars7,
I have been a little SQL challenged for the last coupe of days so I can't seem to write the SQL string you need, but I can detail out what needs to be done.

It seems that the piece that's missing is a way to return the last records from [tt]tbltruststaff[/tt] that you can use to join [tt]tbltruststaff[/tt] and [tt]Importedstaff[/tt]. Create a new query using the code below and save it as [tt]LastTrustStaff[/tt]:

SELECT Code AS LastCode, MAX(DateProcessed) AS LastDateProcessed FROM tblTrustStaff GROUP BY Code;

Now create another new query (this will be the append query) and add:[ol]
[li][tt]Importedstaff[/tt][/li][li][tt]LastTrustStaff[/tt][/li][li][tt]tbltruststaff[/tt][/li][/ol]
Then create the following joins:[ul]
[li][tt]Importedstaff.Code[/tt] INNER JOIN [tt]LastTrustStaff.LastCode[/tt][/li]
[li][tt]LastTrustStaff.LastCode[/tt] INNER JOIN [tt]tbltruststaff.Code[/tt][/li]
[li][tt]LastTrustStaff.LastProcessedDate[/tt] INNER JOIN [tt]tbltruststaff.ProcessedDate[/tt][/li][/ul]
Then you can define the fields that you need to Append into [tt]tbltruststaff[/tt].

I hope this makes sense,
CMP

(GMT-07:00) Mountain Time (US & Canada)

 

[lars7]

Hi Caution,
Did you mean for me to group the append query, without it I'm getting multiple duplications again but with it the correct number. This is looking good but I will do a few tests and get back to you. Thanks for the work you've put into this so far it's appreciated. Here is what I have now.

INSERT INTO tbltruststaff ( Trust, PayNumber, EmployeeName, JobTitle, Code, Grade, DateProcessed, JobFamily, SubJobFamily, PostDescriptor, AFCCode, Spine, [Band] )
SELECT Importedstaff.Trust, Importedstaff.PayNumber, Importedstaff.EmployeeName, Importedstaff.JobTitle, Importedstaff.Code, Importedstaff.Grade, Importedstaff.DateProcessed, tbltruststaff.JobFamily, tbltruststaff.SubJobFamily, tbltruststaff.PostDescriptor, tbltruststaff.AFCCode, tbltruststaff.Spine, tbltruststaff.[Band]
FROM (LastTrustStaff INNER JOIN Importedstaff ON LastTrustStaff.LastCode = Importedstaff.Code) INNER JOIN tbltruststaff ON (LastTrustStaff.LastDateProcessed = tbltruststaff.DateProcessed) AND (LastTrustStaff.LastCode = tbltruststaff.Code)
GROUP BY Importedstaff.Trust, Importedstaff.PayNumber, Importedstaff.EmployeeName, Importedstaff.JobTitle, Importedstaff.Code, Importedstaff.Grade, Importedstaff.DateProcessed, tbltruststaff.JobFamily, tbltruststaff.SubJobFamily, tbltruststaff.PostDescriptor, tbltruststaff.AFCCode, tbltruststaff.Spine, tbltruststaff.[Band];

I hope this is what you intended.

 

[lars7]

Hi Again Caution,
I Cut and 67 staff from "tbltruststaff and pasted them into the "ImportedStaff" but the append query would only show me 19 when it was grouped and 80 without the grouping. The Dateprocessed column does not have all the dates filled in as these records where created before I gave them a database but I could put a single date in there to fill in the blanks if it is necessary.

 

[Zion7]

2 commas, was a typo, should be one.
Make sure all your fields from the recordset, are being populated.
If not, maybe,

...
"spine ='" & Nz(rec!spine,"NA") & "', " & _
"Band ='" & Nz(rec!band,"NA") & "', " & _
....

 

[lars7]

Hi Zion,
I am getting a syntax error with this but when debugging and holding the curser over the (rec!spine, "NA") it was saying "is null" and over the (rec!code & "'",) I was saying "971" the first code.


Private Sub btn4_Click()
On Error GoTo btn4_Err
Dim strSQL As String
Dim rec As New AdODB.Recordset

DoCmd.TransferSpreadsheet acImport, 8, "ImportedStaff", "C:\Documents and Settings\tmcmeekin1\My Documents\truststaffupdate.xls ", True, ""

rec.Open "ImportedStaff", _
CurrentProject.Connection, adOpenForwardOnly, adLockPessimistic


Do Until rec.EOF

CurrentProject.Connection.Execute _
"Update tbltruststaff SET " & _
"JobFamily ='" & Nz(rec!Jobfamily, "NA") & "', " & _
"SubJobFamily='" & Nz(rec!Subjobfamily, "NA") & "', " & _
"PostDescriptor ='" & Nz(rec!PostDescriptor, "NA") & "', " & _
"spine ='" & Nz(rec!spine, "NA") & "', " & _
"Band ='" & Nz(rec!band, "NA") & "', " & _
"AFCCode ='" & rec!AFCCode & "',, " & _
"WHERE code ='" & rec!code & "'", , 129
rec.MoveNext
Loop

rec.Close: Set rec = Nothing