Under the tools menu is the Relationships command, which shows the relationships of the tables. See what fields are linked between those two tables. This is not hard to do, and I know you can do it. So far the only two fields I know about are serial number fields. There could be others. Like for example, what is the Primary key to the table? There may be a relationship set up between the primary keys of the two tables that is shorting out your query.

One thing you could do, is make a new database, import those two tables to it, and then try running your query on that. It won't copy the relationships.

Something else I thought of is that the database might be damaged in some way, if this is a brand new database it is unlikely, but if it has been around for a long time you may want to repair and compact the database, then see if the problem goes away. But before you do that, be sure to back it up first. Sometimes the repair and compact might remove data that is damaged, and last thing you need is losing data.