Access Question I am stuck on

[sai_rlaf]

I apologize if this seems like a dumb question but I am relatively new to access and not of an IT background.



The situation i have is as follows.

I have two tables, one table is a government watch list, and one table is my companies client list. I want to run a query and return any our clients that have the same last name as that on the government watch list. An analyst would then manually work the list, or we may try to narrow it down using the same process on another field.

Any thoughts on how to accomplish this?

[TheShabz]

Are the last names stored as their own field or in one field with the full name? If the last name is its own field,

SELECT * FROM tblClients
WHERE tblClients.LastName = tblWatchList.LastName

Now, this would be a pretty unwise way to do it as I'm sure there are PLENTY of "Smith"s on the list. You dont want false positives. I'd look up multiple fields instead of just last name.

[sai_rlaf]

Thank you for the reply.

This helps alot.

The last name field on our client list is by itself, however, the watch list is in the following format. Lastname, First Name.

My idea was to concatenate the client into the same format as the watch, however, is there a way to seperate out the watch list?

I hear you on the returns. I might reverse it since the watch list is significantly smaller than our client list.

[sai_rlaf]

Also, can i accomplish that in Design view with criteria or do I need to write it in SQL?

[TheShabz]

this is totally untested but give it a shot, replace the WHERE with:
WHERE tblClients.LastName = left(tblWatchList.Name, len(tblClients.LastName))

Seeing as though the last name comes first in the watch list, what the above SHOULD do is take the length of LastName from your Clients table and count off that many characters starting on the left of the Watch list name field, and if they match, it will return.

Yes, you can do it from design view but you can just copy/paste the SQL and change the table and field names to reflect the actual data.

[sai_rlaf]

Thanks for help. I will try this out later today and report back.

[sai_rlaf]

I tried the above and it said there was syntax error with missing an operator (copied below).

SELECT * FROM [Client Excluded Registrations]
WHERE [Client Excluded Registrations].LastName = left(SDN Names CSV List.2, len(Client Excluded Registrations.LastName))

Any other ideas? Kind of the wall, but, is there a way to create "at least one of the words" query.

Also taking suggestions on how to "unconcatenate" the name field in my watch list so I can run the first query you suggested.

[sai_rlaf]

ok. ignore my previous post. I just did a text to columns and got a last name field. I am going to import that into access and run your first equation.

[TheShabz]

[Client Excluded Registrations].[LastName] = left([SDN Names CSV List].[2], len([Client Excluded Registrations].[LastName]))

See changes in RED. This is why we always recommend table, query, and field names not having any spaces, numbers, or special characters. Access was looking for an object called "SDN" and never found it so it just died. To designate and object with spaces in the name you must always wrap it in brackets.

[sai_rlaf]

Okay. I separated out the last names and the first query worked, however, it is asking for a perameter (so if I type in Gonzalez it will return all of the Gonzalez's that are match). I want it to return any matches. How do i do that?

[TheShabz]

If it's asking for a parameter, chances are the SQL is entered incorrectly. I did not include a parameter prompt in it. Check to make sure the brackets [ ] are set up properly like in my previous reply. (we replied at almost the same time so you may have missed it.

[sai_rlaf]

I see. So here is the new equation. I changed it to search the SDN list for any client matches (it is significantly smaller and should be easier to weed out the matches). I also took your advice and renamed my tables and fields to jive better with the code.

SELECT * FROM SDNList
WHERE SDNList.LastName =ClientList.LastName

This is my exact query language. When I click "run", it brings up the prompt box "Enter Parameter Value" and list Clientlist.Lastname for the entry. Not sure what i did wrong but any advice is most appreciated.

Also, I noticed you are on 2003 from your banner and I'm on 2010, doesn't seem like it should affect it but thought I would mention it just in case.

[TheShabz]

enter a space between =ClientList.LastName

= ClientList.LastName

[sai_rlaf]

Added the space. New equation.

SELECT * FROM SDNList
WHERE SDNList.LastName = ClientList.LastName

Still asking for a parameter.

[TheShabz]

...I'm an idiot that's why =P

SELECT *
FROM SDNList INNER JOIN ClientList ON SDNList.LastName = ClientList.LastName;

My apologies.