Solved Query duplicating client??? Maybe? (1 Viewer)

[allen675]

Hello,

I have attached a screenshot of a query that I have put together. It wont be obvious from looking at it (because I've had to cover confidential information) however the two rows that are underlined are the same client record. I have a note system that stores notes for each client in a linked table under their customerID. This allows end user to make timestamped un-editable notes for that client. This query with some VBA then moves information to an Excel spreadsheet to send to directors. I was expecting the query to add all notes into one 'result field' in the query, however, It would appear that it has simply adding an additional row each time for clients depending on how many note records they have stored. What am I doing wrong and how do I get to my desired outcome?

 

[plog]

Its not duplicating anything. David has 1 record in clients and 2 records in notes. Number of results of a query based on an INNER JOIN is a simple calculation:

[Records In Table A] x [Records in Table B]

1 x 2 = 2

To get all notes into 1 note field I suggest you use Allen Brown's concate related function:

Microsoft Access tips: Concatenate values from related records

Return a string containing the values of related records.

allenbrowne.com

 

[allen675]

Its not duplicating anything. David has 1 record in clients and 2 records in notes. Number of results of a query based on an INNER JOIN is a simple calculation:

[Records In Table A] x [Records in Table B]

1 x 2 = 2

To get all notes into 1 note field I suggest you use Allen Brown's concate related function:

Microsoft Access tips: Concatenate values from related records

Return a string containing the values of related records.

allenbrowne.com

Cheers @plog

That makes sense and the link that you have sent looks exactly as I was looking for. Don't have time to try it now but will do tomorrow and will report back to you how I got on.

 

[Gasman]

Cheers @plog

That makes sense and the link that you have sent looks exactly as I was looking for. Don't have time to try it now but will do tomorrow and will report back to you how I got on.

If you cannot get to grips with Allen's code, @theDBguy also has his own version

Code Snippets

 

[allen675]

@Gasman funny you should say that! I've managed to follow Allen's instructions as far as adding the module but to be fair thats quite easy! This is the SQL that I have so far:

SELECT Client.Broker, Client.Lead_Date, [Client_FN] & " " & [Client_SN] AS LF, Client.Email_Address, Client.Mobile_No, Client.Email_Sent, Client.[SMS/WhatsApp_Sent], Client.[Phone_Call_#1], Client.[Phone_Call_#2], Client.[Phone_Call_#3], Client.ClientStatus, Client.Mortgage_Type, Client.Mortgage_Amount, [NoteDate] & ": " & [Note] AS Notes
FROM Client INNER JOIN NoteHistory ON Client.CustomerID = NoteHistory.CustomerID;

Which was giving me the result as it did when I opened this thread. Now I'm sitting hear scratching my bonce (head) trying to understand how I fit that into this OR this into that:

SELECT CompanyName,  ConcatRelated("OrderDate", "tblOrders", "CompanyID = " & [CompanyID])
FROM tblCompany;

Which is Allen's example.

HELP!

 

[Gasman]

I thought you wanted to concatenate the notes?

 

[allen675]

Yes I do but I also want said notes to then show at the end of the client record row under notes. An I getting confused or the wrong end of the stick? Sorry

 

[Gasman]

Read ALL of that link, it has a full explanation as to how it works.
Your FullNotes field will hold the result of the concatenation. Not sure if you can give it an alias of Notes, if you have a field by that name? Try it anyway.

 

[allen675]

Yep looked at all of the link still don't get it! As you can probably tell I don't do this sort of thing for a living!

 

[Gasman]

Try the other link then. It is simpler.

 

[allen675]

@Gasman I have looked at the other link and it makes as about as much sense as the first. Its all very well telling someone to read something, its all there, but if they don't understand in the first instance they still wont understand after reading it. The links tell you what will happen and explains the arguments, agreed. Its a bit like me handing my daughter a sheet of French, not being her first language, she isn't going to be able to understand it just by reading it, she needs someone to teach and give her some guidance. Its no different with SQL. How do I make it work in my scenario and where does it go?

 

[Gasman]

You would need to show your code so far and say where the Notes field is in which table and how they are linked.

 

[mike60smart]

@Gasman I have looked at the other link and it makes as about as much sense as the first. Its all very well telling someone to read something, its all there, but if they don't understand in the first instance they still wont understand after reading it. The links tell you what will happen and explains the arguments, agreed. Its a bit like me handing my daughter a sheet of French, not being her first language, she isn't going to be able to understand it just by reading it, she needs someone to teach and give her some guidance. Its no different with SQL. How do I make it work in my scenario and where does it go?

Why not just create a Report based on your query and then in the Report you can group by Client.

 

[allen675]

You would need to show your code so far and say where the Notes field is in which table and how they are linked.

I've managed to get @theDBguy code to work only by trial and error and watching a few YT videos.

Regards

 

[Gasman]

@Gasman I have looked at the other link and it makes as about as much sense as the first. Its all very well telling someone to read something, its all there, but if they don't understand in the first instance they still wont understand after reading it. The links tell you what will happen and explains the arguments, agreed. Its a bit like me handing my daughter a sheet of French, not being her first language, she isn't going to be able to understand it just by reading it, she needs someone to teach and give her some guidance. Its no different with SQL. How do I make it work in my scenario and where does it go?

No, it is more like telling someone how to make a cup of tea. Only in your case you want a cup of coffee.
However you need to understand what needs to be changed to get that cup of coffee. It is not the kettle, the cup or the water.

Effectively you are creating a new field from the results of whichever function you want to use.
let's go with theDBguy's function.

Public Function SimpleCSV(strSQL As String, _
            Optional strDelim As String = ",") As String
'Returns a comma delimited string of all the records in the SELECT SQL statement
'Source: http://www.accessmvp.com/thedbguy
'v1.0 - 8/20/2013

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strCSV As String

Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

'Concatenate the first (and should be the only one) field from the SQL statement
With rs
    Do While Not .EOF
        strCSV = strCSV & strDelim & .Fields(0)
        .MoveNext
    Loop
    .Close
End With

'Remove the leading delimiter and return the result
SimpleCSV = Mid$(strCSV, Len(strDelim) + 1)

Set rs = Nothing
Set db = Nothing

End Function

Just seen your extra post. Well done . Nothing like accomplishing something yourself.
Plus if you want to continue with Access, that is the only way?

Try and understand it as well.

I'll stop what I was going to do now then.

Please post your solution, as that will help others in the future, who had the same difficulty you did.

FWIW I use AB's method in a report of mine (theDBguy's version was not available to me then) in a report control to get all the common data in that control. So not just for queries

 

[Gasman]

This is the AB method

=ConcatRelated("CrewName","QryCrewStandby","QryCrewStandby.Rank = """ & [Rank] & """ AND QryCrewStandby.On_Date = #" & Format([Dated],"mm/dd/yyyy") & "#","CrewNameRev")

I wanted to return the crew name from QryCrewStandBy where the Rank was equal to the Rank for that particular report record and the QryCrewStandby.On_Date matched a Date in that Record. finally I wanted them presented in alphabetical order by surname then initials, hence the CrewName in reverse.
That is result in the above post.