combining tables

[grades]

Hi all,
I have tblEmployees as an employee list.
tblActions stores records of employee actions (many records for each employee).
In order to link the tables i need to enter the EmployeeID from tblEmployees into tblActions based on the "name" field. The name is unreliable but it will fill in 95% of them, the others I can fix manually.

Can someone point me towards a solution that will update table2 with employeeID's?
I have limited Access knowledge, but I am trying to update it using one as a DAO.recordset, but not sure if a UNION query would be more correct?

The first table looks like this:
tblEmployees:
EmpID, AgentName
1, Bob Jones
2, Phil McRackin

The second table should look like this, with ID's in place:
tblActions
name, ID
bob jones, 1
bob jones, 1
bob jones, 1
bob jones, 1

I will also be using this to update both tables with incoming actions, and employees.
I will be linking the tables based on the name, because that may change once in a while, but in the actions table there is no employeeID until I add it in.

Thanks!

 

[G37Sam]

First, it wouldn't hurt to have a one-many relationship between tblEmployee and tblActions with EmployeeID being the connection.

Then, you can use SQL's INSERT INTO (Currentdb.execute method) everytime you want to record an action.

 

[grades]

First, it wouldn't hurt to have a one-many relationship between tblEmployee and tblActions with EmployeeID being the connection.

Then, you can use SQL's INSERT INTO (Currentdb.execute method) everytime you want to record an action.

There will be a relationship, but since actions are coming in via a seperate unrelated SQL database I need to perform this task manually. So the only reliable connection will be the name.

What I need is a way to do an initial (and subsequent periodic) update on the actions table based on the name field.

Thanks

 

[G37Sam]

Do the names coming from the SQL Server exactly match the names in your tblEmployees with zero duplicates?

 

[grades]

No, they don't match. There are no duplicates tho.
For unmatched names (such as new hires) I will create a manual process to update these.

 

[G37Sam]

One last question, is there a record timestamp on the SQL imported actions?

 

[grades]

One last question, is there a record timestamp on the SQL imported actions?

Yes, though i dont see it being relevant for my current purposes.

 

[G37Sam]

It will be, because that timestamp will tell you if that current action has been imported into your current DB or not. You will have to store the "last imported" date & time in your DB.

 

[grades]

I have a find unmatched query that imports new actions.

I think maybe we are talking about different things, I'm not asking how to add new actions onto that table. I am trying to get the tables to relate to each other. I need some kind of loop that will go through the actions table, look at the name field, and put the appropriate employeeID in.
This is what I have currently sitting on a report listing the names & ID's:

CurrentDb.Execute "UPDATE [tbl agent errors] SET [AgentID] = [an] WHERE [ApplicableAgent] = " & [AIName]

But since there is a comma in the name field it throws an error.
Changing it to this:

CurrentDb.Execute "UPDATE [tbl agent errors] SET [AgentID] = [an] WHERE [ApplicableAgent] = " & """ & [AIName] & """

Gives an error Too few parameters, Expected: 1
Though i am not sure why.

 

[Beetle]

but since actions are coming in via a seperate unrelated SQL database

Clarify what you mean here. Are you linking to an "Actions" table in an external database, or are you importing data from the external source and appending it to an "Actions" table in your Access database?

If the latter, what does the data look like when you import it?

 

[grades]

Clarify what you mean here. Are you linking to an "Actions" table in an external database, or are you importing data from the external source and appending it to an "Actions" table in your Access database?

If the latter, what does the data look like when you import it?

I am importing and appending locally.
The data looks like this (for example):
name; action; ID; datetime
jones, bob; late; 1;, ##
jones, bob; error; 1; ##

 

[Beetle]

I am importing and appending locally.

Then you shouldn't want this;

The second table should look like this, with ID's in place:
tblActions
name, ID
bob jones, 1
bob jones, 1
bob jones, 1
bob jones, 1

where you are writing the ID and the name to the Actions table. You should only be writing the ID value to the Actions table.

Getting back to your imported data, I have two questions;

name; action; ID; datetime
jones, bob; late; 1;, ##
jones, bob; error; 1; ##

1) Is the name in the imported data always in that format (LastName, FirstName - and only two names - no middle name, Sr., Jr. etc.)?

2) What is the ID value in the imported data? is it the employees PK value, or is it something else? If the former, does it match the PK values in the Emp table in your Access database?

 

[grades]

Then you shouldn't want this;



where you are writing the ID and the name to the Actions table. You should only be writing the ID value to the Actions table.

Getting back to your imported data, I have two questions;



1) Is the name in the imported data always in that format (LastName, FirstName - and only two names - no middle name, Sr., Jr. etc.)?

2) What is the ID value in the imported data? is it the employees PK value, or is it something else? If the former, does it match the PK values in the Emp table in your Access database?

Let me explain, sorry I wasnt really thinking typing that.
The "Actions" table is imported from a seperate db and does not include the "ID" field. The "Name" field is not a name as such, but their username on our POS system which i likely going to always be in that format of last, first. I have set up a report that takes the names in MY employees table, creates a "last, first" field and trys to match them up which works for about 75%.
The problem I am having is how to take that list of 75% of employees whose names match up, along with their EmployeeIDs (currently in a report ive created) and adding the employeeID into the Actions table for each of their records. I have a form that does this manually for whoever needs it, but for this initial changeover (and possibly future use) I would like it to be automatic.

So to answer your querstions,
1. yes, always in that format, tho sometimes names change so a particular employee will have more than one "Name" which is why I am assigning the EmployeeID to whichever is the currently named "action" for reporting etc as it will disregard the name and use the ID.
2. the ID of the imported data is an Action ID, there is currently NO relatable data to my own tables. This is why I am messing about with the names because that will take care of most of the records.

Thanks.

 

[Beetle]

set up a report that takes the names in MY employees table, creates a "last, first" field and trys to match them up which works for about 75%.

What you need is a query, with a calculated field that creates "Last, First" from the EmployeeName field in your local Employeetable, This query would also need to include the EmpID field. Then, you run an update query using this query as the source. SQL might look like;


UPDATE tblActions INNER JOIN YourQuery ON tblActions.EmpName = YourQuery.LastFirst SET tblActions.EmpID = YourQuery.ID;

 

[grades]

What you need is a query, with a calculated field that creates "Last, First" from the EmployeeName field in your local Employeetable, This query would also need to include the EmpID field. Then, you run an update query using this query as the source. SQL might look like;


UPDATE tblActions INNER JOIN YourQuery ON tblActions.EmpName = YourQuery.LastFirst SET tblActions.EmpID = YourQuery.ID;

Thank you!
I am not all that good at queries etc. This worked great..

 

[G37Sam]

Sounds like a messy situation.

If you're getting the info from a linked table to an external SQL DB, then you can't really touch it. What you can do is use that table as a temporary import buffer sort of thing. Create a new table that you can control with the right fields and that's where you do the proper linking.

That is why I said you'd need a timestamp, to check when you last updated your local table with respect to the buffer linked table, and only import "new" records. Am I making sense?