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.

 

[Pat Hartman]

You didn't provide any information regrding how the tables are related.

 

[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.

 

[Pat Hartman]

You are not providing enough information and since your foreign keys have names different from the PK they point to, the casual observer cannot easily descern the relationships. I suspect that the relationships are separate. Main-->Incident and Main-->Personal. These three tables cannot be joined in a single query and produce any intelligable result. When more than two tables are joined, the tables MUST have either a hierarchial relationship such as Main-->Incident-->Personnal or one or more must be look up tables that will return only a SINGLE value.

 

[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?

 

[Pat Hartman]

Since the data in each relationship is independent, you will need two searches.