Update query using Like operator -- possible?

  • Thread starter Thread starter LindaM
  • Start date Start date
L

LindaM

I have a USAGE table that indicates the number of times
each user logs into a system in a given month, with fields:

RecordID (key)
PersonID (foreignKey)
Logins (integer)
Month (text)
Year (integer)

Another table, STAFF, stores the names of all the
possible users, their jobs, and various other info:

PersonID (key)
LastName (text)
FirstName (text)
etc.

There is a one-to-many relationship between STAFF and
USAGE, joined on PersonID.

The STAFF table includes records for people who have not
logged in but may someday.

I will be getting the data about logins from a report
created by someone else. That report comes to me monthly
in an Excel spreadsheet with the following fields:

Name (text: last name and first name in one string)
Logins (number)

Some of the Names are people who are not among the STAFF
I'm concerned with. My goal is to get the data from the
spreadsheet into the USAGE table, identifying all and only
those who have a record in the STAFF table and entering
only their data into USAGE.

I'm not a VBA programmer, so I was hoping to do this by
importing the spreadsheet data into a new table (TMP) and
then using a clever update query. I thought that I could
write a couple queries, one that selects all the TMP
records from where STAFF.LastName is like TMP.Name And
STAFF.FirstName is Like TMP.Name (TMP.Name is the full
name string, first and last.) Then I would update USAGE
with the STAFF.PersonID for all those people and the
number of logins from TMP. But I haven't been able to
make it work. My "Like" statements don't select
anything. Obviously I'm a database novice and making
this up as I go along. Is this something I can do in a
query, or do I need to find a programmer?

I can't change the STAFF table to combine LastName and
FirstName into one field, because I get that data from
someone else as well.

thanks much.
 
I would try an Append query that looks something like the following.


INSERT INTO Usage (PersonID, Logins, [Month], [Year])
SELECT S.PersonID , I.Logins, "November", 2003
FROM STAFF as S INNER JOIN ImportTable As I
ON S.LastName & ", " & S.FirstName = I.[Name]


You didn't say where you are getting the month and year, but I guessed they
might be in the title of the file. Note that Month, Year, and Name are not good
field names since they are also properties or functions in Access.

I've assumed that you imported or linked to the Excel table and it is name
ImportTable (use your own name). Also, I guessed the the Import table's Name
field was in the format LastName, Comma, Space, FirstName. If not, you will
have to adjust the linking clause in the SQL statement.
 
Something like this:

SELECT
T1.*
FROM
Staff T1, TMP2 T2
WHERE
T2.Name LIKE '*' + T1.LastName + '*';
 
Back
Top