Strange error message when running query (1 Viewer)

[johnmerlino]

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 records from the PrepareForDuplicateCheck table, I use another INSTR and say show me only those records that aren't in the PrepareForDuplicateCheck table. And Finally I just say the state of contacts must be FL or NY. So basically I want it to return records of matching names that contain the characters I mentioned above and that aren't already in the PreparForDuplicateCheck table. This isn intended to be achieved with the below sql:

SELECT contacts.id, contacts.names_1, contacts.names_2, contacts.addresses INTO Extras2
FROM contacts, superfluous, temp_table, PrepareForDuplicateCheck
WHERE (
(INSTR(CONTACTS.NAMES_1, superfluous.fullname) > 0)
Or
(INSTR(CONTACTS.NAMES_2, superfluous.fullname) > 0) 
)
or
(
InStr([contacts.names_1],",")<>"0" And InStr([contacts.names_1],"&")<>"0"
or
InStr([contacts.names_2],",")<>"0" And InStr([contacts.names_2],"&")<>"0"
)
and
(
(INSTR(CONTACTS.NAMES_1, PrepareForDuplicateCheck.NAMES_1)= 0)
and
(INSTR(CONTACTS.NAMES_2, PrepareForDuplicateCheck.NAMES_2) = 0)
)
and
(
CONTACTS.us_states_and_canada = "FL"
or
CONTACTS.us_states_and_canada = "NY"
);

However, right now this is giving me a "Cannot open database. It may not be a database your application recognizes, or the file may be corrupt." That popup shows up after a couple of minutes running the query.

 

[WayneRyan]

John,

You have this:

FROM contacts, superfluous, temp_table, PrepareForDuplicateCheck

But, there is no Join mentioned in the Where clause

Your result set without the Joins is the number of rows in each table TIMES
the number of rows in every other table.

I think you've just built a result set that is way too big and Access has gone
"brain dead".

What is the relationship(s) between the tables?

Wayne

 

[johnmerlino]

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 table called final_output because final_output would already contain those names. Hence, that temp_table should really be final_output but even when i change the name, I still get same problem.

 

[WayneRyan]

John,

If you don't specify that, for instance:

FROM contacts, superfluous, temp_table, PrepareForDuplicateCheck
Where Contacts.SomeField = Temp_Table.SomeOtherField

Then you're gonna get every possible combination of table rows ... a
Cartesian product. If there were 1,000 rows in each table, you'd be up
in the trillions.

Wayne

 

[johnmerlino]

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 temp_table.someotherfield2 or temp_table.someotherfield3. Reason why I would need to do it this way is because the name in temp_table could be in one of three different columns of contacts, given that I will never know which column it would be in. That's just how the database is setup and there's nothing I can do to change it, because at any given time I may need to query a different name in one of those three columns and they will always have the same address.

 

[johnmerlino]

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 error.

 

[WayneRyan]

John,

You need this kind of syntax. Where the rows don't match, the values will be Null for the items
in the right-hand-side table.

FROM ((contacts Left Join superfluous On
              Contacts.SomeField = superfluous.SomeOtherField) Left Join Temp_Table On
                 Contacts.SomeField = temp_table.ThatOtherField) Left Join PrepareForDuplicateCheck On
                    Contacts.SomeField = PrepareForDuplicateCheck.AnotherField

hth,
Wayne