How to merge crosstab info down in Access?-CodePudding

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;

CodePudding user response：

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).

GROUP BY File_Name means that one row is created for each distinct value of File_Name.
Since this will merge several rows into one, you must specify an aggregate function for every column in the SELECT list not listed in the GROUP BY clause. This can be e.g. SUM(), AVG(), MIN() or MAX(). See SQL Aggregate Functions for a complete list. Since any Null value is considered to be less than any other value, MAX() will take this non-Null value from the merged rows.