Transpose the result of a query and add it to another table (1 Viewer)

[kstaelens]

Hi there!
I have to create certificates and send them by mail to only one person. I use mailmerge for this. I need a table with only one record where I have my necessery details for the mailmerge; most important: the names of the attachments.

I've searched for hours and didn't find how to solve it, so I was very creative, created a form and by loading the form, the details are filled in in the table. This can go up to 50 attachments at a time, so it's getting really slow to load the form. Of course this is working fine at the moment, but there should be an easier/faster way I suppose

I can't share my db, but I created a small example.
Two tables: "Documents" and "Output". Output is used for the data for my mailmerge. Documents contains all the documents I can send by mail.
I created a form where I can chose which documents I want to send (based on a date); e.g. I want all documents for 4/01/2024 to be send, it will put in my query the documents that I want to have transposed in my Output table.
Thanks!
Kind regards,

 

[ebs17]

PARAMETERS
   parDate Date
;
TRANSFORM
   MIN(Q.Document) AS X
SELECT
   Q.Date
FROM
   (
      SELECT
         D.Date,
         D.Document,
         "Att" & Format(Dcount("*", "Documents", "Date = " & Format(D.Date, "\#yyyy\-mm\-dd\#") & " AND Document < '" & D.Document & "'") + 1, "00") AS ColName
      FROM
         Documents AS D
      WHERE
         D.Date = parDate
   ) AS Q
GROUP BY
   Q.Date
PIVOT
   Q.ColName

 

[kstaelens]

PARAMETERS
   parDate Date
;
TRANSFORM
   MIN(Q.Document) AS X
SELECT
   Q.Date
FROM
   (
      SELECT
         D.Date,
         D.Document,
         "Att" & Format(Dcount("*", "Documents", "Date = " & Format(D.Date, "\#yyyy\-mm\-dd\#") & " AND Document < '" & D.Document & "'") + 1, "00") AS ColName
      FROM
         Documents AS D
      WHERE
         D.Date = parDate
   ) AS Q
GROUP BY
   Q.Date
PIVOT
   Q.ColName

Thanks, we use semicolons as seperator, but I receive another error (syntaxis):

 

[ebs17]

we use semicolons as seperator

English is generally spoken in SQL and VBA, and commas are necessary as separators. Such linguistic peculiarities as you mention only apply when used in design views and when using assistants. So: just copy and try. You can vary and play with your own errors later.

 

[kstaelens]

Thanks again, I receive another error then:

 

[ebs17]

The problem can be the name Date, because it is a reserved word and is also used as a Date() function. In my testing it worked like this, but better
- do you use another name or
- you use square brackets to mask => [Date]

 

[arnelgp]

i added a button on your form to Transpose.
see the Code behind it.

 

[kstaelens]

i added a button on your form to Transpose.
see the Code behind it.

Thanks a lot!!