Search results

Problem is detecting for this very exact match: APEL,RICHARD & RHINA That RICHARD is a variable. It can be any name. I just need to make sure that the query I posted in the initial question accomodates ONLY this specific pattern and not any other pattern. Hence, the pattern is...

Hey all, I have a record that looks like this: APEL,RICHARD & RHINA Notice the ampersand before the first name and notice there's another name after the last name, which is Apel. So I would like to account for this pattern. I come up with this: INSERT INTO possibles ( fullname ) SELECT...

No, I think I didn't explain well. Here's the issue. If a person let's say John Smith has more than 5 different addresses, then that's too many addresses to keep him in my original list. So I want to put him in a separate table called extras. For example: John Smith First Street John Smith...

Perhaps I should clear up the issue. I have this: 333 Marge, John State Road 333 Marge, John State Road 444 Marge, John Cap Road 444 Marge, John Cap Road 515 Marge, John 3rd Street I would want to keep only the following: 333 Marge, John State Road 444 Marge, John Cap Road 515 Marge...

By the way, I appreciate all your responses. It has been invaluable as I leap into the world that is Access.

I try this: SELECT KeepThese.* INTO ready_for_print FROM KeepThese WHERE ID <> (SELECT Min(ID) AS MinOfID FROM KeepThese AS Dupe WHERE (Dupe.id = KeepThese.id));And it appends nothing in new table. And it appends nothing in new table.

The table did start with unique ids. However, I had to create a new table to bring a column from one table with a column from another table. This query caused duplicate ids: SELECT possibles.fullname AS fullname, PrepareForDuplicateCheck.addresses, PrepareForDuplicateCheck.id INTO final_output...

Thanks for response. That worked but there's a problem that I have duplicate ids. Is there a simple query to remove duplicate ids - where you just check if the id has dupluicates and nothing else? I tried this: SELECT KeepThese.* INTO ready_for_print FROM KeepThese WHERE id IN (SELECT id...

Thanks for response. Any idea why it says "The specified field names_1 could refer to more than one table listed in the FROM clause of your SQL statement" when I added "names_1" and "addresses" to the line directly below UNION ALL. While I understand what UNION ALL is supposed to do, this part...

Thanks for response. I noticed it only outputs the id into the new table but not the corresponding name and address. Is there a way to output the corresponding name and address with the id into the new table? I tried changing it myself but I get error like "The specified field names_1 could...

Hey all, I would like to update one column with the value of another column from another table: table_1 names_1 addresses BLAIR,SHERON S 6110 Browen Road BROWN,BRENON I H/E BROWN,WANDA R 1100 Lake Road RODRIGUEZ,LILLIAN M 3332 Brick Road...

Thanks for reply. This didn't seem to insert anything in the KeepThese field: INSERT INTO KeepThese ( ID ) SELECT ID FROM (SELECT Min(P.ID) AS ID FROM [The Final Query] as P GROUP BY names_1, addresses HAVING COUNT(*) >=5 UNION ALL SELECT ID FROM [The Final Query] as P INNER JOIN (SELECT...

Hey all, Any idea how to use a function to do a RegExp Replace and then calling it from the query in Microsoft Access? The idea is to match the three fields in the temp_table query: last_name || first_name || middle_initial Blair || Sheron || S Brown || Wanda || R Rodriguez...

Thanks for response but I'm still having problems with this. 1) I create a table called KeepThese and give it a primary key id. 2) I create a query called delete_1 and copy this into it: INSERT INTO KeepThese SELECT ID FROM ( SELECT Min(ID) AS ID FROM Print_Ready GROUP BY names_1, addresses...

Hey all, I had my query getting closer to being accurate until I had to make some additional changes. One change was if in our temp_query, the middle_name field had a value in it and therefore was not null, then we use the sql like to find like characters in the print_ready query that match...

I do want to keep duplicates. I want to delete records where the duplicates exceed 4. So if there are five names and addresses that are the same, then I want to delete 4 of the 5. However, if there's 4, then I want to keep all 4.

That gives me error "could not delete from specified table"

This gives syntax error message: CREATE TABLE isolate_duplicates AS SELECT DISTINCT names_1, addresses, COUNT(*) FROM print_ready GROUP BY names_1, addresses HAVING COUNT(*) > 4; DELETE print_ready FROM print_ready INNER JOIN isolate_duplicates ON print_ready.names_1 =...

Hey all, I am trying to delete 4 out of 5 records from table where name field repeats same value more than 5 times and the address field repeats more than five times for a table. So if there are 5 records with a name field and address field that are the same for all 5, then I would like to...

Hey all, There's an initial table called contacts with 700,000 records There's a field called temp_query, which contains 80 records. Now print_ready matches the contacts and temp_query to check for similarities in first and last name and returns around 200 records because although there was...