Problems with basing form on query

[LQ]

Originally, I had wanted to have a form with a subform that showed letters received on a particular patient. I set it up that way, but then my boss decided that she wanted the ability to search on fields that were in the subform, and I was unable to figure out a way to do that. Then I was trying to figure out how to base my form on a query that combined the two tables. The problem is, if each patient record (the "one" side of the relationship) has many letters (the "many" side), how can I show that in a form without showing multiple instances of each patient? Right now, if a patient has three letters, he will show up three times in the form based on the query. Am I doing something wrong in the query? Or can this just not be accomplished with a query?

Sorry to be so long-winded, but my boss is telling me to just dump everything into one table, and I really don't want to have to do that!

[This message has been edited by LQ (edited 11-28-2001).]

 

[mrssevans]

If the letters that you enter are permanent and they are not changing history then it might be beneficial to put it in the customer table.

 

[Rich]

Your boss is WRONG you have a One to Many relationship between Patients and letters, tell her to read a good book. What does she want to search for records of a particular patient or all patients?

 

[LQ]

I just realized that my initial post might not have been clear. I have two tables, one for patients, one for letters, with a one to many relationship between the two. The query would be based on both tables. But I don't see any way to separate out/group the resulting records by each patient. I want to see each patient once, but I want to see all the letters they received. For example:

Patient Letter Type
---------- ------------
John Smith Denial
Appeal
Review

Instead of:

John Smith Denial
John Smith Appeal
John Smith Review

Is that any clearer?

TIA

 

[LQ]

Rich...yes, I have told her that the "proper" way to do things would be to have a table with the patients and a SEPARATE table for the letters (and then I would be able to have a form with a subform). But because she wants to be able to search for details of the letters as well as particulars of the patients, I am at a loss as to what to do except to do as she says, have one giant, inefficient, flat table. Even though I am an amateur at this, not a professional developer, I still cringe at doing something so wrong. Any ideas of how I can satisfy her demands while at the same time keep to the proper database architecture?

[This message has been edited by LQ (edited 11-28-2001).]

 

[LQ]

Pat, I'm not completely sure I understand what you are saying. Or maybe it's just that I can't figure out how to get it to work. I created a search form with unbound fields. I used the fields to generate a dynamic SQL statement and then used that as the last parameter in the OpenMethod form to open up my form with the proper data showing. But no matter what I try, if I attempt to search on one of the fields that is in the subform, I get an Enter Parameter Value dialog box and my form opens up blank. Perhaps my syntax is incorrect or something, or maybe this just isn't possible?

Here is an abbreviated version of my "search" code:

'this creates a dynamic SQL statement that changes with each new criteria
Private Sub search_Click()
Dim mySql As String
Dim myWhere As String

'search the [letterinfo] table
mySql = "SELECT tbl_letterinfo.rec_id FROM tbl_letterinfo LEFT JOIN tblletter ON tbl_letterinfo.rec_id = tblletter.recid"
myWhere = ""

If Nz(Forms!frmsearch!txtname) <> "" Then
If myWhere <> "" Then
myWhere = myWhere & " AND"
End If
myWhere = myWhere & " (tbl_letterinfo.lastname Like '*" & Forms!frmsearch!txtname & "*')"
End If
If Nz(Forms!frmsearch!cboletter) <> "" Then
If myWhere <> "" Then
myWhere = myWhere & " AND"
End If
myWhere = myWhere & " (tblletter.lettertype Like '*" & Forms!frmsearch!cboletter & "*')"
End If

'add WHERE statement if any search criteria are provided
If myWhere <> "" Then
mySql = mySql & " WHERE " & myWhere
End If

DoCmd.OpenForm "frmmain", , mySql
DoCmd.Close acForm, "frmsearch"
End Sub