Multi Record/Query/Multi Table/Going Crazy Issues

[smbarney]

I have spent the last couple of days trying to figure out how to make this work.

I have three tables.

tblIntakeMain
[IntakeMainID]

tblIncidentDetails
[IncidentdeatailsID]

tblPersonnel
[PersonnelID]

On the main form I use subforms to link tblIncidentDetails and tblPersonnel to tblIntakeMain. Both subforms can, and do, have many entries. This all works fine. What is not working is the search form I am using.

I am using Gromits most excellent Search Form. The problem is when I create a query, qSearch, to bring together the three tables I get a multiple records which makes the searches very confusing and near useless. Is there anyway around this? Is there something I am missing? Is there another search method I could use that would work in a similar way as Gromits? Please help before the Prozac runs out and I lose my mind--what little it left.

 

[smbarney]

my bad....

tblIntakeMain is the main table. tblIncidentDetails and tblPersonnel have a one to many relationship to the tblIntakeMain using the fields I listed.

Thanks.

 

[smbarney]

Okay...I was afraid of that. You are correct, the tables are separate. The Main table contains case information (dates, locations, type of case, etc). The personnel table allows the user to assign any number of people to the case and has a Name field and a function field (investigator, supervisor, admin support, etc..). The IncidentUpdate table allows the user to add updates to the case(interview, case status, etc...) and has a timedate field and memo field. Any thoughts on how I might change this setup so I could search it? Unfortunately, the Personnel and IncidentUpdate tables require multiple entries. I cannot see a way around this. Am I missing something?