Combining fields with query (1 Viewer)

ClaraBarton

Registered User
Joined
Oct 14, 2019
Messages
35
I know how to concatenate fields and I know how to UNION fields but I'm stumped on a problem here that sounds so simple:
I would like to combine one field with multiple foreign keys. For instance a child has two parents with the same child ID. How do I get these two parents together into one record for a report? I use a subform to get them both together on the child form but I'd like to not use a sub report because I want it all on one line.
 

ClaraBarton

Registered User
Joined
Oct 14, 2019
Messages
35
Goodness! That works beautifully! Except... When there are 2 parents I get 2 records. The ConcatRelated function is a report field and not part of a query so I can't use distinct. How would I fix that?
 

ClaraBarton

Registered User
Joined
Oct 14, 2019
Messages
35
Alrighty, dbGuy. I used your simpleCSV function and I have the same problem, two records for each child that has two parents. How can I fix this?
 

theDBguy

I’m here to help
Joined
Oct 29, 2018
Messages
7,686
Alrighty, dbGuy. I used your simpleCSV function and I have the same problem, two records for each child that has two parents. How can I fix this?
You could try tweaking the SQL you pass to the SimpleCSV() function. Otherwise, you may have to fix the data first. Can you post the SQL string you used? Thanks.
 

Galaxiom

Super Moderator
Staff member
Joined
Jan 20, 2009
Messages
11,824
Use two subqueries.
Start with a query that gets all the ChildIDs then OUTER JOIN on ChildID to subqueries that return their parents separately to end up with ChildID, ParentID1, ParentID2.
 

theDBguy

I’m here to help
Joined
Oct 29, 2018
Messages
7,686
Hi. Just to add more to the above... I think the "beauty" of the SimpleCSV() function is you can pass to it any valid SQL statement. That means it can have JOINS between tables or even contain SubQueries. You can also pass a SQL statement using DISTINCT, if that helps. However, I can't say for sure how to better help you unless we can see the SQL statement you're using now.
 

Galaxiom

Super Moderator
Staff member
Joined
Jan 20, 2009
Messages
11,824
I think the "beauty" of the SimpleCSV() function is you can pass to it any valid SQL statement.
The SimpleCSV function doesn't help this problem at all. All it does in write all the records from one field into a line of CSV.

The key to the solution is the SQL strategy I described above.
 

theDBguy

I’m here to help
Joined
Oct 29, 2018
Messages
7,686
The SimpleCSV function doesn't help this problem at all. All it does in write all the records from one field into a line of CSV.

The key to the solution is the SQL strategy I described above.
Sorry, I guess I misunderstood the question. Thanks for the clarification!
 

theDBguy

I’m here to help
Joined
Oct 29, 2018
Messages
7,686
Attached is a small sample. The report shows the problem
Hi Clara. Since I am not familiar with your data, I am not sure I understand the problem. I made a minor adjustment to your report. Could you please let me know if it fixed the report or not? If not, could you then please explain a little more what is the problem? Thanks.
 

Attachments

theDBguy

I’m here to help
Joined
Oct 29, 2018
Messages
7,686
Wow, dbGuy, it looks good and yet I can't see the change you made. Tell me, please.
Hi. Well, like I said, I am not familiar with your data, so I don't know if what I did was a real fix for your issue. I checked the record source for your report and noticed you joined two tables, and the reason for the duplicate row is because of the multiple matching records in one of the tables. The first thing I did was remove the table on the right side, which seemed to reduce/eliminate the duplicates. Then, as an experiment, I added it back and simply removed the one field you're pulling from it into the report, since I noticed you weren't using that field in the report anyway, and that's where I left it for you to check. Hope that makes sense...
 

ClaraBarton

Registered User
Joined
Oct 14, 2019
Messages
35
Thank you so much. I do need that table but I can just duplicate the query for elsewhere. I would have NEVER figured it out!
 

theDBguy

I’m here to help
Joined
Oct 29, 2018
Messages
7,686
Thank you so much. I do need that table but I can just duplicate the query for elsewhere. I would have NEVER figured it out!
Hi. You're very welcome. plog, Galaxiom, and I were all happy to assist. Hope that fixed your issue. Good luck with your project.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom