Search results

Hey all, This query fails to filter out states that aren't either FL or NY, even though I specify to do exactly that in the query: SELECT contacts.id, contacts.names_1, contacts.names_2, contacts.addresses INTO PrepareForDuplicateCheck FROM contacts, possibles WHERE (INSTR(CONTACTS.NAMES_1...

Thanks for suggestion. That reminded me of INNER JOIN.

Hey all, I'm trying to pull records that don't match. I have two tables: TestForgotten fullName Atkins,Ernest Bacchus,Marsula KeepThese f_fullname Atkins,Ernest Notice table 1 and tabel 2 both have Ernest, so I don't want to pull that record. But notice table 1 has a field name that's not...

Thanks for all your replies. I ultimately got it working with this: SELECT * FROM final_output AS f LEFT JOIN ( SELECT t.fullname FROM final_output AS t GROUP BY t.fullname HAVING Count(t.fullname)>=6) AS e ON f.fullname=e.fullname WHERE e.fullname Is Null

Thanks for reply. Unfortunately, when I try to run the query, specifically the second one, because for the first one, the one you posted earlier worked. But when I try to run the second one to pull all records that have less than 6 different addresses in a new table, I get the following error...

Yeah, the reason why is because Harry and Donald have 5 or less different addresses, whereas jen has 6 or more different addresses. Because of this, Harry and Donald go in one table and Jen goes in another, along with anyone else who has 6 or more different addresses. Thanks for all your responses.

Hey all, I don't have flexibility with this. The names I am trying to match with superfluous can either be in one of four fields in contacts table. And because all names in those four fields go together - since they all have same address - they must all be part of the same record. So I am...

Also when I break the query down to this: INSERT INTO Extras2 ( id, names_1, names_2, addresses ) SELECT contacts.id, contacts.names_1, contacts.names_2, contacts.addresses FROM contacts WHERE ( InStr([contacts.names_1],",")<>"0" And InStr([contacts.names_1],"&")<>"0" ); It still gives that...

is it possible to do: FROM contacts, superfluous, temp_table, PrepareForDuplicateCheck Where Contacts.SomeField = Temp_Table.SomeOtherField or Temp_Table.SomeOtherField2 or Temp_Table.SomeOtherField3 So contacts.somefield will either be equal to temp_table.someotherfield or...

What I'm ultimately trying to do is if the names repeated is more than 6, they get put in their own column. If they are less than 6 repeated names, they get put in another column, but the names more than 6 don't get put in this table. I can visually show better than I can explain: extras...

There is no join table here. superfluous table contains characters that are somewhere in contacts' names_1 field or names_2 field. PrepareForDuplicateCheck contains the same names_1 and names_2 field as contacts. However, in my result set, I do not want the matches if they are already in another...

Hey all, I have a query that basically finds first and last names that match between contacts and superfluous table. Finds the first and last names of contacts table that has "," and "&" characters in them. So far these two criteria must be met. Then since I don't want to retrieve the same...

Thanks for response. The first query worked and put all the repeated references more than 6 into their own table. However, the second query did not work. It didn't put all the repeated name references less than 6 into their own table. I have records that look like this that are still left...

Thanks for response. Now you have "SELECT DISTINCT fullName". However, I don't necessarily want to keep distinct names if the number of different addresses is less than five. Hence, if a person has four addresses, then there will be duplicate names, but since they only have 4 different addresses...

ok, so going back to my initial question, do you think this will accomodate the patterns I described: INSERT INTO possibles ( fullname ) SELECT Trim(Trim(temp_table.last_name) & "," & IIf(Trim(middle_initial) Is Null,"* &" & Trim(temp_table.first_name),"* &" & Trim(temp_table.first_name) & " ")...

Thanks for response. In one of my posts above, I pasted a number of "INSERT INTO " statements that almost serve as a regular expression for patterns. These statements work for most of my queries. However, somehow these ones: APEL,RICHARD & RHINA APEL,RICHARD & RHINA B APEL,RICHARD B & RHINA...

For the 1st, the reason why I don't want to return "LIV" or "H/E" is because those are not middle initials or middle names. And I don't want the search thinking they are. For the 3rd, this pattern I am already accomodating for in another query I posted above: APEL,RHINA & RICHARD

I append the following to the possibles table, which basically are a bunch of pattern checks so when I compare two tables, it checks for patterns in another table and pulls those records if there is a pattern match. Now all the queries below are pretty much similar, just accounting for different...

So big john solution would accomodate this: APEL,RICHARD & RHINA APEL,RICHARD & RHINA B APEL,RICHARD B & RHINA APEL,RICHARD B & RHINA B APEL,RICHARD Bud & RHINA B APEL,RICHARD B & RHINA Ann But not something like thse: APEL,RICHARD & RHINA LIV H/E APEL,RHINA APEL,RHINA & RICHARD If it...