Problem with Approvers table query

[cojiro]

I have a problem figuring out the following query:

Initial table: Contains approvals for expense report submissions.

Fields: ReportID, FirstName, LastName, ApprovalDate.

Problem: There can be more than one record for each ReportID (i.e. Multiple approvers for a report).

What I would like my query to result as:
ReportID, App1FirstName, App1LastName, ApprovalDate1, App2FirstName, App2LastName, ApprovalDate2.

Condition: Approval 2 data could be null.


Does anyone have any Ideas?

Thanks for your help.

 

[BNPMike]

SQL produces tables (strictly of course they are relations) which have a column for each column in the table(s) used to source the query. That's how SQL works. What you want is an output table which is wider, and perhaps one that can change shape depending on the data. So you're up against it.

The obvious thing is to settle for:

ReportID, App1FirstName, App1LastName, ApprovalDate1,
App2FirstName, App2LastName, ApprovalDate2

You can get this easily by using a report to display the data.

 

[cojiro]

Thanks for the reply Mike, unfortunately that is very disheartening. It does seem impossible, but if I ever figure it out, I'll post it back here.
Thanks.

 

[OnTheFly]

I guess my question would be, Why do you want to pull the data in this manner?

It can be done (as I have had to do it) but not simply through a query. You would have to build a temp table of some kind, based on the greatest number of approvals in the table. And then you would need to populate it using a loop of some kind. It is not fun and involves extensive coding.

If you could tell us why you want to do this, we might have a better solution.

OnTheFly

 

[cojiro]

To answer your question OnTheFly, the users requested the data be presented this way. It makes it easier for analysis (there are actually other fields such as report amount).

On the upside, I found a solution using SQL. It needs to be cleaned up a bit, but it works. The only drawback is occasionally the first approver will end up where the sencond should be and vice versa. This is fine with me though, because the date/time stamp shows who has approved first.

Anyways, if your interested, this is how I got it to work (I grouped by a concatenated version of the date and name, and then broke it back out.):

SELECT [ConcatGroup].CONSSUBMISSIONID,
Right( [ConcatGroup].ApprovalOne, len( [ConcatGroup].ApprovalOne) - instr([ConcatGroup].ApprovalOne, "*")) as [Approver One Name],
Left(rtrim( [ConcatGroup].ApprovalOne), instr([ConcatGroup].ApprovalOne, "*")-1) as [Approval One Date],
IIF( [ConcatGroup].Approvaltwo is null, null, Right( [ConcatGroup].Approvaltwo, len( [ConcatGroup].Approvaltwo) - instr([ConcatGroup].Approvaltwo, "*"))) as [Approver Two Name],
IIF([ConcatGroup].Approvaltwo is null, null, Left(rtrim( [ConcatGroup].ApprovalTwo), instr([ConcatGroup].ApprovalTwo, "*")-1)) as [Approval Two Date]
FROM
(SELECT [Submissions and Approvers].CONSSUBMISSIONID, Min([Submissions and Approvers].COMPLETEDATETIME & "*" & [Submissions and Approvers].FIRSTNAME & " " & [Submissions and Approvers].LASTNAME) AS ApprovalOne,
IIf(Max([Submissions and Approvers].COMPLETEDATETIME & "*" & [Submissions and Approvers].FIRSTNAME & " " & [Submissions and Approvers].LASTNAME)=Min([Submissions and Approvers].COMPLETEDATETIME & "*" & [Submissions and Approvers].FIRSTNAME & " " & [Submissions and Approvers].LASTNAME),Null,Max([Submissions and Approvers].COMPLETEDATETIME & "*" & [Submissions and Approvers].FIRSTNAME & " " & [Submissions and Approvers].LASTNAME)) AS ApprovalTwo
FROM [Submissions and Approvers]
GROUP BY [Submissions and Approvers].CONSSUBMISSIONID as [ConcatGroup];