Field format in a make table query

[Romio_1968]

I am using this query to create a table

SELECT
    Titles.Title_ID,
    Titles.Title,
    SQLConcRow("SELECT Author.Author_Name FROM Author INNER JOIN TAJunction ON Author.Author_ID = TAJunction.Author_IDFK WHERE TAJunction.Title_IDFK = " & [Titles].[Title_ID],", ") AS AuthorNames,
    Titles.Timestamp,
    Titles.Publisher,
    Titles.PublishPlace,
    Titles.PrintYear,
    Titles.Media,
    SQLConcColumn("SELECT TDJunction.ClassCode_FK & ' • ' & Domains.Domain FROM TDJunction INNER JOIN Domains ON TDJunction.ClassCode_FK = Domains.ClassCode WHERE TDJunction.Title_IDFK = " & [Titles].[Title_ID],",") AS ClassCodeDomains,
    SQLConcRow("SELECT Inventory.Inventory_No FROM Inventory WHERE Inventory.Title_IDFK = " & [Titles].[Title_ID],",") AS InventoryNumbers,
    (SELECT Count(*) FROM Inventory WHERE Inventory.Title_IDFK = Titles.Title_ID) AS Supply,
    SQLConcRowCompact("SELECT Inventory.Inventory_No FROM Inventory WHERE Inventory.Title_IDFK = " & [Titles].[Title_ID],",") AS CompactInventoryNumbers,
    Titles.Call_No
INTO
    SearchTitleSource
FROM
    Titles
ORDER BY
    Titles.Title_ID;

The table is volatile, so it is recreated and deleted after each use.

The SQLConc functions are doing some data manipulation, bi compressing table fields goruped by a certain criteria into a sigle record (example: inventory numbers of multiple records are compressed into one record, as 1; 2; 5; 9
The problem is that the field is automaticaly formated to Short Text, and that results in a truncated result, since sometimes the length of the concatenation is longer then 255 characters.

This is such a query statemet

SQLConcRow("SELECT Inventory.Inventory_No FROM Inventory WHERE Inventory.Title_IDFK = " & [Titles].[Title_ID],",") AS InventoryNumbers,

The function is

Public Function SQLConcRow(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)

   SQLConcRow = Res
End Function

So what should I do in order to format InventoryNumbers field to Long Text?

Thank You.

 

[CJ_London]

I don't believe you can do it this way.

Pretty much the same question asked here with a solution

Access Make table with memo field as column output instead of Text

The Access make table below does the following: Final app number 10 has three sub apps 6, 7 ,8 in AppCombinedAreasandRegions. Their FirstOfRegion (Memo Field) respectively are "a","b","c". Based ...

stackoverflow.com

 

[ebs17]

format InventoryNumbers field to Long Text?

Try that:

SELECT
    ...,
    Left(SQLConcRow(...), 64.000)  AS InventoryNumbers,
    ...

 

[Romio_1968]

Try that:

SELECT
    ...,
    Left(SQLConcRow(...), 64.000)  AS InventoryNumbers,
    ...

still truncating to 255

 

[CJ_London]

did you try the suggestion in post#2?

 

[ebs17]

64000 without point

 

[Romio_1968]

64000 without point

That is what i did. I noticed the dot and removed it

 

[ebs17]

Going back one step, how many characters are in the columns in the normal select query (without INTO...)?

 

[Josef P.]

I suspect that ACE/JET cannot do this with select...into.

Test:

SELECT id, N, T, string(2000, "x") AS M INTO TestTab2
FROM TestTab

=> M is text(255)

Probably only remains:
1. create table
2. insert into NewTable (...) select ....

or
1. select..into (with long text data field from an other table)
2. Update data in field.

 

[Romio_1968]

Didn't really understood the question, but
The source table is Inventory. It contains as many records per Title_IDFK as copyes of the books, in the field named Inventory_No, a numeric field that contains up do 6 characters.
The SQLConcRow concatenates all these Inventory_No into InventoryNumbers field from SearchTitleSource.
The problem is that sometimes the result is longer then 255 characters and it is truncated. As an example, a list of 100 Inventory_No may have up to 600 characters, plus the separator and a space for each pair

 

[ebs17]

' as selection query, without line INTO...

SELECT
   ...,
   Len(SQLConcRow(...))  AS Len_InventoryNumbers,
   Len(String(2000, "x")) AS Len_x,
   ...

If the expected lengths are greater than 255 characters, you can create an append query from the selection query and transfer the content to an existing table.

 

[Romio_1968]

' as selection query, without line INTO...

SELECT
   ...,
   Len(SQLConcRow(...))  AS Len_InventoryNumbers,
   Len(String(2000, "x")) AS Len_x,
   ...

If the expected lengths are greater than 255 characters, you can create an append query from the selection query and transfer the content to an existing table.

i don't get it. Sorry. Can you provide more info?

I have to use Len(SQLConcRow("SELECT Inventory.Inventory_No FROM Inventory WHERE Inventory.Title_IDFK = " & [Titles].[Title_ID],",")) AS Len_InventoryNumbers? Isn't it a new field? What is the use ofit?

 

[CJ_London]

I'm going to drop out since my suggestion is of no interest to the OP

 

[Romio_1968]

I'm going to drop out since my suggestion is of no interest to the OP

Trying to work on that too
I have troble in converting the Make Table query into an Update query. I get an error about non updatable query i I dont get a fix yet.

 

[isladogs]

To specify datatypes for each field use a data definition query starting with CREATE TABLE...

W3Schools.com

W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.

www.w3schools.com

 

[Romio_1968]

I'm going to drop out since my suggestion is of no interest to the OP

It worked like you advised.
Thank you