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!