Generating a Query from Multiple Linked Tables

[jonhimes]

I have a master database that holds a great deal of administrative data and have four additional databases that are identical and they have clinical information in them. Each Case Manager has a Database that holds their clients clinical information in. They have a field in their main table which I need to generate a query with 1 field in my Master database. All tables have common data of Social Security Number, First and Last name. I have linked tables into the master DB to then create a query from them. Once I create a query with one alone it works. Once I get 2 or more tables for the query it shows the columns headings but no data. That is in design view. If I go to wizard it says, "You have chosen fields from record sources which the wizard can't connect. You may have chosen fields from a table and from a query based on that table. If so, try choosing fields from only the table or only the query." I have had the case managers completely close out of their databases and unplugged my network connection. The databases are on the same drive as mine. What are the other ways I create a query to get the info from the fields in their tables or fix the queries that I’m trying to do? Can a report pull the data because that wouldn’t work for me either? Do I have to pull the tables in the database instead of just linking them? I know it is a lot of rambling but I am at a stop for the time being. Thank you for your time.

Jon Himes

 

[JonFer]

Please post the structure of the tables and what fields you link on.

Do you want to treat the tables in the 4 clinical databases as if it were one big table and then join to your master database? If so, look up UNION queries.

 

[jonhimes]

Main data table is called:
Case Management

Social Security #
First Name
Last Name

4 Additional Linked Tables:
A-G
H-L
M-R
S-Z

Social Security #
First Name
Last Name
Spears Data

For one query as
Social Security # First Name Last Name Spears Data


I have a Current Status field which gives me a pull down. the data in that field to be used would be:

Medical Hold - Nondeployable


Thank you,
Jon Himes