create simple "Search" form to look up matching records from 5 tables - please help

[MaggieN]

I have five tables. Each table has a unique record identifier (primary key, e.g. CX1234). A primary key from Table 1 may appear as primary key in Table 2, 3, and 4 (e.g. some records, such as the record CX1234, but not all of them, from Table 1 appear in the other tables - with more detail information - and vice versa. HOWEVER, not all records from Table 1 appear in the other tables, and not all the information from other tables appear in Table 1.



I need to create a basic search form where
1. I type a primary key (e.g. CX1234)
2. the form searches ALL FIVE tables where this record appears and returns records from those tables where this record appears)
3. The results appear on the actual form so it all looks kind of like this:
Top of Form: An empty text box for searching with a "Search" button
Results below:
Table 1: result (all fields)
Table 2: results (all fields)
Table 3: results (all fields)
Table 4: results (all fields)
Table 5: results (all fields for that record).

How do I do that? I tried to link the 5 tables together first, but am having trouble creating the relationship because not all individual unique records from one table appear in the other tables, and vice versa..

Is it correct that I need to create a Query that would pull the fields from all five tables and then design a form with a "Search" button that will run that query each time?

Please help. I am very new to Access so this is pretty challenging.
Thank you!

[June7]

Tables 2,3,4,5 have 1-to-1 relationship with Table 1?

Build query that links the 4 tables to Table 1, don't use INNER JOIN, instead "Include all records from Table 1 and only those records ..."

Build form with that query as RecordSource.

If the 4 dependent tables have many related records, the all-in-one query won't work nice.

[MaggieN]

Thank you for your reply.
I guess it must be one-to-one since the primary keys are always the same for all tables (i.e. each table has a primary key field in this format CX1234).
So far I have done the following:
1. Linked my 5 tables: Table 1 has 1 to 1 relationships with all the other 4 tables (where for each the join type is "include ALL records from Table 1 and only matching records from the related table).
2. I created a query which includes ALL the fields from ALL the linked tables.
3. I created a form based on that query (using Form Design button) and rearranged the fields so they are "grouped" based on each table (like I wanted to - my post above).

Each "page" of the form now shows a particular primary key record from Table 1 with all its fields for that record, and all the fields from the related tables for that record.

HOWEVER, I need to create some kind of a search form, that will GENERATE the corresponding forms for each primary key from Table 1. See the thing is, I can click "next" record and each individual form with a particular record shows up (with data from all tables for that record - like I wanted), however, I need something where I can just TYPE the actual primary key and that particular form (with that particular record and fields from all the tables) shows up. Because I need the user to be able to SEARCH for individual records, rather then click next till they reach the record...

Basically - a look-up database where by imputing (typing) a primary key from Table 1 will show records from Table 1 and all the related records for that primary key from Table 2, Table 3, Table 4, and Table 5.

Any help how to do this now when I already have one "form" for each individual record (with data from all the tables)? Is it some kind of a subform I need to design, that will link to my form already created?

Thank you again very much!

[June7]

Aside from the intrinsic search/filter tools, options:

http://datapigtechnologies.com/flash...tomfilter.html

I prefer:

http://www.allenbrowne.com/ser-62.html

[MaggieN]

Thank you!
I actually figured it out by (in form header) creating an unbound textbox with a button called Search. The button in Events has a macro that searches for primary key typed in the textbox, and returns all records in Table 1, 2, 3, 4, and 5.
I followed this video to do this: https://www.youtube.com/watch?v=wCPs4aE5I2w

Now, I have Table 6, that DOES NOT relate to Table 1 (and none of the other tables). (Primary key field has unique values not found in any of the tables.) Is there ANY way I can add the table into the form, and have the text Search box be also searchable for unique records from Table 6 (on the same form)?

Or do the tables ALWAYS need to be LINKED in order to create a query and create a form based on them? I was trying to just add the fields from Table 6 to my form (that is based on Table 1), but it's asking me for a join type between Table 1 and Table 6, and NONE of the three options of join type apply to this scenario (bcs there are NO matching records in Tables 1 and 6).

Even if I was able to create a separate Search text box (to only search Table 6) on the form (like in a lower part of it), I just can't seem to be able to do it on that same form... So I guess I will just have to have a separate form, to search records only from Table 6...

Any help would be greatly appreciated.

[June7]

If there is no PK/FK relationship and no link is set between tables, then the query will apply a Cartesian relationship - every record of each table will associate with every record of other table. Don't think you will like the result.