Solved Concatenate a query group. Is it possible? (1 Viewer)

[Romio_1968]

I have the following table structure and relationships

I created a query that extracts data from the above tables. The SQL code for this Query is:

SELECT Titles.Title_ID, Titles.Title, Author.Author_Name, Inventory.Inventory_No
FROM
  (Titles INNER JOIN Inventory ON Titles.Title_ID = Inventory.Title_IDFK)
  LEFT JOIN
    (Author RIGHT JOIN TAJunction
    ON Author.Author_ID = TAJunction.Author_IDFK)
  ON Titles.Title_ID = TAJunction.Title_IDFK
ORDER BY Titles.Title_ID, Inventory.Inventory_No;

The output of the query is:

So for the first Title, I have one Title_ID, two Authors and also two copies, each one with its own inventory
The second one have Title_ID 2, no author, and inventory No.2

I need to obtain an output like this:

Title_ID 1 # Some Book # Dere, John; Doe, John; # Inventrory_No 1, 2
Title_ID 2 # Other Book # # Inventrory_No 3

So I need for each Title to merge somehow dte data in column.

If anybody can help,
Thank You



____________________________________________________________________
RESOLUTION

The solution for this thread is offered by ebs17 in his answer
Tested and worling

 

[arnelgp]

goggle this:
Microsoft Access tips: Concatenate values from related records (allenbrowne.com)

 

[ebs17]

' in a standard module
Public Function SQLListe(ByVal AnySQL As String, _
                         Optional ByVal SepF As String = ";", Optional ByVal SepR As String = ";", _
                         Optional ByVal NoNullFields As Boolean = True) As String

    Static db As DAO.Database
    Static rs As DAO.Recordset
    Dim i As Long, Res As String, Tmp As String

    If db Is Nothing Then Set db = CurrentDb
    Set rs = db.OpenRecordset(AnySQL, dbOpenSnapshot, dbReadOnly)
    With rs
        Res = ""
        Do While Not .EOF
            Tmp = ""
            For i = 0 To .Fields.Count - 1
                If Not (NoNullFields And IsNull(.Fields(i))) Then Tmp = Tmp & SepF & .Fields(i)
            Next
            If Tmp <> "" Then Res = Res & SepR & Mid(Tmp, Len(SepF) + 1)
            .MoveNext
        Loop
        .Close
    End With
    If Len(Res) > 0 Then Res = Mid(Res, Len(SepR) + 1)

    SQLListe = Res
End Function

Then the approach for the query:

SELECT
   T.Title_ID,
   T.Title,
   SQLListe(...) AS [Inventory Numbers],
   SQLListe(...) AS [Author Names]
FROM
   Titles AS T

Now the function for composing the values needs to get its arguments:

SELECT
   T.Title_ID,
   T.Title,
   SQLListe("SELECT Inventory_No FROM Inventory WHERE Title_IDFK = " & T.Title_ID, ", ") AS [Inventory Numbers],
   SQLListe("SELECT A.Author_Name FROM Author AS A INNER JOIN TAJunction AS J ON A.Author_ID = J.Author_IDFK
WHERE J.Title_IDFK = " & T.Title_ID, ", ") AS [Author Names]
FROM
   Titles AS T

 

[Gasman]

@theDBguy also has a function

Code Snippets

 

[Romio_1968]

' in a standard module
Public Function SQLListe(ByVal AnySQL As String, _
                         Optional ByVal SepF As String = ";", Optional ByVal SepR As String = ";", _
                         Optional ByVal NoNullFields As Boolean = True) As String

    Static db As DAO.Database
    Static rs As DAO.Recordset
    Dim i As Long, Res As String, Tmp As String

    If db Is Nothing Then Set db = CurrentDb
    Set rs = db.OpenRecordset(AnySQL, dbOpenSnapshot, dbReadOnly)
    With rs
        Res = ""
        Do While Not .EOF
            Tmp = ""
            For i = 0 To .Fields.Count - 1
                If Not (NoNullFields And IsNull(.Fields(i))) Then Tmp = Tmp & SepF & .Fields(i)
            Next
            If Tmp <> "" Then Res = Res & SepR & Mid(Tmp, Len(SepF) + 1)
            .MoveNext
        Loop
        .Close
    End With
    If Len(Res) > 0 Then Res = Mid(Res, Len(SepR) + 1)

    SQLListe = Res
End Function

Now the function for composing the values needs to get its arguments:

SELECT
   T.Title_ID,
   T.Title,
   SQLListe("SELECT Inventory_No FROM Inventory WHERE Title_IDFK = " & T.Title_ID, ", ") AS [Inventory Numbers],
   SQLListe("SELECT A.Author_Name FROM Author AS A INNER JOIN TAJunction AS J ON A.Author_ID = J.Author_IDFK
WHERE J.Title_IDFK = " & T.Title_ID, ", ") AS [Author Names]
FROM
   Titles AS T

Holly ...
It actually works

So SQLListe does the concatenation of the fields in two separate subQueryes.
Then you have a simple query based on a Titles table, with the concatenated strings as calculated fields.
What a charm!

Thank you

 

[Pat Hartman]

Your schema is INCORRECT. I'm assuming that Record_ID is an autonumber but you don't want to use the autonumber as the PK. That is wrong since title is not guaranteed to be unique. However having Title_ID and Title is confusing. Are you getting some unique identifier from some other application and that is what you are calling Title_ID?

If Title_ID is a UNIQUE identifier, then you do not need an autonumber so remove Record_ID. There is no reason for it to exist. The only reason to ever have an autonumber in your table is if you are using it as the primary key. And If Record_ID is the primary key, then the FK in Inventory and TAJunction must both be Record_IDFK, In that case, you might want to get rid of Title_ID unless you are using it to connect with an external application.

 

[Romio_1968]

Pat, you are right. Record_ID does not have a obvious reason to exist.
The most important key is Title_ID. It is unique and hanndled by code, not by user. There is still something. The Title_ID is a little bit "cleaner", at least on the last record. He take it's value on an +1 increment, so if the last record is deleted, the Title_ID goes back. There is some bureaucracy attached to the project, as for every missing Inventory_No must exist a documented reason. It will be annoying to write in the ledger something like "computer glitch". That doesn't mean that I cannot uset the Title_ID in the same way, but not as an Autonumbrer
Yet, while working on the architecture, for reason I do not really understood that time (neither now i don-t get it), everybody adviced to have that Autonumber PK, but not to use it as join key. When I asked why, the answer was "You don't want your join field to be Autonumber". Quite clear, so ... I don't want that. Moreover, I am working for some time on this, i posted the tables schema few times and nobody seemed to bother on that useles field
Anyway, there is no action linked to that key.
Probably i will delete it at the end.
As usual, your advice is pertinent at it was noted.
Most of your recommendations are in the code, now.
If it will not be too much, when I am ready to close the job, I will post the final version of the project.

 

[Pat Hartman]

The autonumber never has to be visible to the user. It is quite valid to have an autonumber primary key and a unique user friendly ID. If you are NOT using the autonumber to create the relationships - GET RID OF IT! It has NO purpose.

Personally, I would use the Autonumber as the PK and for the relationships and just leave the Title_ID as a user friendly unique identifier that exists as a data field which is NEVER used in relationships. I would not call it Title_ID because I like to reserver the use of the "ID" suffix for autonumbers so I would call it Title_CD or Title_Num or for people who can't spell Title_No Of course changing the relationships at this point might be inconvenient since you have to remap all the data. So, going with plan 1 and REMOVING the autonumber is OK. But, please don't leave stuff like this for "later". Do it when you find you need to do it - like now - before you accidentally use the field for something and make a mess for yourself to clean up.