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

kstaelens

New member
Local time
Today, 23:58
Joined
Jan 17, 2024
Messages
7
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,
 

Attachments

  • Transpose.accdb
    444 KB · Views: 46
  • transpose.png
    transpose.png
    125.4 KB · Views: 30

ebs17

Well-known member
Local time
Today, 23:58
Joined
Feb 7, 2020
Messages
1,991
SQL:
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

New member
Local time
Today, 23:58
Joined
Jan 17, 2024
Messages
7
SQL:
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):
1705672425676.png
 

ebs17

Well-known member
Local time
Today, 23:58
Joined
Feb 7, 2020
Messages
1,991
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.
 

ebs17

Well-known member
Local time
Today, 23:58
Joined
Feb 7, 2020
Messages
1,991
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

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:58
Joined
May 7, 2009
Messages
19,246
i added a button on your form to Transpose.
see the Code behind it.
 

Attachments

  • Transpose.accdb
    680 KB · Views: 50

Users who are viewing this thread

Top Bottom