Query multiple tables with multiple criteria

[BadDog]

I am trying to query multiple tables which each share a common "registration_number" (RSN) field. I set up a relationship between each tables RSN field. One table is ENTITY (which has several fields I need including entity name, which I want to filter by), another one is CONTACT (which has the Contact person's name and address - And "contact type" which I want to filter by), and another is the COUNTY table (which I also want to filter by - so I only get certain counties). I am having troubles with this query. I can do the individual queries on each table, but I can't seem to set up the query to work on multiple tables. Say for instance I want all ENTITY info for a given contact type (CONTACT table) for a specific COUNTY with the word "Association" in the entity name (ENTITY). Any help would be greatly appreciated. Thanks.

 

[tallbarb]

Not knowing much about your data, I would venture to guess the problem lies in your join types. For instance, if you want to see ALL the records from your ENTITY table even if there's no matching records in the CONTACT table (assuming the ENTITY table is a "one" table and the CONTACT table is the "many" table), the join type should be set to 2. I'd look there first to make sure your tables are joined correctly so you get the records you want from the tables involved.

 

[BadDog]

What if I only want to see records from the entity table when certain criteria is met for a field in that table, PLUS fields from the contact table for those records from the filter of the entity table but only if the criteria (filter) of the contact table is also satisfied (and to make matters worse a filter on another table too)?

 

[TomIII]

BadDog...

I've found that if you are looking at multiple filters in multiple related tables, it sometimes is easier and more organized to write a couple of queries and then link the queries for your final results...as opposed to trying to bundle all the tables and filters up into one query. That way, you can analyze the results of the more manageable queries, perfect each and then link and run.

Good luck...

 

[BadDog]

I did a query on each table individually and they each worked. But, when I linked them together I would get weird results like only one record (when I know there are thousands that match the criteria). The common field between each of the tables is the RSN (registration number). So I have all joins set as #1. Does it sound like I have the join types messed up?

 

[TomIII]

Sorry, I missed in your first posting that you had set up individual queries already.

You can always experiment with the left and right joins (2, 3) to see the difference, but it sounds like they should be fine.

You might want to try to make sure that there are no hidden spaces in the data for the RSN columns. Instead of just RSN in the query, make it an expression...Expr1: Trim([RSN])...to truncate out extraneous spacing.

Also, are the properties (i.e. datatypes, etc.) for the RSNs identical?

What version of Access? '97, '2000, XP (god forbid <g>)?

 

[BadDog]

Do I need to worry about the joins in the &quot;relationships&quot; window, or just in the relationships window of each of the individual queries, or both? On that note, does the relationship created in the main &quot;relationships&quot; window override the relationship(s) created in the queries, or vice versa, or ???