By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,089 Members | 1,952 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,089 IT Pros & Developers. It's quick & easy.

Trying to use IIF with DlookUp in query

P: 44
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....
Mar 8 '19 #1

✓ answered by 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:
Expand|Select|Wrap|Line Numbers
  1. SELECT FamilyId
  2. , ConcatRelated("PersonName", "qryPersonFamily", "RelationshipType = 'D' And FamilyID_fk = " & [FamilyID_pk]) As Dependents
  3. , Address
  4. 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)?

Share this Question
Share on Google+
17 Replies


Seth Schrock
Expert 2.5K+
P: 2,941
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:
Expand|Select|Wrap|Line Numbers
  1. tblPerson
  2. PersonID_pk
  3. F_Name
  4. L_Name
  5. ...
  6.  
  7.  
  8. tblFamily
  9. FamilyId_pk
  10. Address
  11. ...
  12.  
  13.  
  14. tblRelationships
  15. FamilyId_fk
  16. PersonId_fk
  17. 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.
Mar 8 '19 #2

P: 44
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.
Mar 8 '19 #3

P: 44
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 123-456-7890
DEPENDANTS Brian, Lily
Mar 8 '19 #4

Seth Schrock
Expert 2.5K+
P: 2,941
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT FamilyId
  2. , ConcatRelated("PersonName", "qryPersonFamily", "RelationshipType = 'D' And FamilyID_fk = " & [FamilyID_pk]) As Dependents
  3. , Address
  4. 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)?
Mar 8 '19 #5

P: 44
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
Mar 8 '19 #6

Seth Schrock
Expert 2.5K+
P: 2,941
Quick edit to qryPersonFamily: you will also need the RelationshipType field.

What is your Relationship type for spouse? S?
Mar 8 '19 #7

P: 44
Relationship Types:
S = Spouse
H = Head
D = Dependent
Mar 8 '19 #8

Seth Schrock
Expert 2.5K+
P: 2,941
So if the person you are currently looking at is the spouse, do you want the Head to show up in the Spouse field?
Mar 8 '19 #9

P: 44
exactly! the form or report will view as like a person profile showing all the details about them.
Mar 8 '19 #10

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

Expand|Select|Wrap|Line Numbers
  1. SELECT FamilyId
  2. , ConcatRelated("FullName", "qryPersonFamily", "RelationshipType = 'D' And FamilyID = " & [FamilyID]) As Dependents
  3. FROM tblFamily
I used pretty much your exact script.
It pulls the FamilyID values but the column with Dependents is blank
Mar 8 '19 #11

Seth Schrock
Expert 2.5K+
P: 2,941
Here is some air code for a query (untested).

Expand|Select|Wrap|Line Numbers
  1. SELECT PersonId_pk
  2. , tblPerson.F_Name
  3. , tblPerson.L_Name
  4. , S.F_Name & " " & S.L_Name As SpouseName
  5. , tblFamily.Address
  6. , ConcatRelated("PersonName", "qryPersonFamily", "RelationshipType = 'D' And FamilyID_fk = " & tblRelationships.FamilyID_fk) As Dependents
  7. , Address
  8. FROM tblFamily
  9. FROM ((tblPerson INNER JOIN tblRelationships ON tblPerson.PersonId_pk  = tblRelationships.PersonId_fk)
  10.     INNER JOIN tblFamily ON tblRelationships.FamilyId_fk = tblFamily.FamilyId_pk)
  11.     INNER JOIN (SELECT PersonID_pk, F_Name, L_Name, FamilyId_fk 
  12.                 FROM tblPerson INNER JOIN tblRelationships
  13.                 ON tblPerson.PersonID_pk = tblRelationships.PersonId_fk
  14.                 WHERE RelationshipType = "S" Or RelationshipType = "H") As S
  15.                 ON tblRelationships.PersonId_fk = S.PersonID_pk
  16.                     AND tblPerson.PersonID_pk <> S.PersonID_pk
You may need to add PersonID_pk to qryPersonFamily if it doesn't have it already.
Mar 8 '19 #12

Seth Schrock
Expert 2.5K+
P: 2,941
Can you run qryPersonFamily with no errors? Please provide the SQL for qryPersonFamily.
Mar 8 '19 #13

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

Yes I can run qryPersonFamily with no errors.

Expand|Select|Wrap|Line Numbers
  1. SELECT [F_Name] & " " & [L_Name] AS FullName, tblFamilyRelationship.Family_ID
  2. FROM tblPerson INNER JOIN tblFamilyRelationship ON tblPerson.PersonID = tblFamilyRelationship.PersonID;
Mar 8 '19 #14

twinnyfo
Expert Mod 2.5K+
P: 3,284
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.
Mar 9 '19 #15

Seth Schrock
Expert 2.5K+
P: 2,941
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.
Mar 9 '19 #16

twinnyfo
Expert Mod 2.5K+
P: 3,284
Sorry Seth, I was skimming too fast on a Saturday. Thanks for the hepp!
Mar 9 '19 #17

P: 44
thanks guys!!! This has helped a lot!
Mar 11 '19 #18

Post your reply

Sign in to post your reply or Sign up for a free account.