How to search between main & sub-form?

[hkgary33 via AccessMonster.com]

Dear all,
In my database, I've built a main form (frmVIEW) and it have a subform
(frmAPPDATA). In the main form, it shows the basic information of each file,
which are stored in table named tbl_FileInfo. It includes the key field
FileNo. While in the subform, it lists the information of those applicants
involved in that particular file, and each applicant's ID is shown in that
subform. All these information in the subform is stored in a table named
tbl_App. tbl_FileInfo and tbl_App are linked with the key field FileNo, and
the relationship is one-to-many (becuz one file may involve many applicants).
Now my problem is that I want to add a button call "Auto Search" in the main
form such that once the click event is triggered, it will automatically
obtains all applicants' IDs in that file and use it as a criteria to search
the applicants. The result is to show for each applicant, what files they
were involved and its detail infomation stoed in tbl_FileInfo. I really donno
how to use a query to search it . Also, how can I show the result of this
query in a report?
the expected result is :
ApplicantID FileNo...
1234 1
3
6
1235 2
4
5
....




Thanks for your kind help!!!!!!!

Gary

 

[Ken Sheridan]

Gary:

With your current two tables you could create a report based on a query
which joins tbl_App and tbl_FileNo. The WHERE clause of this query should
include a correlated subquery which references the FileNo of the current
record in the form, e.g.

SELECT tbl_FileNo.FileNo, A1.ApplicantID
FROM tbl_FileNo INNER JOIN tbl_App As A1.
ON tbl_FileNo.FileNo = A1.FileNo
WHERE EXISTS
(SELECT *
FROM tbl_App AS A2
WHERE A2.ApplicantID = A1.ApplicantID
AND A2.FileNo = Forms!frmAppData!FileNo);

You can of course include other fields from each table in the outer query's
SELECT clause if you wish. The way this works is that the outer query returns
any row where at least one row is returned by the subquery where the
applicant's ID is the same as the applicant's ID in the current row of the
outer query and the FileNo is the same as the FileNo of the form's current
record. So if the form's current FileNo is 42 and applicants 99 and 101 are
involved in that file a row will be returned by the subquery for any row in
the outer query where the applicant's ID is 99 or 101 regardless of what the
FileNo of that row is, i.e. all files for any applicant involved in the
form's current file will be returned by the outer query.

Group the report (not the query) on the ApplicantID field. Put the
ApplicantID and any other fields you want regarding each applicant in the
group header and the fields regarding the files in the detail section.

You then simply open the report with code along these lines in the button's
Click event procedure:

DoCmd.OpenReport "YourReport", View:= acViewPreview

However, as each applicant may be involved in many files and each file may
involve many applicants, having just two tables involves a lot of redundancy,
which leaves the door open to update anomalies, as the data regarding each
applicant would have to be repeated for each file in which they are involved.

The relationship between applicants and files is in fact many-to-many. This
type of relationship is modelled by a third table, so you should have one
table for files (with one row per file), one for applicants (with one row per
applicant) and a third which models the relationship between them by having a
FileNo column and an ApplicantID column, each a foreign key referencing the
primary keys of the other two tables. Together these columns form the
composite primary key of the third table. For each file an applicant is
involved in there would be one row in this table with their ApplicantID in
one column and the relevant file numbers in the other. Conversely for each
file there would be a row for each applicant involved in that file.

Your present form's subform would be based on this third table, or possibly
on a query which joins it to the Applicants table. Opening the report from
the button on the main form would be just the same, but the report's query
would in this case join the third table to the tbl_FileNo table; it might
also join the Applicant's table if you wish to include data from that table
other than the applicant's ID in the report.

Ken Sheridan
Stafford, England