sqlms-accesscrosstabmailmerge

How to merge crosstab info down in Access?


Not sure if this is possible but I'm hoping it is. I am using MS Access for Estate Planning for work. I've gotten to the point where I've got the data to look like this:

File_Name Executor_1 Executor_2 Beneficiary_1 Beneficiary_2
Hill, Hank Peggy Hill Peggy Hill
Hill, Hank Bobby Hill Bobby Hill
Gribble, Dale Nancy Gribble
Gribble, Dale Joseph Gribble Joseph Gribble
Gribble, Dale John Redcorn

But I need it to look like this:

File_Name Executor_1 Executor_2 Beneficiary_1 Beneficiary_2
Hill, Hank Peggy Hill Bobby Hill Peggy Hill Bobby Hill
Gribble, Dale Nancy Gribble Joseph Gribble Joseph Gribble John Redcorn

I need it in the latter format so I can use MailMerge in word and create the Will. Can anyone provide any guidance? We don't currently use any software for Est. Planning so anything beats having to go into Word manually and retype everything. Please let me know if more information is needed.

Edit: This is what the SQL looks like:

TRANSFORM Last(File_Roles.File_Name) AS LastOfFile_Name

SELECT File_Roles.Executor_1, 
File_Roles.Executor_2, 
File_Roles.Beneficiary_1, 
File_Roles.Beneficiary_2, 
File_Roles.Trustee_1,
File_Roles.Trustee_2, 
File_Roles.Guardian_1, 
File_Roles.Guardian_2, 
File_Roles.ATTY_IF_1, File_Roles.ATTY_IF_2, 
File_Roles.HCATTY_IF_1, 
File_Roles.HCATTY_IF_2

FROM File_Roles

GROUP BY File_Roles.Executor_1, 
File_Roles.Executor_2, 
File_Roles.Beneficiary_1, 
File_Roles.Beneficiary_2,
File_Roles.Trustee_1,
File_Roles.Trustee_2, 
File_Roles.Guardian_1, 
File_Roles.Guardian_2, 
File_Roles.ATTY_IF_1, 
File_Roles.ATTY_IF_2, 
File_Roles.HCATTY_IF_1, 
File_Roles.HCATTY_IF_2

PIVOT File_Roles.File_Name;

Solution

  • You can use GROUP BY and MAX()

    SELECT
        t.File_Name,
        MAX(t.Executor_1) As Executor_1,
        MAX(t,Executor_2) As Executor_2,
        MAX(t.Beneficiary_1) As Beneficiary_1,
        MAX(t.Beneficiary_2) As Beneficiary_2
    FROM table_or_query t
    GROUP BY File_Name
    

    But maybe you can fix your original crosstab query to do this right away. Probably you are doing the grouping wrong. You must group by File_Name in the crosstab query and apply Max to the total row of the value (so it is difficult to say without seeing this query).