CODEWITHSUNDEEP
sqlms-access
ManuelCukRia
ManuelCukRia

Reputation: 3

SQL Find Pairs of Records from a Single Table

I have the following table of Project Report Submissions made by companies:

SubmissionID Type Date FileNr Company
1   X     01/01/2015   111   AAA
2   X/A   01/10/2015   111   AAA
3   X     01/15/2015   222   XXX
4   X/A   02/03/2015   111   AAA
5   X/C   08/16/2015   222   XXX
6   X/C   09/12/2015   222   XXX
7   X/C   10/25/2015   111   AAA
8   X/C   11/13/2015   111   AAA
9   X     12/22/2015   333   AAA
10  X/B   01/03/2016   111   AAA
11  X/B   01/14/2016   222   XXX
12  X/C   04/06/2016   333   AAA
13  X/B   01/16/2017   222   XXX

A company makes multiple submissions of different types per project which are all under the same file number.

So far I've been sorting the table after FileNr to see all submissions related to a particular file.

However, I would like to run a query that gives me the following table as a result by pairing the records related to a single FileNr:

FileNr Company X_Date X/C_Date1 X/C_Date2
111   AAA   01/01/2015   10/25/2015   11/13/2015
222   XXX   01/15/2015   08/16/2015   09/12/2015
333   AAA   12/22/2015   04/06/2016   null

This would enable me to analyze the time passed between the submission of the X and the X/C reports. Is this possible?

Upvotes: 0

Views: 44

Answers (1)

mazoula
mazoula

Reputation: 1321

As mentioned in the comments you want to do a CrossTab query, but in Access you can't do much formatting inside the crosstab. Instead reshape the data and then do a simple crosstab.

The first query is just for seeing and playing around with the reshaped variables:

enter image description here

expr1: IIf([Test].[SubmissionType]="X","X_Date",[Test].[SubmissionType] & "_Date" & DCount("SubmissionDate","Test","SubmissionDate <= #" & [Test].[SubmissionDate] & "# AND SubmissionType = '" & [test].[SubmissionType] & "' AND Company = '" & [test].[Company] & "' AND FileNumber = '" & [Test].[FileNumber] & "'"))

'shows how expr1 looks when concatenated and how strings are dates are delimited ' 

exp2: "SubmissionDate <= #" & [Test].[SubmissionDate] & "# AND SubmissionType = '" & [test].[SubmissionType] & "' AND Company = '" & [test].[Company] & "' AND FileNumber = '" & [Test].[FileNumber] & "'"

include: IIf([Test].[SubmissionType]="X" Or [Test].[SubmissionType]="X/C",1,0)

Here is the crosstab query: exp1 gives the columns and include restricts it to x and x/c, and when there is just one value in a group first = last=max=min.

enter image description here

'sql for the crosstab query/ replace Test with your table name and adjust for your variable names then paste into the sql tab of the query designer'

TRANSFORM First(Test.SubmissionDate) AS FirstOfSubmissionDate
SELECT Test.FileNumber, Test.Company
FROM Test
WHERE (((IIf([Test].[SubmissionType]="X" Or [Test].[SubmissionType]="X/C",1,0))=1))
GROUP BY Test.FileNumber, Test.Company
PIVOT IIf([Test].[SubmissionType]="X","X_Date",[Test].[SubmissionType] & "_Date" & DCount("SubmissionDate","Test","SubmissionDate <= #" & [Test].[SubmissionDate] & "# AND SubmissionType = '" & [test].[SubmissionType] & "' AND Company = '" & [test].[Company] & "' AND FileNumber = '" & [Test].[FileNumber] & "'"));

Note: the X_Date column will be at the wrong end. This is a consequence of how strings are sorted. To fix this either adjust the variable names or use a report. However, reports based on cross-tab queries are.... beyond the scope of this answer

Upvotes: 1

Related Questions