Trying to use IIF with DlookUp in query

[brikusi]

Writing a query for a database where in the query it's displaying the profile of a family. I want the dependents to appear in 1 field concatenated and separated by commas. So if there is more than 1 dependent in the family it appears in query in 1 field listed.

New to Access so i was trying to get 1 piece to work at a time.

what I have so far:
Dependents: IIf([Is Dependent]=True,DLookUp("[F_Name] & ' ' & [L_Name]","[tblPerson]"),'')

Table Sample:
Person Table
- PersonID (PK)
- F_Name
- L_Name
- DOB
- Email
- Mobile
- Sex
- Is Dependent
- FamilyID (FK)
etc....

Family Table
- FamilyID (PK)
- HeadID
- SpouseID
- Address
- City
- State
etc....

[Seth Schrock]

DLookup() can only return one field, not a concatenated value ([F_Name] & ' ' & [L_Name]). Also, DLookup() only returns the first record which matches the criteria provided. In your case, no criteria is provided so it will return the first record in the family table. All this to say that you don't want to use DLookup() for this. Look at Allen Brown's Concatenate Related Records for a method to do what you are looking for.

I would also recommend changing your table structure. Consider the situation where a child grows older and gets married. He/she is now a child in one family and the head of household in another. It could get confusing where the relationships are in your current setup. Do you leave the FamilyID (FK) to be the parent's family and then put the personID in the HeadID or SpouseID?

Consider something like this:

tblPerson
 PersonID_pk
F_Name
L_Name
...
 
 
 tblFamily
FamilyId_pk
Address
...
 
 
 tblRelationships
FamilyId_fk
PersonId_fk
RelationshipType

Relationship type could be a dropdown box with Head of Household, Spouse, Child, etc. options. This allows the same person to be in two families at the same time. This is called a Many-to-Many relationship. The table relationships go through the table tblRelationships to tie each person to the appropriate family.

Just something to consider.

[brikusi]

Thanks for you response, it has been helpful. I have changed my tables to your suggestion above using a Many to Many relationship. I also copied the function into my Access DB. I am still unsure now how to use the concatrelated function to put my dependents into 1 field in the query based on the FamilyID.

[brikusi]

I only want to concatenate the the fields where relationship Type = D

Example Result I want in Query:

PERSONID 0001
F_NAME John
L_NAME Doe
SPOUSE Jane Doe
ADDRESS 1234 Whatever Lane
MOBILE [phone]
DEPENDANTS Brian, Lily

[Seth Schrock]

So you are going to need a query that joins tblRelationships to tblPerson and returns the person's name (concatenated if so desired) and the family ID (I'll call it qryPersonFamily). You can then use the ConcatRelated() function in a query like this:

SELECT FamilyId
, ConcatRelated("PersonName", "qryPersonFamily", "RelationshipType = 'D' And FamilyID_fk = " & [FamilyID_pk]) As Dependents
, Address
FROM tblFamily

How are you wanting to view your data? By the family and list the member in that family, or by the person and show the family(ies) that person is related to along with their spouse (if applicable)?

[brikusi]

I want show my data in the report by person and be able to list all relatives and show the address only once along with their spouse listed and dependents all in 1 field

[Seth Schrock]

Quick edit to qryPersonFamily: you will also need the RelationshipType field.

What is your Relationship type for spouse? S?

[brikusi]

Relationship Types:
S = Spouse
H = Head
D = Dependent

[Seth Schrock]

So if the person you are currently looking at is the spouse, do you want the Head to show up in the Spouse field?

[brikusi]

exactly! the form or report will view as like a person profile showing all the details about them.

[brikusi]

I still can't get the ConcatRelate function to work, getting: "Error 3061: Too few Parameters.Expected 2"

SELECT FamilyId
, ConcatRelated("FullName", "qryPersonFamily", "RelationshipType = 'D' And FamilyID = " & [FamilyID]) As Dependents
FROM tblFamily

I used pretty much your exact script.
It pulls the FamilyID values but the column with Dependents is blank

[Seth Schrock]

Here is some air code for a query (untested).

SELECT PersonId_pk
, tblPerson.F_Name
, tblPerson.L_Name
, S.F_Name & " " & S.L_Name As SpouseName
, tblFamily.Address
, ConcatRelated("PersonName", "qryPersonFamily", "RelationshipType = 'D' And FamilyID_fk = " & tblRelationships.FamilyID_fk) As Dependents
, Address
FROM tblFamily
FROM ((tblPerson INNER JOIN tblRelationships ON tblPerson.PersonId_pk  = tblRelationships.PersonId_fk)
    INNER JOIN tblFamily ON tblRelationships.FamilyId_fk = tblFamily.FamilyId_pk)
    INNER JOIN (SELECT PersonID_pk, F_Name, L_Name, FamilyId_fk 
                FROM tblPerson INNER JOIN tblRelationships
                ON tblPerson.PersonID_pk = tblRelationships.PersonId_fk
                WHERE RelationshipType = "S" Or RelationshipType = "H") As S
                ON tblRelationships.PersonId_fk = S.PersonID_pk
                    AND tblPerson.PersonID_pk <> S.PersonID_pk

You may need to add PersonID_pk to qryPersonFamily if it doesn't have it already.

[Seth Schrock]

Can you run qryPersonFamily with no errors? Please provide the SQL for qryPersonFamily.

[brikusi]

Thanks so much for all the attention you're giving this. I really appreciate your help.

Yes I can run qryPersonFamily with no errors.

SELECT [F_Name] & " " & [L_Name] AS FullName, tblFamilyRelationship.Family_ID
FROM tblPerson INNER JOIN tblFamilyRelationship ON tblPerson.PersonID = tblFamilyRelationship.PersonID;

[twinnyfo]

There is an easier method to do this concatenation. You can build a small VBA procedures that recceives the Family ID and returns a concatenate list f family members. You simply build a Recordset based upon the criteria, and concatenate all the desired fields.

Let me try to come back to this later.

[Seth Schrock]

In your ConcatRelated() function, you are referencing a RelationshipType field and a FamilyID field, but your qryPersonFamily doesn't have RelationshipType returned at all in it and you have a underscore in the Family_ID field.

@TwinnyFo, we are using a VBA procedure to concatenate the dependents. Allen Browne's to be exact. See post #2.

[twinnyfo]

Sorry Seth, I was skimming too fast on a Saturday. Thanks for the hepp!

[brikusi]

thanks guys!!! This has helped a lot!