Reading in table

[DevState]

Hello,
I'm trying to find a solution to be able to have Access read every row in a column within a table, and evaluate another - seperate - table to see if there is a match.

The purpose of this is to import a list of names, and search the table of names for a match and delete the name. I currently have the Excel spreadsheet import function up and going but need help on how to iterate through the names of the newly imported table against another table?

[ranman256]

you do this with queries, you dont iterate.

an inner join will find matches.
a delete query will use the match query and delete the ones you dont want.

[orange]

DevState,

I agree with ranman. Here is a link to w3schools --JOINs info.

[DevState]

DevState,

I agree with ranman. Here is a link to w3schools --JOINs info.

So if I use the inner join, can I have it go through the primary table and change the value of another record?

So If the list imported had the name 'Smith', and it found 5 smiths in the primary table, could it change a value under the "Employed" column?

[pbaldy]

Post 4 was moderated, I'm posting to trigger email notifications.

[orange]

DevState,

Can you tell us what these tables represent and why there may be duplicates and why some should/may be deleted?

Smith or any Last Name is not very unique generally. When you want to compare records, you need some field(s) value(s) to ensure uniqueness.

[DevState]

The spreadsheet being imported as a table is of employee names. I want to check that against a database of employee names that are tied to certain events. Each employee listed in my database can have several different events they're running and if their name is on the spreadsheet that was imported, it means they no longer are working for me. Thus, I would want to trove through my employee table and make sure to change their status to "No longer employeed".

Hope that helps?

[orange]

Existing table with Employee --Inner join on Employee ---- incoming spreadsheet

[DevState]

I would still need to figure a way out for each employee, to switch another field by their name to "No longer employed". Also, I can't seem to get a split function to break a name up that comes in like "Smith,John, H".

[DevState]

I'm not sure an Inner join is what I am looking for?

I simply need Access to look at the new "Terminations" table and run each name against the "Individual" table. If it finds a match, it makes sure the "Status" column is set to "Terminated" and moves to the next record. Can I do this with an Inner Join?

[orange]

Show readers some data.
Show us your Employee table design and some sample records.
Also include some data from your spreadsheet.

[DevState]

Individual Table (This is already in the DB as 'Individual')


This is the table that was imported from the spreadsheet (Named 'Terminations'):


The terminations Db only has a few same first and last name since that is the only criteria I will be needing to search records in the Individual table with. I need to be able to have the system, on click, automatically evaluate all names in the terminations table against the Individuals table and label those that it found as "Terminated" under the Status column.


EDIT: I may have figured it out simply using a For loop to iterate through Terminations and each one searched against the Individual table with action.

[orange]

What is the difference between Individual_ID and Individual_Employee_ID?

Since you have this data in a database, can you post a copy of the database (with only a few records) in zip format.

First and Last are poor choices of field names.

[ArviLaanemets]

Probably this will do for task you asked for.

DELETE FROM Individuals
WHERE Individual_Employee_id IN (SELECT EmplID FROM Terminations)

Another thing is, that you may consider having a field e.g. TerminationDate in table Individuals, and you use an update query instead, which fills TerminationDate with value from field [Effective Date] in table Terminations. So you can have info about individuals from past whenever you need it.

[DevState]

I can't post this kind of information. I seem to have figured out how I want to approach it but onto the next issue... the following is throwing the infamous error " syntax error (missing operator) in query expression 'Last Name = 'Hansen"

I've tried many different things, any ideas?

If DCount("Case_Number", "Individual", "Last Name =" & "'" & var & "'") > Null Then

EDIT: Yes, the naming conventions are off because I'm simply testing these features and will rewrite proper ones when I complete it. Thank you!