Creating either one or two outputs depnding on field value 1

[Cosette]

Hi all,

I have a a query with first and last names as well as addresses for two people. This query also indetifies if the people are married to each other. I would like to know if there is a way, depending on the value of married, to create either one label (when they share the same address) or two labels (when they aren't). Are there any keywords out there?

Thanks

David

 

[Remou]

Does the married field contain the ID of the married member?
[tt]ID Name Married
12 Jo 23
23 Joe 12[/tt]

Or is there another way by which the related records can be identified? Address, perhaps? It may be possible for you to use:
How to concatenate multiple child records into a single value
faq701-4233
You may run into 'political correctness' issues, if you choose either partner as the recipient.

 

[Cosette]

Unfortunately, this is not the case. Basically, my DB relates to students and their parents. I have a tblStudent and a tblParent. In the tblStudent, I have two fk fields, first parent and second parent. So for most records, I have two parents field. When they are married, it is easy - one label will do. However, when they are not married, I need to generate two labels. That is where I get stuck. Don't know how to go about it.

Thnaks

David

 

[DougP]

If both parents at different addresses are in the tblParents Right. If not that how you can fix it.


DougP, MCP, A+

 

[Remou]

Here is a very rough idea.
Query 1: QryUnion - Select All Parents
[tt]SELECT tblStudent.Mother, tblParent.Married, tblParent.FirstName, tblParent.LastName, tblParent.Address
FROM tblStudent INNER JOIN tblParent ON tblStudent.Mother = tblParent.Code
UNION SELECT tblStudent.Father,tblParent.Married, tblParent.FirstName, tblParent.LastName, tblParent.Address
FROM tblStudent INNER JOIN tblParent ON tblStudent.Father = tblParent.Code;[/tt]

Query 2: QryX - Select Married Mothers
[tt]SELECT [tblStudent].[Mother]
FROM tblStudent INNER JOIN tblParent ON [tblStudent].[Mother]=[tblParent].

WHERE ((([tblParent].[Married])="Yes"));[/tt]

Query 3: QryXandUnion - Exclude Married Mothers from Labels
[tt]SELECT qryUnion.Mother, qryX.Mother, qryUnion.FirstName, qryUnion.LastName, qryUnion.Address
FROM qryUnion LEFT JOIN qryX ON qryUnion.Mother = qryX.Mother
WHERE (((qryX.Mother) Is Null));[/tt]

However, this may well cause a storm among excluded mothers, so it might be safer to build something using recordsets, so you end up with "Mr & Dr Blank". :-)

 

[Cosette]

Sorry DougP and Remou, I don't understand what you wrote. I guess I am not clear enough. I have one table, called tblParent, where I logged all the info for every parent. I then duplicated this table tblParent_1 to then be able to create two forein fields in the table tblStudent. Maybe the design was incorrect, but I figured it was the best one available.

So let me ask another way that Remou hinted but which the FAQ was not addressing entirely: Can I match two different records in a table based on one field being identical?

In other words, can

ID FirstName LastName Address
1 Bill Clinton WhiteHouse
2 Hilary Rodham WhiteHouse

Be merged into Bill Clinton Hillary Rodham White House?

Thanks

David

 

[ProgramError]

I think a duplicate query is needed here.

Ian Mayor (UK)
Program Error
Programmers do it one finger at a time!

 

[Remou]

Here is another rough idea:
Query (SQL view):
[tt]SELECT tblStudent.Code, Getparents([mother],[father]) AS Label1, IIf([label1]="2 Labels",Getparents([mother],""),"") AS Label2, IIf([label1]="2 Labels",Getparents("",[father]),"") AS Label3
FROM tblStudent;[/tt]

Function:

Function GetParents(Mo, Fa)
Dim rsM As DAO.Recordset
Dim rsF As DAO.Recordset

GetParents = ""

'Code is Primary and Text
If Trim(Mo) & "" <> "" Then
Set rsM = CurrentDb.OpenRecordset _
    ("Select * From tblParent Where Code='" & Mo & "'")
    GetParents = rsM!Title & " " & rsM!FirstName _
        & " " & rsM!LastName & vbCrLf & rsM!Address
End If

If Trim(Fa) & "" <> "" Then
Set rsF = CurrentDb.OpenRecordset _
    ("Select * From tblParent_1 Where Code='" & Fa & "'")
    GetParents = rsF!Title & " " & rsF!FirstName _
        & " " & rsF!LastName & vbCrLf & rsF!Address
End If

If Trim(Mo) & "" <> "" And Trim(Fa) & "" <> "" Then
    If rsM!Married = "Yes" Then
        If rsF!Married = "Yes" Then
            GetParents = rsF!Title & " and " & rsM!Title _
                & " " & rsF!FirstName & " " & rsF!LastName _
                & vbCrLf & rsF!Address
        Else
            GetParents = "Data error: not married to each other"
        End If
    Else
        GetParents = "2 Labels"
    End If
End If

End Function

But you probably need to look at your design. I have often seen this recommended:

http://r937.com/relational.html

 

[Cosette]

Remou,

I only got back today. Thank you for all your efforts trying to help me solve this quandry. I hope I can use your code and will go look at the line. In any case, all this deserves a start in my book.

David